Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

How can i rewrite this query to give me the same results

I have the following sql structure:


Select
,Case when lm.origin in ('X','Y','Z')
then cast(li.num as char)
ELSE (lm.customer_num)
 END bl_num
into #loads
from table 1
left join table 2
on 1.id = 2.id
where {fileter criteria}
Group by Case when lm.origin in ('X','Y','Z')
      then cast(li.num as char)
               ELSE (lm.customer_num)
              END
            
There are  a few issues i am haveing when i try to execute the above sql with the changes i need.
I need to change the case statement by removing the hard coded values of 'X','Y','Z' to be a select statement Select letter from table 2 (for example)...i get an error when i try to add the select statement to the case statement in the group by section of the query.  How can i change the query to run without hard codeing  the 'X','Y','Z' ...i am also using a temp table that my cause issues depending on how it is rewritten.

Thanks!
0
MoreThanDoubled
Asked:
MoreThanDoubled
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
It is quite possible that you cannot simply replace 'X','Y','Z' with a select statement.

What is a sample of the select statement you want to use?

If we had some sample data and an expected result we could offer proven alternatives.
0
 
Scott CraigWebmasterCommented:
You can't have a select within a case.  You can, however within an IF statement, so if you can change your case into an IF, you can do this.

Might be a little longer code, but that allows you to not have hard-coded values.
0
 
Scott PletcherSenior DBACommented:
I suggest using OUTER APPLY to assign an alias to the final result; you can then use that alias in the select and group by:

Select
 oa1.bl_num
 into #loads
 from [table] t1
 left join [table] t2
 on t1.id = t2.id
 outer apply (
     select case when lm.origin in (select <column> from <table> where {filter criteria})
         then cast(li.num as char) ELSE (lm.customer_num) END AS bl_num
 ) as oa1
 where {filter criteria}
 Group by ca1.bl_num


>> You can't have a select within a case. <<
That's not true.  There are many possible valid uses of SELECT within a CASE statement.
0
 
Scott CraigWebmasterCommented:
>> That's not true.  There are many possible valid uses of SELECT within a CASE statement. <<
Actually it is unless it's a subquery.
0
 
Scott PletcherSenior DBACommented:
>>
>> That's not true.  There are many possible valid uses of SELECT within a CASE statement. <<
 Actually it is unless it's a subquery.  <<
<<

The q explicitly dealt with only a subquery:

>> I need to change the case statement by removing the hard coded values of 'X','Y','Z' to be a select statement Select letter from table 2 (for example) <<

so saying "can't have SELECT in a CASE" was implicitly stating that you couldn't have a subquery.  As I noted, and demonstrated, that's just not true.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now