SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CASE statement generates error involving EXISTS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

actsql
Starting Member

34 Posts

Posted - 03/01/2013 :  23:50:05  Show Profile  Reply with Quote
I have a subquery that I need to join to but that has null values in the second and non-primary key column I am linking to (I have two links between the subquery I am using for the query, one to the primary and the second, as mentioned in the preceding sentences, to a non-primary). Luckily, when I need the data where the non-primary key column is null, I don't need to link to that column. So, I am trying to use a CASE statement to run the single link query when the non-primary key is null, and the double link query when the non-primary key is not null. I keep getting the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Here is the query:

SELECT

CASE WHEN IsNull("vrvPOTaxRates"."SubTaxCode",0)=0 THEN

(

SELECT "vrvPOTaxRates"."TaxCode", "vrvPOTaxRates"."PO", "HQTX"."Description" FROM "vrvPOTaxRates" INNER JOIN "HQTX" ON "vrvPOTaxRates"."TaxCode" = "HQTX"."TaxCode"

)

ELSE

(

SELECT "vrvPOTaxRates"."PO", d.Description FROM "vrvPOTaxRates" LEFT OUTER JOIN

(
SELECT c.TaxCode, c.SubTaxCode, c.DetTaxCode, "HQTX"."Description" FROM

(



SELECT b.TaxCode, CASE WHEN b.MultiLevel = 'Y' THEN b.TaxLink ELSE '' END AS SubTaxCode, CASE WHEN b.MultiLevel = 'Y' THEN b.TaxLink ELSE b.TaxCode END AS DetTaxCode, b.Description FROM

(

SELECT "HQTX"."TaxCode", "HQTX"."Description", "HQTX"."MultiLevel", a.TaxLink FROM "HQTX" LEFT OUTER JOIN

(


SELECT * FROM "HQTL"


) a ON "HQTX"."TaxCode" = a.TaxCode

) b

) c LEFT OUTER JOIN "HQTX" ON "HQTX"."TaxCode" = c.DetTaxCode

) d ON (("vrvPOTaxRates"."TaxCode"=d.TaxCode) AND ("vrvPOTaxRates"."SubTaxCode" = d.SubTaxCode))

) END FROM vrvPOTaxRates

My research has suggested that an IF statement might be a better choice but struggling to understand how to do that within a SELECT statement.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2013 :  02:16:25  Show Profile  Reply with Quote
you cant use case...when like above. CASE...WHEN is a expression and not a control flow statement. you should be using IF instead



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2013 :  12:06:39  Show Profile  Reply with Quote
IF cant come inside SELECT it should be like

IF condition

SELECT ...
ELSE
SELECT...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2013 :  13:05:22  Show Profile  Reply with Quote
you need to get values of columns onto variables to be used in if condition

can you specify your requirement in words giving table structure and some sample data. then it'll be easier for someone to follow you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2013 :  13:41:09  Show Profile  Reply with Quote
yep...post within code tags

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2013 :  23:47:38  Show Profile  Reply with Quote
so how do relate these rows? is there a common id which links them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

actsql
Starting Member

34 Posts

Posted - 03/03/2013 :  21:28:44  Show Profile  Reply with Quote
Thank you visakh16 for taking the time to help me. The more time I spent working on this query the more I realized the basic logic behind it is flawed. I went back to the drawing board and found a much simpler solution. So, I am abandoning this approach in favor of a simpler and hopefully faster one. Thank you again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/03/2013 :  22:41:31  Show Profile  Reply with Quote
cool..glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000