sql statements need help combining into 1 statement

I have 2 sql statements that both work well.  I need to combine them to one statement to accomplish the task i need.

The following is the main select statement that I need to incorporate a case statment into.

SELECT     OIBT.ItemCode, OIBT.BatchNum, POR1.ShipDate, POR1.OpenQty, OIBT.InDate, OPOR.CardCode, OPOR.CardName,
                      DATEDIFF(day, OIBT.InDate, POR1.ShipDate) AS dayslate
FROM         OIBT INNER JOIN
                      OPDN ON OIBT.BaseType = OPDN.ObjType AND OIBT.BaseNum = OPDN.DocNum INNER JOIN
                      PDN1 ON OPDN.DocEntry = PDN1.DocEntry INNER JOIN
                      OPOR INNER JOIN
                      POR1 ON OPOR.DocEntry = POR1.DocEntry ON PDN1.BaseType = OPOR.ObjType AND PDN1.BaseEntry = OPOR.DocEntry AND
                      PDN1.BaseLine = POR1.LineNum
WHERE     (OPOR.U_SIF_PO_Sample = 'Y')


I need to change the Datediff part for the field of POR1.shipdate instead i want the result from the following case statement.

select CASE WHEN por1.U_SIF_Vendprom IS NULL then por1.shipdate else por1.U_SIF_Vendprom END as REQDATE from por1


in the end i would like the above statement with   Datediff(day,OIBT.InDate, REQDATE) AS dayslate


I did try this many ways but could not get it to work.
notasgoodasyouAsked:
Who is Participating?
 
derekkrommConnect With a Mentor Commented:
SELECT     OIBT.ItemCode, OIBT.BatchNum, POR1.ShipDate, POR1.OpenQty, OIBT.InDate, OPOR.CardCode, OPOR.CardName,
                      DATEDIFF(day, OIBT.InDate, isnull(POR1.U_SIF_VENDPROM, POR1.ShipDate)) AS dayslate
FROM         OIBT INNER JOIN
                      OPDN ON OIBT.BaseType = OPDN.ObjType AND OIBT.BaseNum = OPDN.DocNum INNER JOIN
                      PDN1 ON OPDN.DocEntry = PDN1.DocEntry INNER JOIN
                      OPOR INNER JOIN
                      POR1 ON OPOR.DocEntry = POR1.DocEntry ON PDN1.BaseType = OPOR.ObjType AND PDN1.BaseEntry = OPOR.DocEntry AND
                      PDN1.BaseLine = POR1.LineNum
WHERE     (OPOR.U_SIF_PO_Sample = 'Y')
0
 
Rajkumar GsSoftware EngineerCommented:
I am not clear what you mean.
I understood like you want incoperate the case statement that you give to the above query
If so try this query
SELECT  OIBT.ItemCode ,
        OIBT.BatchNum ,
        POR1.ShipDate ,
        POR1.OpenQty ,
        OIBT.InDate ,
        OPOR.CardCode ,
        OPOR.CardName ,
        CASE WHEN POR1.U_SIF_Vendprom IS NULL THEN POR1.shipdate
             ELSE POR1.U_SIF_Vendprom
        END AS REQDATE ,
        DATEDIFF(day, OIBT.InDate, POR1.ShipDate) AS dayslate
FROM    OIBT
        INNER JOIN OPDN ON OIBT.BaseType = OPDN.ObjType
                           AND OIBT.BaseNum = OPDN.DocNum
        INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry
        INNER JOIN OPOR
        INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry ON PDN1.BaseType = OPOR.ObjType
                                                            AND PDN1.BaseEntry = OPOR.DocEntry
                                                            AND PDN1.BaseLine = POR1.LineNum
WHERE   ( OPOR.U_SIF_PO_Sample = 'Y' )

Open in new window


Raj
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
just wondering on the type of Join shouldn't it be LEFT JOIN instead of inner join ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.