0

have a query that I would appreciate some help on, I'm not too sure why its not working.

Select 
  ContactID
, CASE 
     WHEN Special_credit = '0' AND Credit_Check_Status__c = 'Approve' THEN 'SENT' 
     ELSE NULL 
  END as Sent_1
, CONVERT (DATE, GETDATE()) as Sent_1_date
, Sent_RFS
, Sent_RFS_date
, Sent_Welcome
, Sent_Welcome_date
, Serial_number
, Active
, Credit_Check_Status__c 
from Submitted_Applications_Master 
where 
Sent_1_date > '1/01/1900' 
AND Sent_1_date is not null 
AND Sent_1 = 'SENT'

IF SOMEONE COULD TELL ME THE PROBLEM AS TO WHY THE 'Sent_1' & 'Sent_1_date' are not being populated even though there is records that seem to meet the criteria.

If more info is needed to understand this question please let me know.

2
  • Can you please update your question to include the full query? Jan 25 at 22:26
  • Select ContactID, CASE WHEN Special_credit = '0' AND Credit_Check_Status__c = 'Approve' THEN 'SENT' ELSE NULL END as Sent_1, CONVERT (DATE, GETDATE()) as Sent_1_date, Sent_RFS, Sent_RFS_date, Sent_Welcome, Sent_Welcome_date, Serial_number, Active, Credit_Check_Status__c from Submitted_Applications_Master where Sent_1_date > '1/01/1900' AND Sent_1_date is not null AND Sent_1 = 'SENT' Jan 25 at 22:32

1 Answer 1

2

You can't refer to aliases in the where-clause directly. You'll have to repeat the criteria:

Select 
  ContactID
, CASE 
     WHEN Special_credit = '0' AND Credit_Check_Status__c = 'Approve' THEN 'SENT' 
  END as Sent_1
, CONVERT(DATE, GETDATE()) as Sent_1_date
, Sent_RFS
, Sent_RFS_date
, Sent_Welcome
, Sent_Welcome_date
, Serial_number
, Active
, Credit_Check_Status__c 
from Submitted_Applications_Master 
where 
CONVERT(DATE, GETDATE()) is not null 
AND CONVERT(DATE, GETDATE()) > '1900-01-01' 
AND 
  CASE 
     WHEN Special_credit = '0' AND Credit_Check_Status__c = 'Approve' THEN 'SENT' 
  END = 'SENT'

...or use an outer query:

select
  x.ContactID
, x.Sent_1
, x.Sent_1_date
, x.Sent_RFS
, x.Sent_RFS_date
, x.Sent_Welcome
, x.Sent_Welcome_date
, x.Serial_number
, x.Active
, x.Credit_Check_Status__c
from (

  Select 
    ContactID
  , CASE 
       WHEN Special_credit = '0' AND Credit_Check_Status__c = 'Approve' THEN 'SENT' 
    END as Sent_1
  , CONVERT(DATE, GETDATE()) as Sent_1_date
  , Sent_RFS
  , Sent_RFS_date
  , Sent_Welcome
  , Sent_Welcome_date
  , Serial_number
  , Active
  , Credit_Check_Status__c 
  from Submitted_Applications_Master

) x
where 
x.sent_1_date is not null 
AND x.sent_1_date > '1900-01-01' 
AND x.Sent_1 = 'SENT'

Also, no need to check for null in your CASE statement. If you don't provide an ELSE, that's what gets returned by default.

Last thing -- 01/01/1900 is an ambiguous date format, it'd be better to use an ISO date -- 1900-01-01.

1
  • Thanks for the help Adam, ill try out the outer query Jan 26 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.