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 MVPhttp://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 likeIF conditionSELECT ...ELSESELECT...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 MVPhttp://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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|