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.
| Author |
Topic |
|
Adam West
Constraint Violating Yak Guru
261 Posts |
 Posted - 2009-10-06 : 10:22:10
|
| I found out that I have a critical error in this SQL. The field Itmgedsc, is not a good field for the group description. The correct field I need is located in the table IV40600 in the field UserCatLongDescr. The table IV40600 has only 3 fields: USCATVAL, USCATNUM, USCATLONGDESCR. The Table IV00101 field ITMGEDSC would correspond to USCATVAL. I would need to match those and then get the USCATLONGDESCR, without disturbing the current smooth running of this SQL, I have my doubts as to how to do this: SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity, DATEADD(WK, DATEDIFF(WK, 6, SOP10100.DOCDATE), 6) AS ID, SOP10200.ITEMNMBR + CHAR(13) + IV00101.ITEMDESC + CHAR(13) + ''Par Level:'' AS ITEMDESC, IV00101.ITMGEDSCFROM dbo.SOP10200 AS SOP10200 INNER JOIN dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 10:36:16
|
Just addinner join IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC and add the field UserCatLongDescr to your select list.If IV40600.USCATVAL = IV00101.ITMGEDSC has always matches then the result should give the same records.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-06 : 10:41:53
|
| Two questions:Are [USCATVAL] values unique in the table [IV40600]? --this will determine if we can do a simple JOIN or not.Does column [ITMGEDSC] in [IV00101] allow NULL values? --this would determine if we need LEFT OUTER or INNER join to [IV00101].Be One with the OptimizerTG |
|
|
|
Adam West
Constraint Violating Yak Guru
261 Posts |
Posted - 2009-10-06 : 10:53:19
|
| 1. They appear to be unique, yes.2. allows Null, yes. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-06 : 11:08:30
|
| Then you can follow webfred's advice but change the "inner join" to "left outer join". Add that after all the inner joins.Do your tables have primary key? They should, you know...Be One with the OptimizerTG |
|
|
|
Adam West
Constraint Violating Yak Guru
261 Posts |
Posted - 2009-10-06 : 13:11:35
|
| Thank you Fred and TG, it looks great, the tables are from Great Plains so that they have the primary key. |
|
|
|
|
|
|
|
|
Need to add a new table into this SQL