• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

If Statement in SQL Stored Procedure

I have the select statement below.

I am trying to create an if statement if the alias DAY is Null or > 0 then a new alias Late should = 1 otherwise it would be zero.  Everything I tried messes up the prior selections.

SELECT    distinct  ph.NAME_VND_ORDFM as Name_Vend,ph.ID_PO as PO_NBR,  pl.ID_REL_ORD AS PO_REL,pl.ID_LINE_PO AS PO_LN,  CONVERT(VARCHAR,pl.DATE_RCV,101)as RECEIPT,
                      CONVERT(VARCHAR,pl.DATE_RQST,101) as REQUEST, DATEDIFF(DAY, pl.DATE_RQST,pl.DATE_RCV) AS DAY

Any help will be appreciated.
0
Scamquist
Asked:
Scamquist
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
SELECT    distinct  ph.NAME_VND_ORDFM as Name_Vend,ph.ID_PO as PO_NBR,  pl.ID_REL_ORD AS PO_REL,pl.ID_LINE_PO AS PO_LN,  CONVERT(VARCHAR,pl.DATE_RCV,101)as RECEIPT,
                      CONVERT(VARCHAR,pl.DATE_RQST,101) as REQUEST, DATEDIFF(DAY, pl.DATE_RQST,pl.DATE_RCV) AS DAY
, CASE WHEN ISNULL( DATEDIFF(DAY, pl.DATE_RQST,pl.DATE_RCV), 100) > 0 THEN 1 ELSE 0 END IsLate 

Open in new window


you cannot use the "alias" directly in the same query level, you have to repeat the expression. the only place where you could use the alias is in the ORDER BY part.
0
 
ScamquistAuthor Commented:
Your answer worked.  Can you explain the syntax, mainly the ,100)>0

CASE WHEN ISNULL( DATEDIFF(DAY, pl.DATE_RQST,pl.DATE_RCV), 100) > 0 THEN 1 ELSE 0 END IsLate
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the value returned is null, the isnull(xxx, 100) will return 100.
as you said that null value returned should also display 1 as result, I used 100 as value, 1 or 99999 would have worked the same way.
0
 
ScamquistAuthor Commented:
Thank you for the assist and the explaination.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now