What is wrong with my code?

Question

What is wrong with my code?

djustice 20821

Old Hand

Points: 326
More actions
February 24, 2012 at 11:27 am

#252183

I have a DB that used to be in Access. I moved all of the tables from Access to SQL and am in the process of moving the queries to views. I copied the following code right from Access into SQL Query analyzer.
The problem is it works great in Access, but returns multiple duplicates when run in SQL? For example this code below in Access returns 2 rows, but in SQL returns 16 rows. The 2 that access returns with 7 duplicates of each row.
My guess is it has to do with the joins, and I have tried to figure it out, but am having no luck.
Thanks for any help.
SELECT c.DOC_NUM, b.LINE_NUM, c.INVOICE_NUM, b.ITEM_NUM, b.DESCRIPTION_1, a.CUSTOMER_NAME, c.SHIP_REF_DATE, b.QTY_SHIPPED, b.AMT_ORDERED, b.AMT_COMMITTED, b.AMT_SHIPPED, b.AMT_BACKORDERED, b.AMT_TOTAL_SHIP, b.COST, b.PRICE, b.UM_STOCKING, b.UM_PRICING, b.UM_SELLING, [amt_shipped]*[cost] AS [Ext cost], [amt_shipped]*[price] AS [Ext Price]
FROM AR_CUST_MAST a INNER JOIN (SO_PAST_INV_LINE b INNER JOIN SO_PAST_INV_HEAD c
ON (b.INVOICE_NUM = c.INVOICE_NUM)
AND (b.DOC_NUM = c.DOC_NUM))
ON a.CUSTOMER_NUM = c.CUSTOMER_NUM
WHERE (((b.ITEM_NUM)<>'m')
AND ((a.CUSTOMER_NAME)='IOB')
AND ((c.SHIP_REF_DATE) Between '10/1/2002' And '10/2/2002'));

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

DDL scripts, sample data, and expected results would be extremely helpful in troubleshooting your query.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

djustice 20821 Old Hand Points: 326 More actions · Answer 2

Here is the data I'm getting with most of the columns deleted. Too much to put in here: If you look at the Item_Num there are only 2 unique item numbers. I want only 2 rows returned, which it does in Access. I am really wondering why it returns the correct info in Access, but not in SQL? All of the tables and relationships are identical and the SQL code is the same? The 2nd set is what I expect to be returned.

DOC_NUMLINE_NUMINVOICE_NUMITEM_NUMDESCRIPTION_1

3069672604618445-01-396-8105 NECK TAB, WOMEN'S SHIRT

306968260461COMM-S NIB COMMISSIONS

3069672604618445-01-396-8105 NECK TAB, WOMEN'S SHIRT

306968260461COMM-S NIB COMMISSIONS

DOC_NUMLINE_NUMINVOICE_NUMITEM_NUMDESCRIPTION_1

3069672604618445-01-396-8105 NECK TAB, WOMEN'S SHIRT

306968260461COMM-S NIB COMMISSIONS

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

By chance - did you run your import several times?

I'd check each table to make sure you know what is really in there.

Also - you might care to check in Access whether the "distinct values" flag was set in the query properties. If so - it might not show the DISTINCT in the query code.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sturner SSC-Insane Points: 22411 More actions · Answer 4

That is not a proper join clause, though it may work (somehow) for Access.

try this:

SELECT (your select list)

FROM AR_CUST_MAST a

INNER JOIN SO_PAST_INV_HEAD c ON a.CUSTOMER_NUM = c.CUSTOMER_NUM

AND c.SHIP_REF_DATE Between '10/1/2002' And '10/2/2002'

INNER JOIN SO_PAST_INV_LINE b ON b.INVOICE_NUM = c.INVOICE_NUM

AND b.DOC_NUM = c.DOC_NUM and b.ITEM_NUM<>'m'

WHERE a.CUSTOMER_NAME='IOB'

The probability of survival is inversely proportional to the angle of arrival.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

sturner (2/24/2012)
That is not a proper join clause, though it may work (somehow) for Access.
try this:
SELECT (your select list)
FROM AR_CUST_MAST a
INNER JOIN SO_PAST_INV_HEAD c ON a.CUSTOMER_NUM = c.CUSTOMER_NUM
AND c.SHIP_REF_DATE Between '10/1/2002' And '10/2/2002'
INNER JOIN SO_PAST_INV_LINE b ON b.INVOICE_NUM = c.INVOICE_NUM
AND b.DOC_NUM = c.DOC_NUM and b.ITEM_NUM<>'m'
WHERE a.CUSTOMER_NAME='IOB'

Technically it's not improper. It does not follow standard ways to express the joins, but is syntactically valid nonetheless

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

djustice 20821 Old Hand Points: 326 More actions · Answer 6

Well, you called it Matt. I did import these tables twice. I imported these 3 last week and then when I imported the rest yesterday, I forgot to exclude these 3 tables. DUH. The code is returning the data that I expected now.

Thank you everyone!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

djustice 20821 (2/24/2012)
Well, you called it Matt. I did import these tables twice. I imported these 3 last week and then when I imported the rest yesterday, I forgot to exclude these 3 tables. DUH. The code is returning the data that I expected now.
Thank you everyone!

Don't feel bad. I wouldn't have jumped that quickly to the answer if I hadn't pulled that trick a few times myself!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?