question

Wiz avatar image
Wiz asked

Sql query issues

I have this query shown below, can someone help me point to where am going wrong. select b.duns, a.RESPNUM,'D_AR', cast(round(a.q1,2)as float)*cast (round(Q1B_1/12,2)as float)+ cast(round(a.q1,2)as float)*cast (round(Q1B_2/1,2)as float)+ cast(round(a.q1,2)as float)*cast (round(Q1B_3*4.3,2)as float)+ cast(round(a.q1,2)as float)*cast (round(Q1B_4*21,2)as float) as Q1_VOL, cast(round(a.q1F,2)as float)*cast (round(Q1G_1/12,2)as float)+ cast(round(a.q1F,2)as float)*cast (round(Q1G_2/1,2)as float)+ cast(round(a.q1F,2)as float)*cast (round(Q1G_3*4.3,2)as float)+ cast(round(a.q1F,2)as float)*cast (round(Q1G_4*21,2)as float) as Q1G_VOL, case when Q1I = 0 and 'Q1G_VOL' = 0 and Q1D = 0 then 'Q1_VOL' = 'D_AR' end from Raw_Survey_Data_Test.dbo.S3000AR_Part1 a left join Raw_Survey_Data_Test.dbo.S3000AR_CompanyInfo b on a.RESPNUM = b.respnum
case-expression
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wiz avatar image Wiz commented ·
Can Anyone help me
0 Likes 0 ·
KenJ avatar image KenJ commented ·
What is your intent with this statement: then 'Q1_VOL' = 'D_AR' You are probably getting an error as you try to assign one string literal to another string literal. If your goal was to alias the column, place that after then "end" end as Q1_VOL
0 Likes 0 ·
Wiz avatar image Wiz commented ·
Q1I, Q1G_VOL, Q1D and Q1_VOL are temporal columns. But I want a case statement where 1. if Q1I is equal to 0, then the value of the column D_AR should be the value of Q1G_VOL, 2. if Q1G_VOL is equal to 0, then the value of the column D_AR should be the value of Q1D, 3. if Q1D is equal to 0, then the value of the column D_AR should be the value of Q1_VOL, And I want to ultimately name the end value D_AR. Having just 3 columns in my table.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
you will need one case statement per computed column. A case statement can only return a single value. You can choose to use that case statement for a single column, but you can't do value assignments within the case statement - it's used purely as a single expression.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
You could use a subquery to define the temporal values, with you can then reference in the outer query select duns, RESPNUM, case when Q1I = 0 and Q1G_VOL = 0 and Q1D = 0 then Q1_VOL when Q1I = 0 and Q1G_VOL = 0 then Q1D when Q1I = 0 then Q1G_VOL else Q1I end as 'D_AR' from ( select b.duns, a.RESPNUM,'D_AR', cast(round(a.q1,2)as float)*cast (round(Q1B_1/12,2)as float)+ cast(round(a.q1,2)as float)*cast (round(Q1B_2/1,2)as float)+ cast(round(a.q1,2)as float)*cast (round(Q1B_3*4.3,2)as float)+ cast(round(a.q1,2)as float)*cast (round(Q1B_4*21,2)as float) as Q1_VOL, cast(round(a.q1F,2)as float)*cast (round(Q1G_1/12,2)as float)+ cast(round(a.q1F,2)as float)*cast (round(Q1G_2/1,2)as float)+ cast(round(a.q1F,2)as float)*cast (round(Q1G_3*4.3,2)as float)+ cast(round(a.q1F,2)as float)*cast (round(Q1G_4*21,2)as float) as Q1G_VOL from Raw_Survey_Data_Test.dbo.S3000AR_Part1 a left join Raw_Survey_Data_Test.dbo.S3000AR_CompanyInfo b on a.RESPNUM = b.respnum )YourSubquery Note this isn't quite correct as I don;t know what the origin of Q1I is?
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wiz avatar image Wiz commented ·
awesome worked!!!!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.