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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help to compare 3 columns/same table/return a valu

Author  Topic 

JuliaLux
Starting Member

2 Posts

Posted - 2014-09-18 : 17:46:22
Hi
I am using this Case statement and runs but doesnt do the compare and giving me a rating value



select a.id,a.name
,s.External_Unique_Key__c as 'Loc_Key'
,SUBSTRING (s.External_Unique_Key__c,1,3) as 'BAAN_ERP'
,SUBSTRING (s.External_Unique_Key__c,5,9) as 'BAAN_INVOICE'
,SUBSTRING (s.External_Unique_Key__c,15,9) as 'BAAN_SOLD'
,SUBSTRING (s.External_Unique_Key__c,25,9) as 'BAAN_SHIP'

,CASE WHEN 'BAAN_INVOICE' = 'BAAN_SOLD' THEN
CASE WHEN 'BAAN_INVOICE' = 'BAAN_SHIP' THEN 1 ELSE 0 END
CASE WHEN 'BAAN_SOLD' = 'BAAN_SHIP' THEN 1 ELSE 0 END
ELSE
0
END as 'Rating'

**** ALSO TRIED ****
--,CASE WHEN ('BAAN_INVOICE'='BAAN_SHIP') and ('BAAN_INVOICE' = 'BAAN_SOLD') and ('BAAN_SHIP'!='BAAN_SOLD') THEN '1' END as 'Rating'

from salesforce...[SVMXC__Site__c] s (nolock)
--join salesforce...[ERP_Company_Configuration__c] e (nolock)on e.id = s.ERP_Company_Number1__c
join qa3salesforce...[account] a (nolock) on a.id = s.SVMXC__Account__c and a.recordtypeid ='012300000000XGBAA2'-- QA3A.LSGNASD
--left join qa3salesforce...[Financial_Parent__c] f (nolock) on f.id= s.ERP_Parent_Account_Number__c and f.recordtypeid = '01230000000ZP55AAG'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-18 : 17:55:42
You either need to repeat the SUBSTRING calculations in the CASE, use a derived table or CTE. And don't put single quotes around the alias names when referencing them, otherwise they are treated as a string rather than the alias name.

Here's the derived table:

select *,
CASE
WHEN BAAN_INVOICE = BAAN_SOLD OR BAAN_INVOICE = BAAN_SHIP OR BAAN_SOLD = BAAN_SHIP THEN 1
ELSE 0
END as 'Rating'
from
(
select
a.id,a.name
,s.External_Unique_Key__c as 'Loc_Key'
,SUBSTRING (s.External_Unique_Key__c,1,3) as 'BAAN_ERP'
,SUBSTRING (s.External_Unique_Key__c,5,9) as 'BAAN_INVOICE'
,SUBSTRING (s.External_Unique_Key__c,15,9) as 'BAAN_SOLD'
,SUBSTRING (s.External_Unique_Key__c,25,9) as 'BAAN_SHIP'
from salesforce...[SVMXC__Site__c] s (nolock)
--join salesforce...[ERP_Company_Configuration__c] e (nolock)on e.id = s.ERP_Company_Number1__c
join qa3salesforce...[account] a (nolock) on a.id = s.SVMXC__Account__c and a.recordtypeid ='012300000000XGBAA2'-- QA3A.LSGNASD
--left join qa3salesforce...[Financial_Parent__c] f (nolock) on f.id= s.ERP_Parent_Account_Number__c and f.recordtypeid = '01230000000ZP55AAG'
) t


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

JuliaLux
Starting Member

2 Posts

Posted - 2014-09-18 : 22:24:50
I bow to the Goddess!!! Worked Great.
Thank you again for the quick response.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-19 : 12:57:32


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -