Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NiceSituation
Starting Member
22 Posts |
 Posted - 2008-03-29 : 04:02:50
|
| Hi everyone. I am creating a view which will show certain information about radio programs. It must show information about every commercial transmitted during each program (program name, commercial name, who paid for it, when it was transmitted, date and time on separate fields, and other fields). My problem is: there are certain commercials which are not classified by program but rather by the time in which they were transmitted, and in those cases the program field is left "NULL". I need to find out which program the commercial was transmitted on. For that, I have the program schedules on another view, so I must compare the time when the commercial was transmitted with the program schedules to find the appropriate program. So it seems that everything is solved, since it is only a matter of using a CASE sentence on the SELECT statement, for instance:SELECT Date_transmitted, Time_transmitted, Payer, Commercial_Name...CASE WHEN Program_Name IS NULL THEN (SELECT Program_Name FROM Program_Schedules WHERE Time_transmitted BETWEEN Start_time AND End_Time) ELSE Program_Name END AS Program_NameFROM Many_Tables_Joined_TogetherBut the problem here is I need that "Time_transmitted" per row beforehand so that I can check with my program schedules and find the appropriate program. I thought about doing a "recursive" query, but I get an error stating that a query or view cannot reference itself. I thought about getting the transmitted times on another view and just selecting that view and joining it with the program schedules view as a subquery, with the same logic I wrote above. However, I get an error which tells me that my subquery is returning more than one row whenever it is executed, and indeed it should because I am not telling the view about a specific transmitted time each time the subquery is executed. My last resort was to join the program schedules and times transmitted for each commercial on another view and then just selecting the program name from this view, but in this case I get a timeout error, the view execution is way too slow and it times out.Any suggestions? If I did not explain myself enough, please let me know.May today be good, and tomorrow better |
|
|
|
|
|
|
|
"Recursive" Query-View?