?
Solved

T-SQL Use of the "IN" Clause

Posted on 2013-12-09
8
Medium Priority
?
699 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
7 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 41

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

864 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