CASE statement generates error involving EXISTS

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE statement generates error involving EXISTS

Author

Topic

actsql
Starting Member

34 Posts

Posted - 2013-03-01 : 23:50:05

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

52326 Posts

Posted - 2013-03-02 : 02:16:25

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 12:06:39

IF cant come inside SELECT it should be like

IF condition

SELECT ...
ELSE
SELECT...

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 13:05:22

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 13:41:09

yep...post within code tags

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 23:47:38

so how do relate these rows? is there a common id which links them?

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

actsql
Starting Member

34 Posts

Posted - 2013-03-03 : 21:28:44

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-03 : 22:41:31

cool..glad that you got it sorted out

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources