Solved

T-SQL Use of the "IN" Clause

Posted on 2013-12-09
8
647 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get column names from table in vb.net 8 49
migrate a SQL 2008 to 2016, 2 33
Add a step to a system backup job 6 16
SQL Recursion schedule 13 14
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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