Sybase - CASE-WHEN Question

sorry  whats the problem

if you only have a sequence number of 2 then @v_CustUniqNum_1 will be null

also one of the result columns will always be null on each of the rows...

 

Yep,
Can you provide example table data and resultset you want to see ?

OK. I figured out the answer. When there are two rows returned for "lcxr.SequenceNum in (1, 2)" for ex: 444, 555, for CASE 1 (@v_CustUniqNum_1), it takes  (444, NULL) and the second value NULL is assigned as a latest assignment. So I get NULL for @v_CustUniqNum_1. Whereas, @v_CustUniqNum_2 gets (NULL,555) in CASE 2. So 555 is assigned correctly. Hence I included a 'SUM' function to both the CASEs to suppress the NULL.

select @v_CustUniqNum_1 = SUM (
        case
        when lcxr.SequenceNum = 1 then
             ct.CustUniqNum
        end ),
        @v_CustUniqNum_2 = SUM (
        case
        when lcxr.SequenceNum = 2 then
             ct.CustUniqNum
        end )
   from Cust ct, LeadCustXRef lcxr
   where lcxr.LeadUniqNum = @v_LeadUniqNum
        and lcxr.SequenceNum in (1, 2)
        and ct.CustUniqNum = lcxr.CustUniqNum

Pl. let me know, if anyone knows a better way to do this. Thanks for all your comments.

Why don't you use the ISNULL function around the variable equations like so:

select @v_CustUniqNum_1 =
        isnull (case
        when lcxr.SequenceNum = 1 then
             ct.CustUniqNum
        end,0),
        @v_CustUniqNum_2 =
        isnull(case
        when lcxr.SequenceNum = 2 then
             ct.CustUniqNum
        end,0)
   from Cust ct, LeadCustXRef lcxr
   where lcxr.LeadUniqNum = @v_LeadUniqNum
        and lcxr.SequenceNum in (1, 2)
        and ct.CustUniqNum = lcxr.CustUniqNum

Otherwise, you could use the ELSE feature:

select @v_CustUniqNum_1 =
        case
        when lcxr.SequenceNum = 1 then
             ct.CustUniqNum
        ELSE 0
        end,
        @v_CustUniqNum_2 =
        case
        when lcxr.SequenceNum = 2 then
             ct.CustUniqNum
        ELSE 0
        end
   from Cust ct, LeadCustXRef lcxr
   where lcxr.LeadUniqNum = @v_LeadUniqNum
        and lcxr.SequenceNum in (1, 2)
        and ct.CustUniqNum = lcxr.CustUniqNum

You need to have sum in ordrer to go from two row resultset  to one row.
Another potential problem is that if you have more then one row with lcxr.SequenceNum in (1, 2) you will get
incorrect result (sum of CustUniqNum).
You can replace 'sum' with 'Max' so you will get correct numbers.

Since the lcxr.SequenceNum is part of key in that table, there is no chance to have 2 SequencNum  as same values (Ex: 1,1 or 2,2). Hence, both SUM and MAX works in this scenario.