locked
SQL query with Case on linked server RRS feed

  • Question

  • I have an SQL query that has multiple Case When statements. When I run it directly on the remote SQL server everything works fine. When I run it from my office server with a linked server connection to the remote server, I get the error

    "Case expressions may only be nested to level 10." Is there a work around this issue. Following is the sample code

    CREATE TABLE #temp(IDNO [int], PIID [varchar](10), [Name] [varchar](30), ACCTID [varchar](15), Descr [varchar](50), ADATE [Date], Site [varchar](10), STAT [varchar](10), FLD varchar(10))
    
    Insert into #temp values (1,'AQ1111', 'client 1','a001','dct','01/01/2020','GM','ACTIVE','Z01')
    Insert into #temp values (2,'AQ1111', 'client 1','a001','dsadct','01/01/2020','AB','ACTIVE','A02')
    Insert into #temp values (3,'AQ1111', 'client 1','a002','dqe2ct','01/01/2020','HG','ACTIVE','ABS')
    Insert into #temp values (4,'AQ2222', 'client 1','a003','dc364t','01/01/2020','GM','ACTIVE','Q12')
    Insert into #temp values (5,'AQ1213', 'client 1','a004','dct','01/01/2020','GM','CANCEL','W23')
    Insert into #temp values (6,'AQ3333', 'client 1','a005','dct','01/01/2020','GM','ACTIVE','NA')
    Insert into #temp values (7,'AQ3333', 'client 1','a005','dwafwfect','01/01/2020','GM','CANCEL','M12')
    Insert into #temp values (8,'AQ3333', 'client 1','a005','d4t43fct','01/01/2020','GM','ACTIVE','S12')
    Insert into #temp values (9,'AQ3333', 'client 1','a006','dawfwct','01/01/2020','GM','ACTIVE','M12')
    Insert into #temp values (10,'AQ4444', 'client 1','a006','dct','01/01/2020','GT','ACTIVE','A02')
    Insert into #temp values (11,'AQ4444', 'client 1','a006','dwafwafct','01/01/2020','RG','CANCEL','A08')
    Insert into #temp values (12,'AQ4444', 'client 1','a007','dwfaafct','01/01/2020','GM','ACTIVE','A14')
    Insert into #temp values (13,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A32')
    Insert into #temp values (14,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A01')
    Insert into #temp values (15,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A03')
    Insert into #temp values (16,'AQ5555', 'client 1','a008','dct','01/01/2020','AM','ACTIVE','A01')
    Insert into #temp values (17,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A12')
    Insert into #temp values (18,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A03')
    Insert into #temp values (19,'AQ5555', 'client 1','a009','dct','01/01/2020','GQ','ACTIVE','A08')
    Insert into #temp values (20,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A50')
    Insert into #temp values (21,'AQ5555', 'client 1','a010','dct','01/01/2020','GM','ACTIVE','A32')
    Insert into #temp values (22,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','PENDING','A56')
    Insert into #temp values (23,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','ACTIVE','S33')
    
    Select PIID,NAME,ACCTID,Descr,ADATE,
    
    CASE WHEN FLD = 'A01' THEN 'ADM' 
    	WHEN FLD = 'A02' THEN 'MRG' 
    	WHEN FLD = 'A03' THEN 'DIS' 
    	WHEN FLD = 'A04' THEN 'FWD' 
    	WHEN FLD = 'A05' THEN 'UNKNOWN' 
    	WHEN FLD = 'A06' THEN 'NA' 
    	WHEN FLD = 'A50' THEN 'ACTIVE' 
    	WHEN FLD = 'S33' THEN 'PENDING' 
    	WHEN FLD = 'Z01' THEN 'COMPLETED' 
    	WHEN FLD = 'ABS' THEN 'CANCELLED' 
    	WHEN FLD = 'S12' THEN 'REJECTED' 
    	WHEN FLD = 'Q12' THEN 'FAILED' 
    	WHEN FLD = 'Z01' THEN 'COMPLETED' 
    	WHEN FLD = 'ABS' THEN 'CANCELLED' 
    	WHEN FLD = 'S12' THEN 'REJECTED' 
    	WHEN FLD = 'Q12' THEN 'FAILED' 
    
    	ELSE 'NOT DEFINED'
    	END AS CODE
    
    
    
    from #temp 
    drop table #temp
    

    Friday, August 7, 2020 8:02 PM

Answers

  • Try this:

    CREATE TABLE #temp(IDNO [int], PIID [varchar](10), [Name] [varchar](30)
    , ACCTID [varchar](15), Descr [varchar](50), ADATE [Date], Site [varchar](10)
    , STAT [varchar](10), FLD varchar(10))
    
    Insert into #temp values (1,'AQ1111', 'client 1','a001','dct','01/01/2020','GM','ACTIVE','Z01')
    Insert into #temp values (2,'AQ1111', 'client 1','a001','dsadct','01/01/2020','AB','ACTIVE','A02')
    Insert into #temp values (3,'AQ1111', 'client 1','a002','dqe2ct','01/01/2020','HG','ACTIVE','ABS')
    Insert into #temp values (4,'AQ2222', 'client 1','a003','dc364t','01/01/2020','GM','ACTIVE','Q12')
    Insert into #temp values (5,'AQ1213', 'client 1','a004','dct','01/01/2020','GM','CANCEL','W23')
    Insert into #temp values (6,'AQ3333', 'client 1','a005','dct','01/01/2020','GM','ACTIVE','NA')
    Insert into #temp values (7,'AQ3333', 'client 1','a005','dwafwfect','01/01/2020','GM','CANCEL','M12')
    Insert into #temp values (8,'AQ3333', 'client 1','a005','d4t43fct','01/01/2020','GM','ACTIVE','S12')
    Insert into #temp values (9,'AQ3333', 'client 1','a006','dawfwct','01/01/2020','GM','ACTIVE','M12')
    Insert into #temp values (10,'AQ4444', 'client 1','a006','dct','01/01/2020','GT','ACTIVE','A02')
    Insert into #temp values (11,'AQ4444', 'client 1','a006','dwafwafct','01/01/2020','RG','CANCEL','A08')
    Insert into #temp values (12,'AQ4444', 'client 1','a007','dwfaafct','01/01/2020','GM','ACTIVE','A14')
    Insert into #temp values (13,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A32')
    Insert into #temp values (14,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A01')
    Insert into #temp values (15,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A03')
    Insert into #temp values (16,'AQ5555', 'client 1','a008','dct','01/01/2020','AM','ACTIVE','A01')
    Insert into #temp values (17,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A12')
    Insert into #temp values (18,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A03')
    Insert into #temp values (19,'AQ5555', 'client 1','a009','dct','01/01/2020','GQ','ACTIVE','A08')
    Insert into #temp values (20,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A50')
    Insert into #temp values (21,'AQ5555', 'client 1','a010','dct','01/01/2020','GM','ACTIVE','A32')
    Insert into #temp values (22,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','PENDING','A56')
    Insert into #temp values (23,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','ACTIVE','S33')
    
    Select PIID,NAME,ACCTID,Descr,ADATE  
    
    --,CASE WHEN FLD = 'A01' THEN 'ADM' 
    --	WHEN FLD = 'A02' THEN 'MRG' 
    --	WHEN FLD = 'A03' THEN 'DIS' 
    --	WHEN FLD = 'A04' THEN 'FWD' 
    --	WHEN FLD = 'A05' THEN 'UNKNOWN' 
    --	WHEN FLD = 'A06' THEN 'NA' 
    --	WHEN FLD = 'A50' THEN 'ACTIVE' 
    --	WHEN FLD = 'S33' THEN 'PENDING' 
    --	WHEN FLD = 'Z01' THEN 'COMPLETED' 
    --	WHEN FLD = 'ABS' THEN 'CANCELLED' 
    --	WHEN FLD = 'S12' THEN 'REJECTED' 
    --	WHEN FLD = 'Q12' THEN 'FAILED' 
    --	WHEN FLD = 'Z01' THEN 'COMPLETED' 
    --	WHEN FLD = 'ABS' THEN 'CANCELLED' 
    --	WHEN FLD = 'S12' THEN 'REJECTED' 
    --	WHEN FLD = 'Q12' THEN 'FAILED' 
    
    --	ELSE 'NOT DEFINED'
    --	END as code1
    
    ,ISNULL(CODE,'NOT DEFINED') AS CODE
    
    
    
    from #temp t
    
    left join 
    (values('Z01','COMPLETED'),('A02','MRG')
    ,('ABS','CANCELLED')
    ,('Q12','FAILED')
    ,('W23','NOT DEFINED')
    ,('NA','NOT DEFINED')
    ,('M12','NOT DEFINED')
    ,('S12','REJECTED')
    ,('M12','NOT DEFINED')
    ,('A02','MRG')
    ,('A08','NOT DEFINED')
    ,('A14','NOT DEFINED')
    ,('A32','NOT DEFINED')
    ,('A01','ADM')
    ,('A03','DIS')
    ,('A01','ADM')
    ,('A12','NOT DEFINED')
    ,('A03','DIS')
    ,('A08','NOT DEFINED')
    ,('A50','ACTIVE')
    ,('A32','NOT DEFINED')
    ,('A56','NOT DEFINED')
    ,('S33','PENDING') ) d(FLD2, CODE) on t.FLD=d.FLD2
    
    drop table #temp

    • Proposed as answer by Naomi N Friday, August 7, 2020 8:32 PM
    • Marked as answer by dper77 Tuesday, August 11, 2020 12:48 PM
    Friday, August 7, 2020 8:17 PM
  • Hi dper77,

    Please refer to :

    CREATE TABLE #temp(IDNO [int], PIID [varchar](10), [Name] [varchar](30), ACCTID [varchar](15), Descr [varchar](50), ADATE [Date], Site [varchar](10), STAT [varchar](10), FLD varchar(10)) Insert into #temp values (1,'AQ1111', 'client 1','a001','dct','01/01/2020','GM','ACTIVE','Z01') Insert into #temp values (2,'AQ1111', 'client 1','a001','dsadct','01/01/2020','AB','ACTIVE','A02') Insert into #temp values (3,'AQ1111', 'client 1','a002','dqe2ct','01/01/2020','HG','ACTIVE','ABS') Insert into #temp values (4,'AQ2222', 'client 1','a003','dc364t','01/01/2020','GM','ACTIVE','Q12') Insert into #temp values (5,'AQ1213', 'client 1','a004','dct','01/01/2020','GM','CANCEL','W23') Insert into #temp values (6,'AQ3333', 'client 1','a005','dct','01/01/2020','GM','ACTIVE','NA') Insert into #temp values (7,'AQ3333', 'client 1','a005','dwafwfect','01/01/2020','GM','CANCEL','M12') Insert into #temp values (8,'AQ3333', 'client 1','a005','d4t43fct','01/01/2020','GM','ACTIVE','S12') Insert into #temp values (9,'AQ3333', 'client 1','a006','dawfwct','01/01/2020','GM','ACTIVE','M12') Insert into #temp values (10,'AQ4444', 'client 1','a006','dct','01/01/2020','GT','ACTIVE','A02') Insert into #temp values (11,'AQ4444', 'client 1','a006','dwafwafct','01/01/2020','RG','CANCEL','A08') Insert into #temp values (12,'AQ4444', 'client 1','a007','dwfaafct','01/01/2020','GM','ACTIVE','A14') Insert into #temp values (13,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A32') Insert into #temp values (14,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A01') Insert into #temp values (15,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A03') Insert into #temp values (16,'AQ5555', 'client 1','a008','dct','01/01/2020','AM','ACTIVE','A01') Insert into #temp values (17,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A12') Insert into #temp values (18,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A03') Insert into #temp values (19,'AQ5555', 'client 1','a009','dct','01/01/2020','GQ','ACTIVE','A08') Insert into #temp values (20,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A50') Insert into #temp values (21,'AQ5555', 'client 1','a010','dct','01/01/2020','GM','ACTIVE','A32') Insert into #temp values (22,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','PENDING','A56') Insert into #temp values (23,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','ACTIVE','S33') select * from #temp with cte as (Select PIID,NAME,ACCTID,Descr,ADATE,FLD,Site,STAT, CASE WHEN FLD = 'A01' THEN 'ADM' WHEN FLD = 'A02' THEN 'MRG' WHEN FLD = 'A03' THEN 'DIS' WHEN FLD = 'A04' THEN 'FWD' WHEN FLD = 'A05' THEN 'UNKNOWN' WHEN FLD = 'A06' THEN 'NA' WHEN FLD = 'A50' THEN 'ACTIVE' WHEN FLD = 'S33' THEN 'PENDING' WHEN FLD = 'Z01' THEN 'COMPLETED' ELSE 'NOT DEFINED' END AS CODE from #temp) ,cte1 as(Select PIID,NAME,ACCTID,Descr,ADATE,FLD,Site,STAT, CASE WHEN FLD = 'ABS' and CODE='NOT DEFINED' THEN 'CANCELLED' WHEN FLD = 'S12' and CODE='NOT DEFINED' THEN 'REJECTED' WHEN FLD = 'Q12' and CODE='NOT DEFINED' THEN 'FAILED' WHEN FLD = 'Z01' and CODE='NOT DEFINED' THEN 'COMPLETED' WHEN FLD = 'ABS' and CODE='NOT DEFINED' THEN 'CANCELLED' WHEN FLD = 'S12' and CODE='NOT DEFINED' THEN 'REJECTED' WHEN FLD = 'Q12' and CODE='NOT DEFINED' THEN 'FAILED' ELSE CODE END AS CODE1 from cte) ,cte2 as(select * from cte where CODE<>'NOT DEFINED' union select * from cte1) select PIID,NAME,ACCTID,Descr,ADATE,CODE from cte2

    drop table #temp

    This issue may be help to you: why-do-linked-servers-have-a-limitation-of-10-branches-in-a-case-expression


    Best Regards   
    Echo



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.





    • Edited by Echo Liuz Monday, August 10, 2020 8:23 AM
    • Marked as answer by dper77 Tuesday, August 11, 2020 12:48 PM
    Monday, August 10, 2020 7:52 AM
  • I also found the following that works too (as per Echo's post).

    https://dba.stackexchange.com/questions/42837/why-do-linked-servers-have-a-limitation-of-10-branches-in-a-case-expression

    Will be doing more testing.

    Select PIID,NAME,ACCTID,Descr,ADATE ,
    CASE SUBSTRING(FLD, 1, 10)
    WHEN  'A01' THEN 'ADM' 
           WHEN  'A02' THEN 'MRG' 
           WHEN  'A03' THEN 'DIS' 
           WHEN  'A04' THEN 'FWD' 
           WHEN  'A05' THEN 'UNKNOWN' 
           WHEN  'A06' THEN 'NA' 
           WHEN  'A50' THEN 'ACTIVE' 
           WHEN  'S33' THEN 'PENDING' 
           WHEN 'Z01' THEN 'COMPLETED' 
           WHEN  'ABS' THEN 'CANCELLED' 
           WHEN  'S12' THEN 'REJECTED' 
           WHEN  'ABS' THEN 'CANCELLED' 
           ELSE 'NOT DEFINED'
           END AS CODE
    from #temp



    • Edited by dper77 Monday, August 10, 2020 9:57 PM
    • Marked as answer by dper77 Tuesday, August 11, 2020 12:49 PM
    Monday, August 10, 2020 9:53 PM

All replies

  • Try this:

    CREATE TABLE #temp(IDNO [int], PIID [varchar](10), [Name] [varchar](30)
    , ACCTID [varchar](15), Descr [varchar](50), ADATE [Date], Site [varchar](10)
    , STAT [varchar](10), FLD varchar(10))
    
    Insert into #temp values (1,'AQ1111', 'client 1','a001','dct','01/01/2020','GM','ACTIVE','Z01')
    Insert into #temp values (2,'AQ1111', 'client 1','a001','dsadct','01/01/2020','AB','ACTIVE','A02')
    Insert into #temp values (3,'AQ1111', 'client 1','a002','dqe2ct','01/01/2020','HG','ACTIVE','ABS')
    Insert into #temp values (4,'AQ2222', 'client 1','a003','dc364t','01/01/2020','GM','ACTIVE','Q12')
    Insert into #temp values (5,'AQ1213', 'client 1','a004','dct','01/01/2020','GM','CANCEL','W23')
    Insert into #temp values (6,'AQ3333', 'client 1','a005','dct','01/01/2020','GM','ACTIVE','NA')
    Insert into #temp values (7,'AQ3333', 'client 1','a005','dwafwfect','01/01/2020','GM','CANCEL','M12')
    Insert into #temp values (8,'AQ3333', 'client 1','a005','d4t43fct','01/01/2020','GM','ACTIVE','S12')
    Insert into #temp values (9,'AQ3333', 'client 1','a006','dawfwct','01/01/2020','GM','ACTIVE','M12')
    Insert into #temp values (10,'AQ4444', 'client 1','a006','dct','01/01/2020','GT','ACTIVE','A02')
    Insert into #temp values (11,'AQ4444', 'client 1','a006','dwafwafct','01/01/2020','RG','CANCEL','A08')
    Insert into #temp values (12,'AQ4444', 'client 1','a007','dwfaafct','01/01/2020','GM','ACTIVE','A14')
    Insert into #temp values (13,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A32')
    Insert into #temp values (14,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A01')
    Insert into #temp values (15,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A03')
    Insert into #temp values (16,'AQ5555', 'client 1','a008','dct','01/01/2020','AM','ACTIVE','A01')
    Insert into #temp values (17,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A12')
    Insert into #temp values (18,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A03')
    Insert into #temp values (19,'AQ5555', 'client 1','a009','dct','01/01/2020','GQ','ACTIVE','A08')
    Insert into #temp values (20,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A50')
    Insert into #temp values (21,'AQ5555', 'client 1','a010','dct','01/01/2020','GM','ACTIVE','A32')
    Insert into #temp values (22,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','PENDING','A56')
    Insert into #temp values (23,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','ACTIVE','S33')
    
    Select PIID,NAME,ACCTID,Descr,ADATE  
    
    --,CASE WHEN FLD = 'A01' THEN 'ADM' 
    --	WHEN FLD = 'A02' THEN 'MRG' 
    --	WHEN FLD = 'A03' THEN 'DIS' 
    --	WHEN FLD = 'A04' THEN 'FWD' 
    --	WHEN FLD = 'A05' THEN 'UNKNOWN' 
    --	WHEN FLD = 'A06' THEN 'NA' 
    --	WHEN FLD = 'A50' THEN 'ACTIVE' 
    --	WHEN FLD = 'S33' THEN 'PENDING' 
    --	WHEN FLD = 'Z01' THEN 'COMPLETED' 
    --	WHEN FLD = 'ABS' THEN 'CANCELLED' 
    --	WHEN FLD = 'S12' THEN 'REJECTED' 
    --	WHEN FLD = 'Q12' THEN 'FAILED' 
    --	WHEN FLD = 'Z01' THEN 'COMPLETED' 
    --	WHEN FLD = 'ABS' THEN 'CANCELLED' 
    --	WHEN FLD = 'S12' THEN 'REJECTED' 
    --	WHEN FLD = 'Q12' THEN 'FAILED' 
    
    --	ELSE 'NOT DEFINED'
    --	END as code1
    
    ,ISNULL(CODE,'NOT DEFINED') AS CODE
    
    
    
    from #temp t
    
    left join 
    (values('Z01','COMPLETED'),('A02','MRG')
    ,('ABS','CANCELLED')
    ,('Q12','FAILED')
    ,('W23','NOT DEFINED')
    ,('NA','NOT DEFINED')
    ,('M12','NOT DEFINED')
    ,('S12','REJECTED')
    ,('M12','NOT DEFINED')
    ,('A02','MRG')
    ,('A08','NOT DEFINED')
    ,('A14','NOT DEFINED')
    ,('A32','NOT DEFINED')
    ,('A01','ADM')
    ,('A03','DIS')
    ,('A01','ADM')
    ,('A12','NOT DEFINED')
    ,('A03','DIS')
    ,('A08','NOT DEFINED')
    ,('A50','ACTIVE')
    ,('A32','NOT DEFINED')
    ,('A56','NOT DEFINED')
    ,('S33','PENDING') ) d(FLD2, CODE) on t.FLD=d.FLD2
    
    drop table #temp

    • Proposed as answer by Naomi N Friday, August 7, 2020 8:32 PM
    • Marked as answer by dper77 Tuesday, August 11, 2020 12:48 PM
    Friday, August 7, 2020 8:17 PM
  • Hi dper77,

    Please refer to :

    CREATE TABLE #temp(IDNO [int], PIID [varchar](10), [Name] [varchar](30), ACCTID [varchar](15), Descr [varchar](50), ADATE [Date], Site [varchar](10), STAT [varchar](10), FLD varchar(10)) Insert into #temp values (1,'AQ1111', 'client 1','a001','dct','01/01/2020','GM','ACTIVE','Z01') Insert into #temp values (2,'AQ1111', 'client 1','a001','dsadct','01/01/2020','AB','ACTIVE','A02') Insert into #temp values (3,'AQ1111', 'client 1','a002','dqe2ct','01/01/2020','HG','ACTIVE','ABS') Insert into #temp values (4,'AQ2222', 'client 1','a003','dc364t','01/01/2020','GM','ACTIVE','Q12') Insert into #temp values (5,'AQ1213', 'client 1','a004','dct','01/01/2020','GM','CANCEL','W23') Insert into #temp values (6,'AQ3333', 'client 1','a005','dct','01/01/2020','GM','ACTIVE','NA') Insert into #temp values (7,'AQ3333', 'client 1','a005','dwafwfect','01/01/2020','GM','CANCEL','M12') Insert into #temp values (8,'AQ3333', 'client 1','a005','d4t43fct','01/01/2020','GM','ACTIVE','S12') Insert into #temp values (9,'AQ3333', 'client 1','a006','dawfwct','01/01/2020','GM','ACTIVE','M12') Insert into #temp values (10,'AQ4444', 'client 1','a006','dct','01/01/2020','GT','ACTIVE','A02') Insert into #temp values (11,'AQ4444', 'client 1','a006','dwafwafct','01/01/2020','RG','CANCEL','A08') Insert into #temp values (12,'AQ4444', 'client 1','a007','dwfaafct','01/01/2020','GM','ACTIVE','A14') Insert into #temp values (13,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A32') Insert into #temp values (14,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A01') Insert into #temp values (15,'AQ5555', 'client 1','a008','dct','01/01/2020','FG','ACTIVE','A03') Insert into #temp values (16,'AQ5555', 'client 1','a008','dct','01/01/2020','AM','ACTIVE','A01') Insert into #temp values (17,'AQ5555', 'client 1','a008','dct','01/01/2020','GM','ACTIVE','A12') Insert into #temp values (18,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A03') Insert into #temp values (19,'AQ5555', 'client 1','a009','dct','01/01/2020','GQ','ACTIVE','A08') Insert into #temp values (20,'AQ5555', 'client 1','a009','dct','01/01/2020','GM','ACTIVE','A50') Insert into #temp values (21,'AQ5555', 'client 1','a010','dct','01/01/2020','GM','ACTIVE','A32') Insert into #temp values (22,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','PENDING','A56') Insert into #temp values (23,'AQ5555', 'client 1','a011','dct','01/01/2020','GM','ACTIVE','S33') select * from #temp with cte as (Select PIID,NAME,ACCTID,Descr,ADATE,FLD,Site,STAT, CASE WHEN FLD = 'A01' THEN 'ADM' WHEN FLD = 'A02' THEN 'MRG' WHEN FLD = 'A03' THEN 'DIS' WHEN FLD = 'A04' THEN 'FWD' WHEN FLD = 'A05' THEN 'UNKNOWN' WHEN FLD = 'A06' THEN 'NA' WHEN FLD = 'A50' THEN 'ACTIVE' WHEN FLD = 'S33' THEN 'PENDING' WHEN FLD = 'Z01' THEN 'COMPLETED' ELSE 'NOT DEFINED' END AS CODE from #temp) ,cte1 as(Select PIID,NAME,ACCTID,Descr,ADATE,FLD,Site,STAT, CASE WHEN FLD = 'ABS' and CODE='NOT DEFINED' THEN 'CANCELLED' WHEN FLD = 'S12' and CODE='NOT DEFINED' THEN 'REJECTED' WHEN FLD = 'Q12' and CODE='NOT DEFINED' THEN 'FAILED' WHEN FLD = 'Z01' and CODE='NOT DEFINED' THEN 'COMPLETED' WHEN FLD = 'ABS' and CODE='NOT DEFINED' THEN 'CANCELLED' WHEN FLD = 'S12' and CODE='NOT DEFINED' THEN 'REJECTED' WHEN FLD = 'Q12' and CODE='NOT DEFINED' THEN 'FAILED' ELSE CODE END AS CODE1 from cte) ,cte2 as(select * from cte where CODE<>'NOT DEFINED' union select * from cte1) select PIID,NAME,ACCTID,Descr,ADATE,CODE from cte2

    drop table #temp

    This issue may be help to you: why-do-linked-servers-have-a-limitation-of-10-branches-in-a-case-expression


    Best Regards   
    Echo



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.





    • Edited by Echo Liuz Monday, August 10, 2020 8:23 AM
    • Marked as answer by dper77 Tuesday, August 11, 2020 12:48 PM
    Monday, August 10, 2020 7:52 AM
  • >> I have an SQL query that has multiple CASE When statements [sic]. <<
    The CASE construct in SQL is an expression and not a statement. Big difference. An expression must return a single scalar value of a known data type.

    >> "CASE expressions may only be nested to level 10." Is there a work around this issue. Following is the sample code <<

    Why do you find that to be unclear? Did you know that a table must have a key. By definition? Why do you like vague column names that are virtually useless? When you take a course in software engineering will tell you that putting keywords in a language in mixed CASE and the user created names in upper case is incredibly bad practice. Did you ever think about why a newspaper uses mixed CASE? It's to make text easier to read. 

    Dates in SQL use the ISO 8601 format, not your local dialect. You might want to look up the ISO 11179 naming rules and learn what an "attribute property" means in data modeling

    Let's try and clean up your attempt and then go from there.

    CREATE TABLE Foobar
    (pi_id VARCHAR(10) NOT NULL, 
     something_name VARCHAR(30) NOT NULL, 
     acct_id VARCHAR(15) NOT NULL, 
     foobar_descr VARCHAR(50) NOT NULL, 
     foobar_date DATE NOT NULL, 
     site_name VARCHAR(10) NOT NULL, 
     foobar_status VARCHAR(10) NOT NULL, 
     control_flg VARCHAR(10) NOT NULL, 
     PRIMARY KEY(pi_id, control_flg));

    I had to guess the primary key since you're still thinking of sequential files and simply numbered the rows in your table. Technically they were rows because you didn't have a table by virtue of not having the key. You should also add some check constraints to guarantee data integrity, but that's another topic.

    INSERT INTO Foobar 
    VALUES 
    ('AQ1111', 'client 1', 'a001', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'Z01'), 
    ('AQ1111', 'client 1', 'a001', 'dsadct', '2020-02-01', 'AB', 'ACTIVE', 'A02'), 
    ('AQ1111', 'client 1', 'a002', 'dqe2ct', '2020-02-01', 'HG', 'ACTIVE', 'ABS'), 
    ('AQ1213', 'client 1', 'a004', 'dct', '2020-02-01', 'GM', 'CANCEL', 'W23'),
    ('AQ2222', 'client 1', 'a003', 'dc364t', '2020-02-01', 'GM', 'ACTIVE', 'Q12'), 
    ('AQ3333', 'client 1', 'a005', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'NA'), 
    ('AQ3333', 'client 1', 'a005', 'dwafwfect', '2020-02-01', 'GM', 'CANCEL', 'M12'),
    ('AQ4444', 'client 1', 'a006', 'dct', '2020-02-01', 'GT', 'ACTIVE', 'A02'),
    ('AQ4444', 'client 1', 'a006', 'dwafwafct', '2020-02-01', 'RG', 'CANCEL', 'A08')
    ('AQ4444', 'client 1', 'a007', 'dwfaafct', '2020-02-01', 'GM', 'ACTIVE', 'A14'),
    ('AQ5555', 'client 1', 'a008', 'dct', '2020-02-01', 'FG', 'ACTIVE', 'A01'),
    ('AQ5555', 'client 1', 'a008', 'dct', '2020-02-01', 'FG', 'ACTIVE', 'A03'),
    ('AQ5555', 'client 1', 'a008', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'A12'),
    ('AQ5555', 'client 1', 'a008', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'A32'),
    ('AQ5555', 'client 1', 'a009', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'A03'),
    ('AQ5555', 'client 1', 'a009', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'A50'),
    ('AQ5555', 'client 1', 'a009', 'dct', '2020-02-01', 'GQ', 'ACTIVE', 'A08'),
    (‘AQ3333', 'client 1', 'a005', 'd4t43fct', '2020-02-01', 'GM', 'ACTIVE', 'S12'),
    (‘AQ3333', 'client 1', 'a006', 'dawfwct', '2020-02-01', 'GM', 'ACTIVE', 'M12'),
    (‘AQ5555', 'client 1', 'a008', 'dct', '2020-02-01', 'AM', 'ACTIVE', 'A01'),
    (21, 'AQ5555', 'client 1', 'a010', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'A32'),
    (22, 'AQ5555', 'client 1', 'a011', 'dct', '2020-02-01', 'GM', 'PENDING', 'A56'),
    (23, 'AQ5555', 'client 1', 'a011', 'dct', '2020-02-01', 'GM', 'ACTIVE', 'S33');

    Notice that you can use a table constructor with an insert statement. This lets the optimizer use the entire table and set of having to do it row by row by row by row the way you posted it. You also don't seem to know the other syntax for CASE expression.

    SELECT something_name, acct_id, descr, foobar_date, 

    -- remove duplicate WHEN clauses, and different syntax 

    CASE control_flg
     WHEN 'A01' THEN 'ADM' 
     WHEN 'A02' THEN 'MRG' 
     WHEN 'A03' THEN 'DIS' 
     WHEN 'A04' THEN 'FWD' 
     WHEN 'A05' THEN 'UNKNOWN' 
     WHEN 'A06' THEN 'NA' 
     WHEN 'A50' THEN 'ACTIVE' 
     WHEN 'S33' THEN 'PENDING' 
     WHEN 'Z01' THEN 'COMPLETED' 
     WHEN 'ABS' THEN 'CANCELLED' 
     WHEN 'S12' THEN 'REJECTED' 
     WHEN 'Q12' THEN 'FAILED' 
     ELSE 'NOT DEFINED'
     END AS foobar_code
    FROM Foobar;

    This still has your original problem of too many when clauses. However, the real problem is that you're not supposed to do display formatting in the query! This is supposed to be done in the presentation layer after the query is completed. In short, you're not writing SQL yet but you are stuck in a mindset of sequential files and COBOL programming. 

    Without have any specs, I would make a guess that you need to sit down and actually design your control flag. I can't see any pattern in your encoding


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, August 10, 2020 9:05 PM
  • >> "CASE expressions may only be nested to level 10." Is there a work around this issue. Following is the sample code <<

    Why do you find that to be unclear? Did you know that a table must have a key. By definition? Why do you like vague column names that are virtually useless? When you take a course in software engineering will tell you that putting keywords in a language in mixed CASE and the user created names in upper case is incredibly bad practice. Did you ever think about why a newspaper uses mixed CASE? It's to make text easier to read. 

    --------------------------------------------------------------------

    Yes I know that a table must have a KEY. This is just a sample code to show my issue to ask for an answer from experts in this forum. It doesn't matter what names I use as columns as long as I can show the error for the experts.

    I was just hoping that there is a work around with all the experts here in the forum.

    I am no expert with SQL. Just trying to get data from a 3rd party database and display in a crystal report. Doing this type of formatting in Crystal slows down the report. So trying to create this in a view and call that from crystal.

    Since I don't have access the to the table design of the 3rd party database, I just included a sample code to create a table and insert the data, so that I can show what the problem is.

    Also thanks for all the suggestions. I will test them out.



    • Edited by dper77 Monday, August 10, 2020 9:43 PM
    Monday, August 10, 2020 9:37 PM
  • I also found the following that works too (as per Echo's post).

    https://dba.stackexchange.com/questions/42837/why-do-linked-servers-have-a-limitation-of-10-branches-in-a-case-expression

    Will be doing more testing.

    Select PIID,NAME,ACCTID,Descr,ADATE ,
    CASE SUBSTRING(FLD, 1, 10)
    WHEN  'A01' THEN 'ADM' 
           WHEN  'A02' THEN 'MRG' 
           WHEN  'A03' THEN 'DIS' 
           WHEN  'A04' THEN 'FWD' 
           WHEN  'A05' THEN 'UNKNOWN' 
           WHEN  'A06' THEN 'NA' 
           WHEN  'A50' THEN 'ACTIVE' 
           WHEN  'S33' THEN 'PENDING' 
           WHEN 'Z01' THEN 'COMPLETED' 
           WHEN  'ABS' THEN 'CANCELLED' 
           WHEN  'S12' THEN 'REJECTED' 
           WHEN  'ABS' THEN 'CANCELLED' 
           ELSE 'NOT DEFINED'
           END AS CODE
    from #temp



    • Edited by dper77 Monday, August 10, 2020 9:57 PM
    • Marked as answer by dper77 Tuesday, August 11, 2020 12:49 PM
    Monday, August 10, 2020 9:53 PM
  • The issue and the error message is*VERY CLEAR* _to me. Yes I know that a table must have a_*KEY*_. This is just a sample code to show my issue to ask for an answer from experts in this forum. It doesn't matter what names I use as columns as long as I can show the error for the experts._

    Ignore this Celko robot. Joe Celko is a well-known <inserted explitive referring to the rear of the body here> who likes to come here and pester people, particluarly those who appear to be inexperienced.
    The issue you ran into is a known-problem with CASE expressions over linked servers, and is less than fun to sort out.

    I see that you have something working based on the post that Echo linked to. The alternative is to use OPENQUERY to sent the query as-is to the remote instance.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Monday, August 10, 2020 9:59 PM