Member Avatar for niladri.prima

Hi All,

In a table (dbo.UnquotedContacts) I have few columns from where I am trying to extract few columns. But My crcern is as follows

IF the field 'Date_ABC_Updated' is NOT empty AND the field 'subscribed_to_Business_XL' = 'SUB', the contact has an ACTIVE BXL subscription

IF the field 'Date_ABC_Updated' is NOT empty AND the field 'subscribed_to_Business_XL' does NOT contain a value (IS EMPTY), the the contact has an INACTIVE BXL subscription. I have tried a query

SELECT  source,
    unique_contact_code,
    bxl_amount_paid,Date_ABC_Updated,       
    (Case 

    WHEN Date_ABC_Updated IS not null and Subscribed_to_Business_XL is null then 'Active'
    when Date_ABC_Updated IS not null and Subscribed_to_Business_XL IS null then 'Inactive'     
End )
    Subscribed_to_Business_XL,
    Audit_Q,
    Name_of_Company_Requestee,
    Job_Title_of_Company_Requestee,
    bxl_sub_type,
    bxl_amount_paid

FROM dbo.UnquotedContacts

But it is not working

Please help!!!!

Edited by pritaeas because: fixed code tags
  • 4 Contributors
  • 5 Replies
  • 111 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for pritaeas

But it is not working

Explain what's not working. Show error messages if you have them.

My guess is that Subscribed_to_Business_XL as alias won't work.

Member Avatar for ChrisHunter

Put AS after the brackets and before Subscribed_to_Business_XL see if that works.

Edited by ChrisHunter
Member Avatar for BitBlt

You simply neglected to change the criteria on the two parts of the CASE clause. Look at this:

SELECT  source,
    unique_contact_code,
    bxl_amount_paid,
    Date_ABC_Updated,       
    (Case 
    WHEN Date_ABC_Updated IS not null and Subscribed_to_Business_XL = 'SUB' then 'Active'    -- <------ Notice this! 
    when Date_ABC_Updated IS not null and Subscribed_to_Business_XL IS null then 'Inactive'
    End) Subscribed_to_Business_XL,
    Audit_Q,
    Name_of_Company_Requestee,
    Job_Title_of_Company_Requestee,
    bxl_sub_type,
    bxl_amount_paid
FROM dbo.UnquotedContacts

I built a little test table in SQL2008, populated it with junk data and tried this statement out. It seemed to work fine, and even didn't care about the alias for the "Case" column having the same name as a table column.

Hope it works out for you too! Good luck!

Edited by BitBlt
Member Avatar for niladri.prima

Hi All,

thanks for prompt reply

it is now working...............

Niladri Sekahr Biswas

Member Avatar for BitBlt

Good news! Please mark the thread as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.