October 6, 2016 at 3:33 pm
I need to have a case statement in within a JOIN. The condition needs to be a SELECT,
Basically I need to see if records exist that matches a condition.
If there are any records that match the condition then my JOIN with be XX
If the count of matching records is 0 then my JOIN is YY
The following code does not work but it will show I think what I need to accomplish.
** This will be in a dynamic statement so I can not go set a variable and say if the variable > 0 kind of thing. I really need to SELECT this real time and make a decision based upon those results.
The example is not exactly what the end result will be but if this works then I can adapt to what I actually need. Can someone please help??
SELECT *
FROM ETLCorrelation c
JOIN ETLMapping as m on
case (SELECT count(*) FROM ETLCorrelation WHERE DestinationTable = 'Node' AND SourceID <> DestinationID)
when >1 (SELECT TOP 10 FROM ETLMapping)
WHEN =0 (SELECT TOP 10 FROM EventLog)
ELSE end as outcome
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 6, 2016 at 4:07 pm
Jeffery Williams (10/6/2016)
I need to have a case statement in within a JOIN. The condition needs to be a SELECT,Basically I need to see if records exist that matches a condition.
If there are any records that match the condition then my JOIN with be XX
If the count of matching records is 0 then my JOIN is YY
The following code does not work but it will show I think what I need to accomplish.
** This will be in a dynamic statement so I can not go set a variable and say if the variable > 0 kind of thing. I really need to SELECT this real time and make a decision based upon those results.
The example is not exactly what the end result will be but if this works then I can adapt to what I actually need. Can someone please help??
SELECT *
FROM ETLCorrelation c
JOIN ETLMapping as m on
case (SELECT count(*) FROM ETLCorrelation WHERE DestinationTable = 'Node' AND SourceID <> DestinationID)
when >1 (SELECT TOP 10 FROM ETLMapping)
WHEN =0 (SELECT TOP 10 FROM EventLog)
ELSE end as outcome
The CASE expression has two forms: the simple form and the searched form. You are trying to mix them both, and your ELSE clause does not contain an expression. The simple form does not allow comparison operators, because the only allowed comparison is equals.
Also, a CASE expression can only return a single scalar value per row. You're trying to return a resultset. Try the following instead.
SELECT *
FROM ETLCorrelation c
CROSS APPLY (SELECT count(*) FROM ETLCorrelation WHERE DestinationTable = 'Node' AND SourceID <> DestinationID) AS c(cnt)
CROSS APPLY (
SELECT TOP 10 * -- You need to return the exact same number of columns in both subqueries (or your code violates First Normal Form)
FROM ETLMapping
WHERE c.cnt > 1
-- You are missing an order by clause here, so your output is non-deterministic
UNION ALL
SELECT TOP 10 * -- You need to return the exact same number of columns in both subqueries (or your code violates First Normal Form)
FROM EventLog
WHERE c.cnt = 0
-- You are missing an order by clause here, so your output is non-deterministic
) e
Those two tables don't sound like they have similar data, so I'm not sure that you really want to take this approach.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2016 at 4:20 pm
I will give it a try thank you. AND I don't have to count I just need to know if the result is greater than 0. Basically true / false.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 6, 2016 at 4:28 pm
Jeffery Williams (10/6/2016)
I will give it a try thank you. AND I don't have to count I just need to know if the result is greater than 0. Basically true / false.
If you are able to provide some sample DDL, set-up scripts and desired results, you will get a better response. I've read your post several times and still I am having trouble understanding what you are trying to achieve with those joins.
October 6, 2016 at 5:33 pm
Jeffery Williams (10/6/2016)
I will give it a try thank you. AND I don't have to count I just need to know if the result is greater than 0. Basically true / false.
If you don't need a COUNT, you should be using EXISTS. COUNT has to read all of the records to return a value, whereas the EXISTS can short circuit as soon as it finds ANY record.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2016 at 9:40 pm
Solved in thank you thou.
+ ' AND (' + @Alias + '.SourceID <> ' + @Alias + '.DestinationID AND EXISTS (SELECT 1 FROM ETLCorrelation WHERE DestinationTable = ''' + @PKTableName + '''' + ' AND SourceID <> DestinationID AND BatchID = ' + Cast(@BatchID AS varchar(10)) + ')' -- 10042016 DJ | Changed from <> to =
+ ' OR ' + @Alias + '.SourceID = ' + @Alias + '.DestinationID AND NOT EXISTS (SELECT 1 FROM ETLCorrelation WHERE DestinationTable = ''' + @PKTableName + '''' + ' AND SourceID <> DestinationID AND BatchID = ' + Cast(@BatchID AS varchar(10)) + '))' -- 10042016 DJ | Changed from <> to =
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply