Help to compare 3 columns/same table/return a valu

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