asked on

T-SQL Use of the "IN" Clause

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

TBSupport

ASKER

I will remember that, next time.

I look back to hearing more on this.

Thank you, for your time!

TBSupport

Kyle Abrahams, PMP

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

Paul Jackson

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)

TBSupport

ASKER

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

ASKER CERTIFIED SOLUTION

TBSupport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

TBSupport

ASKER

This was the exact T-SQL programming required, as the client verified so yesterday afternoon.

T-SQL Use of the &quot;IN&quot; Clause

T-SQL Use of the "IN" Clause