Solved

T-SQL Use of the "IN" Clause

Posted on 2013-12-09
8
664 Views
Last Modified: 2013-12-16
Hello:

The portion of the query shown below prior to the "UNION ALL" section is a T-SQL statement that I use in a view to derive items where Standard Cost - Unit Cost is not equal to zero.

The clause in the query after the "UNION ALL" is designed to show those same items but where Standard Cost - Unit Cost is equal to zero.  In other words, items should be returned from the clause in the query after "UNION ALL", but only where those items match items returned prior to "UNION ALL".

I want to see items that are in the section prior to UNION ALL.  As far as the section of the query after the UNION ALL is concerned, I only want to see those matching items that are in the section prior to UNION ALL.

But, this query is returning items that are in the section prior to UNION ALL plus items that are not in the section prior to UNION ALL.

I hope that makes sense.  What I'm trying to say is that I want to see items (records) returned from the section of the query prior to UNION ALL plus items (records) from the section after the UNION ALL that match those items (records) returned prior to UNION ALL.

Please let me know, if you have any questions.

Thanks!  Much appreciated!

TBSupport


SELECT     dbo.IV30300.DOCDATE AS StartDate, dbo.IV30300.DOCNUMBR AS DocumentNo, dbo.IV30300.TRXQTY AS Qty, dbo.IV30300.ITEMNMBR AS Item,
                      dbo.IV00101.ITEMDESC AS ItemDescription, dbo.IV30300.UNITCOST, dbo.IV00101.STNDCOST AS StandardCost,
                      dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST AS Variance, dbo.PM00200.VENDORID AS Vendor, dbo.PM00200.VENDNAME AS VendorName
FROM         dbo.IV30300 INNER JOIN
                      dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE     (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%')
and dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST <> 0
AND dbo.IV30300.DOCDATE BETWEEN '2013-12-01 00:00:00.000' AND '2013-12-31 00:00:00.000'
UNION ALL
SELECT     dbo.IV30300.DOCDATE AS StartDate, dbo.IV30300.DOCNUMBR AS DocumentNo, dbo.IV30300.TRXQTY AS Qty, dbo.IV30300.ITEMNMBR AS Item,
                      dbo.IV00101.ITEMDESC AS ItemDescription, dbo.IV30300.UNITCOST, dbo.IV00101.STNDCOST AS StandardCost,
                      dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST AS Variance, dbo.PM00200.VENDORID AS Vendor, dbo.PM00200.VENDNAME AS VendorName
FROM         dbo.IV30300 INNER JOIN
                      dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE     (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%')
AND dbo.IV30300.DOCDATE BETWEEN '2013-12-01 00:00:00.000' AND '2013-12-31 00:00:00.000'
and dbo.IV30300.ITEMNMBR IN (
SELECT     dbo.IV30300.ITEMNMBR
FROM         dbo.IV30300 INNER JOIN
                      dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE     (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%')
and dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST = 0
AND dbo.IV30300.DOCDATE BETWEEN '2013-12-01 00:00:00.000' AND '2013-12-31 00:00:00.000')
ORDER BY dbo.IV30300.ITEMNMBR
0
Comment
Question by:TBSupport
[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
8 Comments
 
LVL 1

Author Comment

by:TBSupport
ID: 39706154
I will remember that, next time.

I look back to hearing more on this.  

Thank you, for your time!

TBSupport
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39706202
is this a one time thing just for validation or you need to see this every time?

The easiest way is to use temp tables:
your code with inserting into temp tables
SELECT     dbo.IV30300.DOCDATE AS StartDate, dbo.IV30300.DOCNUMBR AS DocumentNo, dbo.IV30300.TRXQTY AS Qty, dbo.IV30300.ITEMNMBR AS Item, 
                      dbo.IV00101.ITEMDESC AS ItemDescription, dbo.IV30300.UNITCOST, dbo.IV00101.STNDCOST AS StandardCost, 
                      dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST AS Variance, dbo.PM00200.VENDORID AS Vendor, dbo.PM00200.VENDNAME AS VendorName
into #temp1
FROM         dbo.IV30300 INNER JOIN
                      dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE     (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%')
and dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST <> 0
AND dbo.IV30300.DOCDATE BETWEEN '2013-12-01 00:00:00.000' AND '2013-12-31 00:00:00.000'






SELECT     dbo.IV30300.DOCDATE AS StartDate, dbo.IV30300.DOCNUMBR AS DocumentNo, dbo.IV30300.TRXQTY AS Qty, dbo.IV30300.ITEMNMBR AS Item, 
                      dbo.IV00101.ITEMDESC AS ItemDescription, dbo.IV30300.UNITCOST, dbo.IV00101.STNDCOST AS StandardCost, 
                      dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST AS Variance, dbo.PM00200.VENDORID AS Vendor, dbo.PM00200.VENDNAME AS VendorName
into #temp2
FROM         dbo.IV30300 INNER JOIN
                      dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE     (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%')
AND dbo.IV30300.DOCDATE BETWEEN '2013-12-01 00:00:00.000' AND '2013-12-31 00:00:00.000'
and dbo.IV30300.ITEMNMBR IN (
SELECT     dbo.IV30300.ITEMNMBR
FROM         dbo.IV30300 INNER JOIN
                      dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE     (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%')
and dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST = 0
AND dbo.IV30300.DOCDATE BETWEEN '2013-12-01 00:00:00.000' AND '2013-12-31 00:00:00.000')
ORDER BY dbo.IV30300.ITEMNMBR 

Open in new window


select * from #temp1  -- top part of union
select * from #temp2  -- bottom part of union

select * from #temp1 union all
select * from #temp2  -- original results
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39706214
No that doesn't make sense at all, the first section of tsql returns rows where standard cost minus unit cost in not equal to zero.
In the second set of tsql after the union all you say you want to only include rows that also match the first part of the tsql however you want the rows where standard cost minus unit cost is equal to zero.
There will be zero rows met by the 2nd set of tsql if it has to meet the criteria of the first set of tsql first because they use where clause,s that are the opposite of each other (1st not equal to zero, 2nd equal to zero)
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 1

Author Comment

by:TBSupport
ID: 39706256
ged325>>it's something that I need to see every time.

jacko82>>I agree that it doesn't make much sense.  But, that's what the customer wants to see.  They claim that there is such data in their system.

TBSupport
0
 
LVL 1

Accepted Solution

by:
TBSupport earned 0 total points
ID: 39708556
OK.  Let me state this another way.

Below is my T-SQL query which I think is correct, but the logic is being questioned.

My query shows purchases and, specifically, those purchases where there is a variance between Standard Cost and Unit Cost.  That's the first clause of my query.  The second clause of my query, after the UNION ALL, is to show purchases where there is no variance.  But, those records are to only reflect Item Numbers in the first clause.

So, the query is to show (a) purchase variances and (b) purchase with no variances but containing only Item Numbers from (a).  

I know that that sounds contradictory.  But, the customer says that (b) does indeed exist in Microsoft Dynamics GP and they need to report on both (a) and (b).

(b) is shown in the portion of the second clause where I state "dbo.IV30300.ITEMNMBR IN".

Again, the data that my query is returning is being questioned.  I'm being told that not enough of (b) is being returned.  Can someone please review this query and let me know where I went wrong?

Thanks!  Much appreciated!

TBSupport


SELECT dbo.IV30300.DOCDATE AS StartDate, dbo.IV30300.DOCNUMBR AS DocumentNo, dbo.IV30300.TRXQTY AS Qty, dbo.IV30300.ITEMNMBR AS Item,
 dbo.IV00101.ITEMDESC AS ItemDescription, dbo.IV30300.UNITCOST, dbo.IV00101.STNDCOST AS StandardCost,
 dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST AS Variance, dbo.PM00200.VENDORID AS Vendor, dbo.PM00200.VENDNAME AS VendorName
FROM dbo.IV30300 INNER JOIN
 dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
 dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%') AND
 dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST <> 0
and dbo.IV30300.DOCDATE between '2013-01-01 00:00:00.000' and '2013-11-30 00:00:00.000'
UNION ALL
SELECT dbo.IV30300.DOCDATE AS StartDate, dbo.IV30300.DOCNUMBR AS DocumentNo, dbo.IV30300.TRXQTY AS Qty, dbo.IV30300.ITEMNMBR AS Item,
 dbo.IV00101.ITEMDESC AS ItemDescription, dbo.IV30300.UNITCOST, dbo.IV00101.STNDCOST AS StandardCost,
 dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST AS Variance, dbo.PM00200.VENDORID AS Vendor, dbo.PM00200.VENDNAME AS VendorName
FROM dbo.IV30300 INNER JOIN
 dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
 dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%') AND
 dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST = 0
and dbo.IV30300.DOCDATE between '2013-01-01 00:00:00.000' and '2013-11-30 00:00:00.000'
AND dbo.IV30300.ITEMNMBR IN
(SELECT dbo.IV30300.ITEMNMBR
FROM dbo.IV30300 INNER JOIN
 dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
 dbo.PM00200 ON dbo.IV30300.CUSTNMBR = dbo.PM00200.VENDORID
WHERE (dbo.IV30300.DOCTYPE = '4') AND (dbo.IV00101.VCTNMTHD = '4') AND (dbo.PM00200.VENDORID NOT LIKE 'XFR%') AND
 dbo.IV00101.STNDCOST - dbo.IV30300.UNITCOST <> 0)
and dbo.IV30300.DOCDATE between '2013-01-01 00:00:00.000' and '2013-11-30 00:00:00.000'
ORDER BY dbo.IV30300.ITEMNMBR
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39719448
Just a couple of comments:
1.  Doing an ORDER BY on a UNION'd statement with a column from a table does not make a lot of sense and in fact in more recent versions of SQL Server causes an error.  You should use the alias instead.
2.  Using aliases makes it easier to read.  As in:
SELECT  i300.DOCDATE AS StartDate,
        i300.DOCNUMBR AS DocumentNo,
        i300.TRXQTY AS Qty,
        i300.ITEMNMBR AS Item,
        i101.ITEMDESC AS ItemDescription,
        i300.UNITCOST,
        i101.STNDCOST AS StandardCost,
        i101.STNDCOST - i300.UNITCOST AS Variance,
        p200.VENDORID AS Vendor,
        p200.VENDNAME AS VendorName
FROM    dbo.IV30300 i300
        INNER JOIN dbo.IV00101 i101 ON i300.ITEMNMBR = i101.ITEMNMBR
        INNER JOIN dbo.PM00200 p200 ON i300.CUSTNMBR = p200.VENDORID
WHERE   i300.DOCTYPE = '4'
        AND i101.VCTNMTHD = '4'
        AND p200.VENDORID NOT LIKE 'XFR%'
        AND i101.STNDCOST - i300.UNITCOST <> 0
        AND i300.DOCDATE BETWEEN '2013-01-01 00:00:00.000' AND '2013-11-30 00:00:00.000'
UNION ALL
SELECT  i300.DOCDATE AS StartDate,
        i300.DOCNUMBR AS DocumentNo,
        i300.TRXQTY AS Qty,
        i300.ITEMNMBR AS Item,
        i101.ITEMDESC AS ItemDescription,
        i300.UNITCOST,
        i101.STNDCOST AS StandardCost,
        i101.STNDCOST - i300.UNITCOST AS Variance,
        p200.VENDORID AS Vendor,
        p200.VENDNAME AS VendorName
FROM    dbo.IV30300 i300
        INNER JOIN dbo.IV00101 i101 ON i300.ITEMNMBR = i101.ITEMNMBR
        INNER JOIN dbo.PM00200 p200 ON i300.CUSTNMBR = p200.VENDORID
WHERE   i300.DOCTYPE = '4'
        AND i101.VCTNMTHD = '4'
        AND p200.VENDORID NOT LIKE 'XFR%'
        AND i101.STNDCOST - i300.UNITCOST = 0
        AND i300.DOCDATE BETWEEN '2013-01-01 00:00:00.000' AND '2013-11-30 00:00:00.000'
        AND i300.ITEMNMBR IN (SELECT    i300.ITEMNMBR
                              FROM      dbo.IV30300 i300
                                        INNER JOIN dbo.IV00101 i101 ON i300.ITEMNMBR = i101.ITEMNMBR
                                        INNER JOIN dbo.PM00200 p200 ON i300.CUSTNMBR = p200.VENDORID
                              WHERE     i300.DOCTYPE = '4'
                                        AND i101.VCTNMTHD = '4'
                                        AND p200.VENDORID NOT LIKE 'XFR%'
                                        AND i101.STNDCOST - i300.UNITCOST <> 0)
        AND i300.DOCDATE BETWEEN '2013-01-01 00:00:00.000' AND '2013-11-30 00:00:00.000'
ORDER BY Item

Open in new window

0
 
LVL 1

Author Closing Comment

by:TBSupport
ID: 39721054
This was the exact T-SQL programming required, as the client verified so yesterday afternoon.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 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