Solved

T-SQL Use of the "IN" Clause

Posted on 2013-12-09
8
628 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
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 39

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now