IN (SELECT)? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

IN (SELECT)?

Author

Topic

techsupport14
Starting Member

18 Posts

Posted - 2014-08-05 : 00:04:04

I think I need an IN (SELECT) statement ...

What I need to do:

An invoice can have one line or many lines.

In some of the invoices there is the item and a processing fee.

Sometimes there's just a processing fee on a single invoice.

I do not want to see the single processing fee invoices however I need to see single transaction invoices (as long as it's not the processing fee only).

How do I write this to show me all invoice detail records EXCEPT when the invoice has only the processing fee on it?

I was thinking the IN (SELECT), though don't know how to right it really, but to use that to see something like
ONLY when the ITEM = FEE the count of the InvNo has to be greater than 1 else don't show the invoice.
something like this (though it's not working and why I'm seeking help)
CODE
(dbo.AE_DeferredMemberships.InvNo IN
(SELECT InvNo
FROM dbo.AE_DeferredMemberships AS AE_DeferredMemberships_1
WHERE ClassID = ‘ADMINFEE’
GROUP BY InvNo
HAVING (COUNT(1) > 1))) AND (dbo.AE_DeferredSalesMembers.YearEnd > dbo.AE_DeferredMembershipsDynamics.PaidYear)

As well as there are invoices with the same ItemDescription but a return invoice starting with RTN for refunding the original invoice.
How I can tell so far is that the ItemDescription is the same along with the obvious customber number is the same, but the InvNo field is different with the original invno and the RTN#.

What would be the correct syntax to handle this scenario?

Thank you. I am very new to SQL coding.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-05 : 08:53:55

Please post:

1. CREATE TABLE statements to create test tables for your query
2. INSERT INTO statements to populate the tables
3. The results of your query executed against the test tables
4. The desired results of your query.

techsupport14
Starting Member

18 Posts

Posted - 2014-08-05 : 11:02:43

[code]
SELECT RTRIM(USFSA.dbo.SOP30200.SOPNUMBE) AS InvNo, RTRIM(USFSA.dbo.IV00101.ITMCLSCD) AS ClassID, RTRIM(USFSA.dbo.SOP30300.ITEMNMBR)
AS Item, CASE WHEN RTRIM(USFSA.dbo.IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'ADMIN FEE' ELSE MemberGroup END AS MbrGroup,
CASE WHEN RTRIM(USFSA.dbo.IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'Processing Fee' ELSE Description END AS Type, RTRIM(USFSA.dbo.SOP30300.ITEMDESC)
AS InvItemDesc, CASE WHEN USFSA.dbo.SOP30200.SOPNUMBE LIKE 'RTN%' THEN XTNDPRCE * - 1 ELSE XTNDPRCE END AS ExtPrice,
USFSA.dbo.SOP30200.DOCDATE AS PaymentDate, YEAR(USFSA.dbo.SOP30200.DOCDATE) AS PaidYear, MONTH(USFSA.dbo.SOP30200.DOCDATE) AS PaidMonth,
RTRIM(USFSA.dbo.SOP30200.CUSTNMBR) AS CustNo, CASE WHEN RTRIM(USFSA.dbo.SOP30200.CUSTNMBR)
LIKE 'PER%' THEN ' Individual' ELSE RTRIM(USFSA.dbo.SOP30200.CUSTNAME) END AS OrgName, CASE WHEN CONVERT(INT,
dbo.GetNumeric(RTRIM(USFSA.dbo.SOP30300.ITEMDESC))) = 0 THEN MembershipNumber ELSE CONVERT(INT, dbo.GetNumeric(RTRIM(USFSA.dbo.SOP30300.ITEMDESC)))
END AS MbrNoGP, entity.Person.LastName + ', ' + entity.Person.FirstName AS Name, entity.Person.MembershipNumber AS USFSANo,
RTRIM(USFSA.dbo.SOP30200.CUSTNAME) AS GPName
FROM USFSA.dbo.SOP30300 INNER JOIN
USFSA.dbo.IV00101 ON USFSA.dbo.SOP30300.ITEMNMBR = USFSA.dbo.IV00101.ITEMNMBR INNER JOIN
USFSA.dbo.SOP30200 ON USFSA.dbo.SOP30300.SOPNUMBE = USFSA.dbo.SOP30200.SOPNUMBE LEFT OUTER JOIN
entity.Person ON USFSA.dbo.SOP30200.CUSTNMBR = entity.Person.Id LEFT OUTER JOIN
lookup.MemberTypes ON USFSA.dbo.IV00101.ITEMNMBR = lookup.MemberTypes.ProductCode
WHERE (lookup.MemberTypes.Id BETWEEN 1 AND 10 OR
lookup.MemberTypes.Id BETWEEN 22 AND 31 OR
lookup.MemberTypes.Id BETWEEN 41 AND 44 OR
lookup.MemberTypes.Id IS NULL) AND (RTRIM(USFSA.dbo.IV00101.ITMCLSCD) IN ('DUES-MEMB', 'DUES BSCOA', 'DUES-BSMEM', 'DUES-FOFS', 'DUES-COACH', 'ADMINFEE'))
AND (YEAR(USFSA.dbo.SOP30200.DOCDATE) >= YEAR(USFSA.dbo.SOP30200.DOCDATE) - 1) AND (MONTH(USFSA.dbo.SOP30200.DOCDATE) < 7) AND
(NOT (RTRIM(USFSA.dbo.SOP30200.CUSTNMBR) LIKE 'TEA%')) AND EXISTS dbo.SOP30200.SOPNUMBE IN
(SELECT dbo.SOP30200.SOPNUMBE AS InvNo
FROM dbo.SOP30200 AS SOP30200_1
WHERE IV00101.ITMCLSCD = 'ADMINFEE'
GROUP BY dbo.SOP30200.SOPNUMBE
HAVING (COUNT(*) > 1)))[/code]

Incorrect syntax near dbo (on the last part where I am trying to do the IN SELECT statement.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-05 : 11:36:42

OK -- first off, this is really hard to read. the main reason is that you are not using table aliases. FOr example, if, in the first FROM clause, you said:


FROM     USFSA.dbo.SOP30300 SOP303

Then you can replace ever occurrence of 'USFSA.dbo.SOP30300' with 'SOP303', thereby shortening all the column references and the whole query text

Second, try moving the 'INNER JOIN' to the start of the line like this:


FROM     USFSA.dbo.SOP30300 SOP303 
  INNER JOIN USFSA.dbo.IV00101 IV101
     ON SOP303.ITEMNMBR = IV101.ITEMNMBR
...

Third, (this may be the cause of the error), in your query, you join on 'USFSA.dbo.SOP30200.SOPNUMBE' but try to refer to the column without the linked server name '... AND EXISTS dbo.SOP30200.SOPNUMBE '

Aliasing your table names will definitely help with that.

Fourth, try stacking the Where clauses. e.g. not this


WHERE x AND y ANX z

but


WHERE x
  AND y
  AND z

As you do this, you might find missing parentheses and other problems

Finally, without some test data (see my earlier post), It's well-nigh impossible to debug your query.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-05 : 12:57:43

To do that, you use <> {type = 'fee' OR IN_statement}.

Here's the shell of the full statement, but without the details on the IN statement, as I don't fully understand what you need. The table name in the original IN doesn't match the last one you posted.



SELECT RTRIM(SOP30200.SOPNUMBE) AS InvNo, RTRIM(IV00101.ITMCLSCD) AS ClassID, RTRIM(SOP30300.ITEMNMBR) AS Item, 
    CASE WHEN RTRIM(IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'ADMIN FEE' ELSE MemberGroup END AS MbrGroup, 
    CASE WHEN RTRIM(IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'Processing Fee' ELSE Description END AS Type, 
    RTRIM(SOP30300.ITEMDESC) AS InvItemDesc, 
    CASE WHEN SOP30200.SOPNUMBE LIKE 'RTN%' THEN XTNDPRCE * - 1 ELSE XTNDPRCE END AS ExtPrice, 
    SOP30200.DOCDATE AS PaymentDate, YEAR(SOP30200.DOCDATE) AS PaidYear, MONTH(SOP30200.DOCDATE) AS PaidMonth, 
    RTRIM(SOP30200.CUSTNMBR) AS CustNo, 
    CASE WHEN RTRIM(SOP30200.CUSTNMBR) LIKE 'PER%' THEN ' Individual' ELSE RTRIM(SOP30200.CUSTNAME) END AS OrgName, 
    CASE WHEN CONVERT(INT, dbo.GetNumeric(RTRIM(SOP30300.ITEMDESC))) = 0 THEN MembershipNumber 
         ELSE CONVERT(INT, dbo.GetNumeric(RTRIM(SOP30300.ITEMDESC))) 
         END AS MbrNoGP, 
    Person.LastName + ', ' + Person.FirstName AS Name, Person.MembershipNumber AS USFSANo, 
    RTRIM(SOP30200.CUSTNAME) AS GPName
FROM     USFSA.dbo.SOP30300 SOP30300 INNER JOIN
                  IV00101 IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
                  SOP30200 SOP30200 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
                  entity.Person Person ON SOP30200.CUSTNMBR = Person.Id LEFT OUTER JOIN
                  lookup.MemberTypes ON IV00101.ITEMNMBR = lookup.MemberTypes.ProductCode
WHERE  (lookup.MemberTypes.Id BETWEEN 1 AND 10 OR
                  lookup.MemberTypes.Id BETWEEN 22 AND 31 OR
                  lookup.MemberTypes.Id BETWEEN 41 AND 44 OR
                  lookup.MemberTypes.Id IS NULL) AND 
       (IV00101.ITMCLSCD IN ('DUES-MEMB', 'DUES BSCOA', 'DUES-BSMEM', 'DUES-FOFS', 'DUES-COACH', 'ADMINFEE')) AND
       (YEAR(SOP30200.DOCDATE) >= YEAR(SOP30200.DOCDATE) - 1) AND 
       (MONTH(SOP30200.DOCDATE) < 7) AND 
       (NOT (RTRIM(SOP30200.CUSTNMBR) LIKE 'TEA%')) AND 
       (IV00101.ITMCLSCD <> 'ADMINFEE' OR
        {<IN statement goes here>})

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources