Need to add logic to SQL join - 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

General SQL Server Forums

New to SQL Server Programming

Need to add logic to SQL join

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-17 : 19:17:19

HI I have a union SQL that joins Current and History records.

This works fine except there are 2 quirks that have to be accounted for. I would like to account for this in the SQL rather than the reporting level.
1. If an item appears 2 times on the same SOP document it won't get counted onto the report.
2. Averages are made if items are purchased multiple times at differing prices.

The SQL:
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source
FROM ((((PBS.dbo.SOP10200
SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))
INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR
UNION ALL
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' as source
FROM ((((PBS.dbo.SOP30300
SOP30300 LEFT OUTER JOIN PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))
LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-18 : 05:31:11

This isn't a dig it's a serious point....

I was going to look into this -- don't think that it is going to be a hard problem. But...

I couldn't be bothered trying to format your code into something readable.

Tidy it up and it'll be so much easier to debug and change.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-18 : 10:37:06

Charlie, I am very new to SQL so I don't know what you mean about tidying it up. It was a long time to get this to work. If there are any links that show what I should do to tidy up by all means I can address it. This is the SQL that Crystal Reports runs. but it was done manually rather than having Crystal link the tables.

pootle_flump

1064 Posts

Posted - 2009-05-18 : 10:45:09

paste it into the forum code tags - this preserves formatting and changes font to fixed width.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-18 : 10:46:49

there you go. Nice and neat


SELECT
        IV00101.ITMGEDSC,
        IV00101.ITEMDESC,
        RM00101.CUSTNAME,
        IV40600.UserCatLongDescr,
        CATS.UserCatLongDescr,
        SOP10200.ITEMNMBR,
        SOP10200.SOPNUMBE,
        SOP10200.QUANTITY,
        SOP10200.OXTNDPRC,
        SOP10200.SOPTYPE,
        SOP10100.DOCDATE,
        'Current' AS source
FROM    
        PBS.dbo.SOP10200 SOP10200 
        INNER JOIN PBS.dbo.IV00101 IV00101      ON      SOP10200.ITEMNMBR       = IV00101.ITEMNMBR
        INNER JOIN PBS.dbo.SOP10100 SOP10100    ON      SOP10200.SOPTYPE        = SOP10100.SOPTYPE
                                                AND     SOP10200.SOPNUMBE       = SOP10100.SOPNUMBE
        INNER JOIN PBS.dbo.IV40600 IV40600      ON      IV00101.ITMGEDSC        = IV40600.USCATVAL
        INNER JOIN PBS.dbo.IV40600 CATS         ON      IV00101.USCATVLS_2      = CATS.USCATVAL
        INNER JOIN PBS.dbo.RM00101 RM00101      ON      SOP10100.CUSTNMBR       = RM00101.CUSTNMBR
        
UNION ALL

SELECT
        IV00101.ITMGEDSC,
        IV00101.ITEMDESC,
        RM00101.CUSTNAME,
        IV40600.UserCatLongDescr,
        CATS.UserCatLongDescr,
        SOP30300.ITEMNMBR,
        SOP30300.SOPNUMBE,
        SOP30300.QUANTITY,
        SOP30300.OXTNDPRC,
        SOP30300.SOPTYPE,
        SOP30200.DOCDATE,
        'History' AS source
FROM 
        PBS.dbo.SOP30300 SOP30300 
        LEFT OUTER JOIN PBS.dbo.IV00101 IV00101         ON      SOP30300.ITEMNMBR       = IV00101.ITEMNMBR
        INNER JOIN PBS.dbo.SOP30200 SOP30200            ON      SOP30300.SOPTYPE        = SOP30200.SOPTYPE
                                                        AND     SOP30300.SOPNUMBE       = SOP30200.SOPNUMBE
        LEFT OUTER JOIN PBS.dbo.IV40600 IV40600         ON      IV00101.ITMGEDSC        = IV40600.USCATVAL
        LEFT OUTER JOIN PBS.dbo.IV40600 CATS            ON      IV00101.USCATVLS_2      = CATS.USCATVAL
        INNER JOIN PBS.dbo.RM00101 RM00101              ON      SOP30200.CUSTNMBR       = RM00101.CUSTNMBR

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-18 : 10:50:31

There are a lot of pages on the net' for writing readable SQL.

You could try starting here:
http://www.communitymx.com/content/article.cfm?cid=A5B54EAAE1BC138F

Basically -- SQL is a language where it is extremely easy to write horrible code and unfortunately most code produced by GUI front ends produces unreadable garbage.

khtan's formatted your code.

can you give some sample data and tell us exactly what you need changed?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-18 : 11:03:30

Well first I did change the inner joins of the first part to left outer joins for the IV406 table.

When I run this and the records I get I sum a field called OXTNDPRC,
which is extended price, I don't get the exact amounts that the application gets. I was told that possibly, this is due to the 2 issues I mentioned,

1. If an item appears 2 times on the same SOP document it won't get counted onto the report.
2. Averages are made if items are purchased multiple times at differing prices.

Since I am getting higher amounts this might make some sense. but I don't know for sure so I just wanted to try making this alteration to the SQL.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-18 : 11:45:30

Does this work?


SELECT
	ITMGEDSC
	, ITEMDESC
	, CUSTNAME
	, UserCatLongDescr
	, UserCatLongDescr
	, ITEMNMBR
	, SOPNUMBE
	, QUANTITY
	, AVG(OXTNDPRC) AS AvgOXTNDPRC
	, SOPTYPE
	, DOCDATE
	, source
FROM
	(
		SELECT DISTINCT
				IV00101.ITMGEDSC,
				IV00101.ITEMDESC,
				RM00101.CUSTNAME,
				IV40600.UserCatLongDescr,
				CATS.UserCatLongDescr,
				SOP10200.ITEMNMBR,
				SOP10200.SOPNUMBE,
				SOP10200.QUANTITY,
				SOP10200.OXTNDPRC,
				SOP10200.SOPTYPE,
				SOP10100.DOCDATE,
				'Current' AS source
		FROM    
				PBS.dbo.SOP10200 SOP10200 
				INNER JOIN PBS.dbo.IV00101 IV00101      ON      SOP10200.ITEMNMBR       = IV00101.ITEMNMBR
				INNER JOIN PBS.dbo.SOP10100 SOP10100    ON      SOP10200.SOPTYPE        = SOP10100.SOPTYPE
														AND     SOP10200.SOPNUMBE       = SOP10100.SOPNUMBE
				INNER JOIN PBS.dbo.IV40600 IV40600      ON      IV00101.ITMGEDSC        = IV40600.USCATVAL
				INNER JOIN PBS.dbo.IV40600 CATS         ON      IV00101.USCATVLS_2      = CATS.USCATVAL
				INNER JOIN PBS.dbo.RM00101 RM00101      ON      SOP10100.CUSTNMBR       = RM00101.CUSTNMBR
		        
		UNION ALL

		SELECT DISTINCT
				IV00101.ITMGEDSC,
				IV00101.ITEMDESC,
				RM00101.CUSTNAME,
				IV40600.UserCatLongDescr,
				CATS.UserCatLongDescr,
				SOP30300.ITEMNMBR,
				SOP30300.SOPNUMBE,
				SOP30300.QUANTITY,
				SOP30300.OXTNDPRC,
				SOP30300.SOPTYPE,
				SOP30200.DOCDATE,
				'History' AS source
		FROM 
				PBS.dbo.SOP30300 SOP30300 
				LEFT OUTER JOIN PBS.dbo.IV00101 IV00101         ON      SOP30300.ITEMNMBR       = IV00101.ITEMNMBR
				INNER JOIN PBS.dbo.SOP30200 SOP30200            ON      SOP30300.SOPTYPE        = SOP30200.SOPTYPE
																AND     SOP30300.SOPNUMBE       = SOP30200.SOPNUMBE
				LEFT OUTER JOIN PBS.dbo.IV40600 IV40600         ON      IV00101.ITMGEDSC        = IV40600.USCATVAL
				LEFT OUTER JOIN PBS.dbo.IV40600 CATS            ON      IV00101.USCATVLS_2      = CATS.USCATVAL
				INNER JOIN PBS.dbo.RM00101 RM00101              ON      SOP30200.CUSTNMBR       = RM00101.CUSTNMBR
	)
	results
GROUP BY
	ITMGEDSC
	, ITEMDESC
	, CUSTNAME
	, UserCatLongDescr
	, UserCatLongDescr
	, ITEMNMBR
	, SOPNUMBE
	, QUANTITY
	, SOPTYPE
	, DOCDATE
	, source

The Distinct should eliminate duplicates in your two SELECT statements. And then an average grouped by the other fields on the OXTNDPRC column

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 12:06:40

As far as formatting goes, there are many things to look at. I'd suggest looking for ANSI standards or compliance (aliasing tables and columns for example, you should use "AS" instead of "=", etc) As for actual formatting, there are several tools available, one free one that does an ok job is: http://www.sqlinform.com/

Cheers!

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-18 : 12:16:41

Charlie, this is might strong code! It is failing on "UsercatLongDesc" was specified multiple times for results. I took out the dups though and it still failed.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-18 : 12:26:38

Ah -- didn't see you were returning the same column name from two tables there!

Try this:


SELECT
	ITMGEDSC
	, ITEMDESC
	, CUSTNAME
	, [userCatLongDescr]
	, [UserCatLongDescr_CATS]
	, ITEMNMBR
	, SOPNUMBE
	, QUANTITY
	, AVG(OXTNDPRC) AS AvgOXTNDPRC
	, SOPTYPE
	, DOCDATE
	, source
FROM
	(
		SELECT DISTINCT
				IV00101.ITMGEDSC,
				IV00101.ITEMDESC,
				RM00101.CUSTNAME,
				IV40600.UserCatLongDescr AS [userCatLongDescr],
				CATS.UserCatLongDescr AS [UserCatLongDescr_CATS],
				SOP10200.ITEMNMBR,
				SOP10200.SOPNUMBE,
				SOP10200.QUANTITY,
				SOP10200.OXTNDPRC,
				SOP10200.SOPTYPE,
				SOP10100.DOCDATE,
				'Current' AS source
		FROM    
				PBS.dbo.SOP10200 SOP10200 
				INNER JOIN PBS.dbo.IV00101 IV00101      ON      SOP10200.ITEMNMBR       = IV00101.ITEMNMBR
				INNER JOIN PBS.dbo.SOP10100 SOP10100    ON      SOP10200.SOPTYPE        = SOP10100.SOPTYPE
														AND     SOP10200.SOPNUMBE       = SOP10100.SOPNUMBE
				INNER JOIN PBS.dbo.IV40600 IV40600      ON      IV00101.ITMGEDSC        = IV40600.USCATVAL
				INNER JOIN PBS.dbo.IV40600 CATS         ON      IV00101.USCATVLS_2      = CATS.USCATVAL
				INNER JOIN PBS.dbo.RM00101 RM00101      ON      SOP10100.CUSTNMBR       = RM00101.CUSTNMBR
		        
		UNION ALL

		SELECT DISTINCT
				IV00101.ITMGEDSC,
				IV00101.ITEMDESC,
				RM00101.CUSTNAME,
				IV40600.UserCatLongDescr AS [userCatLongDescr],
				CATS.UserCatLongDescr AS [UserCatLongDescr_CATS],
				SOP30300.ITEMNMBR,
				SOP30300.SOPNUMBE,
				SOP30300.QUANTITY,
				SOP30300.OXTNDPRC,
				SOP30300.SOPTYPE,
				SOP30200.DOCDATE,
				'History' AS source
		FROM 
				PBS.dbo.SOP30300 SOP30300 
				LEFT OUTER JOIN PBS.dbo.IV00101 IV00101         ON      SOP30300.ITEMNMBR       = IV00101.ITEMNMBR
				INNER JOIN PBS.dbo.SOP30200 SOP30200            ON      SOP30300.SOPTYPE        = SOP30200.SOPTYPE
																AND     SOP30300.SOPNUMBE       = SOP30200.SOPNUMBE
				LEFT OUTER JOIN PBS.dbo.IV40600 IV40600         ON      IV00101.ITMGEDSC        = IV40600.USCATVAL
				LEFT OUTER JOIN PBS.dbo.IV40600 CATS            ON      IV00101.USCATVLS_2      = CATS.USCATVAL
				INNER JOIN PBS.dbo.RM00101 RM00101              ON      SOP30200.CUSTNMBR       = RM00101.CUSTNMBR
	)
	results
GROUP BY
	ITMGEDSC
	, ITEMDESC
	, CUSTNAME
	, [userCatLongDescr]
	, [UserCatLongDescr_CATS]
	, ITEMNMBR
	, SOPNUMBE
	, QUANTITY
	, SOPTYPE
	, DOCDATE
	, source

and see if that gets you any further.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-18 : 12:37:32

Charlie, this one did attach to the tables but in Crystal there is a error message that {Command.OXTNDPRC} field is not known.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-18 : 13:06:31

However it works as a SQL Server Management query.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-18 : 13:27:07

I was wanting to take the result of the Sequel Server Query and place ther results in a table that Crystal reports would open. As it was not able to run this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-18 : 13:50:38

Two distincts and an UNION ALL is faster than no distinct and an UNION?

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-19 : 04:27:57

D'Oh -- Peso is of course right!

don't need the DISTINCT if you change to use UNION on it's own.

Example


(
          SELECT '4' AS [foo]
UNION ALL SELECT '4'
)
UNION
(
	      SELECT '5' AS [foo]
UNION ALL SELECT '5'
)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources