Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql statements need help combining into 1 statement

Posted on 2011-02-28
3
Medium Priority
?
302 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:notasgoodasyou
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34999266
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
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 34999272
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34999279
just wondering on the type of Join shouldn't it be LEFT JOIN instead of inner join ?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question