SQL Help - 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 2000 Forums

SQL Server Development (2000)

SQL Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-16 : 11:55:29

This query displays the open orders for the sale's reps. The only problem is, if the rep does not have any open orders, it wont display them at all.

How can I have it display 0 and their slspsn_no ?

SELECT SUM((OEORDLIN_SQL.Qty_Ordered * OEORDLIN_SQL.Unit_Price) * ((100 - OEORDLIN_SQL.Discount_Pct) / 100)) AS domestic,
OEORDHDR_SQL.Slspsn_No
FROM OEORDLIN_SQL INNER JOIN
OEORDHDR_SQL ON OEORDLIN_SQL.Ord_Type = OEORDHDR_SQL.Ord_Type AND OEORDLIN_SQL.Ord_No = OEORDHDR_SQL.Ord_No
WHERE (OEORDHDR_SQL.Ord_Type = 'O') AND (OEORDHDR_SQL.Slspsn_No NOT IN ('109', '112', '119', '120', '125')) AND
(OEORDHDR_SQL.Ord_Dt < 20040716) AND (OEORDHDR_SQL.User_Def_Fld_5 = 'outbound')
GROUP BY OEORDHDR_SQL.Slspsn_No

Sorry so sloppy, is there a way to format this?

jharwood
Starting Member

41 Posts

Posted - 2004-07-16 : 12:20:28

Inner joins only return matching values. Have a look at outer joins in Bol. The way your tables are in the join, it would probably be a right outer join. You also may need to use isnull around your sum statement.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-16 : 14:13:26

This should work for you:


SELECT 		h.Slspsn_No,
		SUM(ISNULL(l.Qty_Ordered * l.Unit_Price * (100 - l.Discount_Pct) / 100,0)) AS domestic
FROM 		OEORDHDR_SQL		h
LEFT JOIN 	OEORDLIN_SQL		l
ON 		l.Ord_Type 		= h.Ord_Type
AND 		l.Ord_No 		= h.Ord_No
WHERE 		h.Ord_Type 		= 'O'
AND		h.Slspsn_No		
NOT IN 		(
		'109',
		'112',
		'119',
		'120',
		'125'
		)
AND 		h.Ord_Dt 		< 20040716
AND		h.User_Def_Fld_5 	= 'outbound'
GROUP BY 	h.Slspsn_No

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-16 : 14:19:25

I figured out what part of the problem is, These 2 tables do not list all the Sales Reps Number.
I have another table that contains all of the sales reps numbers, so I would have to include this table too.

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-16 : 15:26:18

I still cant figure this out. I have another table that lists all of the Slspsn_no
ARSLMFIL_SQL.Slspsn_No

It still wont display all of the reps

SELECT ARSLMFIL_SQL.Slspsn_No, SUM((OEORDLIN_SQL.Qty_Ordered * OEORDLIN_SQL.Unit_Price) * ((100 - OEORDLIN_SQL.Discount_Pct) / 100))
AS domestic
FROM OEORDLIN_SQL INNER JOIN
OEORDHDR_SQL ON OEORDLIN_SQL.Ord_No = OEORDHDR_SQL.Ord_No AND
OEORDLIN_SQL.Ord_Type = OEORDHDR_SQL.Ord_Type RIGHT OUTER JOIN
ARSLMFIL_SQL ON OEORDHDR_SQL.Slspsn_No = ARSLMFIL_SQL.Slspsn_No
WHERE (OEORDHDR_SQL.Ord_Dt < 20040716) AND (OEORDHDR_SQL.User_Def_Fld_5 = 'outbound') AND (OEORDHDR_SQL.Slspsn_No NOT IN ('109', '112',
'119', '120', '125')) AND (OEORDHDR_SQL.Ord_Type = 'O')
GROUP BY ARSLMFIL_SQL.Slspsn_No

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources