March 18, 2008 at 9:11 am
I am a newcomer to SQL Server 2000 T-SQL (have used COBOL/DB2)and am trying to use a CASE structure against 2 joined tables to yield a result set that I can then source to run a simple query against a 3rd table...and having problem structuring this.
(1) joining Unit & Network tbls: want to use CASE for specific conditions and source against joined tbls to create a temp result set of UnitID elements (from Unit Tbl) - Network tbl holds posting type element; how can I build a temp table of unitid values (when match found) that I can then source to query against a third table (capture unitids if no match)?
(2) Can I nest the CASE logic (#1 above) within a query that will source result set to find unitids on 3rd tbl where there is no match?
Should I even use CASE logic here?
Any feedback would be appreciated - I realize this is a bit "over my head" but am interested in learning...
use mediadbq
/* use inner logic's result set to query against 3rd tbl - write out
when no match on unitid */
select unitid from demotbl
where not exists
(select unitid -- is this correct? what follows each 'then'?
CASE
when n.networkpostingtype in ('C','N','S') and
(u.airdate between ('July 3, 2007'and 'July 29, 2007'))
then n.unitid
when n.networkpostingtype in ('N','S') and
(u.airdate between ('July 23, 2007'and 'July 29, 2007'))
then n.unitid
when n.networkpostingtype = 'C' and
(u.airdate between ('August 27, 2007'and 'September 30, 2007'))
then n.unitid
when n.networkpostingtype = 'S' and
(u.airdate between ('August 27, 2007'and 'September 2, 2007'))
then n.unitid
when n.networkpostingtype = 'C' and
(u.airdate between ('October 29, 2007'and 'November 25, 2007'))
then n.unitid
when n.networkpostingtype = 'N' and
(u.airdate between ('November 26, 2007'and 'December 30, 2007'))
then n.unitid
END
from nesunit as u inner join nesnetwork as n
on u.agency = n.agency and
u.media = n.media and
u.network = n.network
where media = 'N' and
agency = 'M2')
go
March 18, 2008 at 9:29 am
The EXISTS clause doesn't care about the column return - it just cares whether there is a record found or not. In the case you wrote - the NOT EXISTS will return false if there are ANY records matching the WHERE, even if the case happens to return NULL.
I'm thinking you simply want:
select unitid from demotbl
where not exists
(select NULL --the value is irrelevant, it's checking for existence of a ROW.
from nesunit as u
inner join nesnetwork as n
on u.agency = n.agency
and u.media = n.media
and u.network = n.network
where
media = 'N'
and agency = 'M2'
and (
(n.networkpostingtype in ('C','N','S') and
u.airdate between 'July 3, 2007'and 'July 29, 2007')
--note - this condition is already 100% contained within cond # 1
-- OR
-- (n.networkpostingtype in ('N','S') and
-- u.airdate between 'July 23, 2007'and 'July 29, 2007')
OR
(n.networkpostingtype = 'C' and
u.airdate between 'August 27, 2007'and 'September 30, 2007')
OR
(n.networkpostingtype = 'S' and
u.airdate between 'August 27, 2007'and 'September 2, 2007')
OR
(n.networkpostingtype = 'C' and
u.airdate between 'October 29, 2007'and 'November 25, 2007')
OR
(n.networkpostingtype = 'N' and
u.airdate between 'November 26, 2007'and 'December 30, 2007')
)
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 9:32 am
There is always more than one way to skin a cat, and the same applies to TSQL.
One way...
SELECT unitid
FROM demotbl AS d
LEFT JOIN nesunit AS u
ON u.unitID = d.unitID
AND media = 'N'
AND agency = 'M2'
INNER JOIN nesnetwork AS n
ON u.agency = n.agency
AND u.media = n.media
AND u.network = n.network
AND (
(n.networkpostingtype IN ('C','N','S') AND (u.airdate BETWEEN 'July 3, 2007'AND 'July 29, 2007'))
OR
(n.networkpostingtype IN ('N','S') AND (u.airdate BETWEEN 'July 23, 2007'AND 'July 29, 2007'))
OR
(n.networkpostingtype = 'C' AND (u.airdate BETWEEN 'August 27, 2007'AND 'September 30, 2007'))
OR
(n.networkpostingtype = 'S' AND (u.airdate BETWEEN 'August 27, 2007'AND 'September 2, 2007'))
OR
(n.networkpostingtype = 'C' AND (u.airdate BETWEEN 'October 29, 2007'AND 'November 25, 2007'))
OR
(n.networkpostingtype = 'N' AND (u.airdate BETWEEN 'November 26, 2007'AND 'December 30, 2007'))
)
WHERE
u.initID IS NULL
However, I think thi will perform poorly. You'll probably be better off building a table variable, or permanent table if this is used alot and joining to that.
This solution is a lot closer to true set based programming.
DECLARE @exclusionRange TABLE (TypeID CHAR(1), fromDate DATETIME, toDate DATETIME)
INSERT @exclusionRange
SELECT 'C', 'July 3, 2007', 'July 29, 2007' UNION
SELECT 'N', 'July 3, 2007', 'July 29, 2007' UNION
SELECT 'S', 'July 3, 2007', 'July 29, 2007' UNION
SELECT 'N', 'July 23, 2007', 'July 29, 2007' UNION
SELECT 'S', 'July 23, 2007', 'July 29, 2007' UNION
SELECT 'C', 'August 27, 2007', 'September 30, 2007' UNION
SELECT 'S', 'August 27, 2007', 'September 2, 2007' UNION
SELECT 'C', 'October 29, 2007', 'November 25, 2007' UNION
SELECT 'N', 'November 26, 2007', 'December 30, 2007'
SELECT unitid
FROM demotbl AS d
LEFT JOIN nesunit AS u
ON u.unitID = d.unitID
AND media = 'N'
AND agency = 'M2'
INNER JOIN nesnetwork AS n
ON u.agency = n.agency
AND u.media = n.media
AND u.network = n.network
INNER JOIN @exclusionRange e
ON e.TypeID = n.networkpostingtype
AND u.airdate BETWEEN e.fromDate AND e.toDate
WHERE
u.initID IS NULL
I hope this helps! 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 18, 2008 at 9:37 am
Gerald,
You seem to be along the right track.
Looking at the innner query (the one with the case statement) that looks spot on except for the syntax at the beginning, where you want to add an equals sign after the select unitid statement, like so:
(select unitid =
case ...
end
You could probably do without some of the brackets but that doesn't affect the syntax.
You might want to include an ELSE statement just in case non of the supplied conditions match.
Other than the the case statement looks ok as far as I can tell.
Now, to do the whole bit, including the outer query you'd do something like this:
select unitid from demotbl
where not exists
(select * from
(select unitid =
case ...
end
from nesunit as u inner join nesnetwork as n
on u.agency = n.agency and
u.media = n.media and
u.network = n.network
where media = 'N' and
agency = 'M2'
) as derived_temp_table
where demotbl.unitd = derived_temp_table.unitid
I'm not quite sure what you mean by your second question. You can nest case statements (not sure if there's a nesting limit) but I'm not sure this applies to your question.
Anyway, hope that helps.
March 24, 2008 at 7:59 am
Thanks guys for your input - appreciate the insights!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply