May 28, 2009 at 9:21 am
My T-SQL works but has one problem.
(c.POBuyersItemKey = b.ponbr + '|' + b.BuyersItemNbrP0107)
For some reason I am getting a record in my results that does not meet this criteria. I do have a record in the Factory table that partially matches the key on ponbr but it is a different BuyersItemNbr.
I really don't know how to go about debugging this. I have changed it to a exists select and have took the left 13 of b.BuyersItemNbrP0107 to try and isolate what I am doing wrong.
I suspect that it does not like this contatenation. Maybe I need to be more complex and declare these earlier to prevent this but don't know what to do.
Appreciate any advise. :w00t:
Cheers
Select a.TranactionSetPurpose, a.POKey, a.Division, a.QuickResponse, a.SendID, a.GSNbr, a.SetNbr, a.TransDate, a.InboundFilename, a.POExtFlag, a.PODateBEG05, a.CurrencyCUR02, a.DeptNbrREF02, a.InternalVendorNbrREF02, a.QuickRespRefNbrREF02, a.CityStateFOB03, a.OriginShippingPointFOB03, a.TermsDescrITD12, a.ItemCountCTT01
from PO a, POItem b, Factory c, HTS d
where a.pokey = b.POnbr and
a.POextFlag = 'N' and
b.VendorStyleNbrPO109 = d.StyleKey and
(c.POBuyersItemKey = b.ponbr + '|' + b.BuyersItemNbrP0107)
group by a.TranactionSetPurpose, a.POKey, a.Division, a.QuickResponse, a.SendID, a.GSNbr, a.SetNbr, a.TransDate, a.InboundFilename, a.POExtFlag, a.PODateBEG05, a.CurrencyCUR02, a.DeptNbrREF02, a.InternalVendorNbrREF02, a.QuickRespRefNbrREF02, a.CityStateFOB03, a.OriginShippingPointFOB03, a.TermsDescrITD12, a.ItemCountCTT01
order by a.POKey
May 28, 2009 at 9:57 am
What is the value that's coming through that shouldn't?
Would be easier to help on this if I had table definitions and data that would duplicate the problem. Is that something you can provide?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 28, 2009 at 10:07 am
Try also printing out the intermediate values. Either with PRINT or a SELECT to your query results. It will help you figure out what's being compared. You can also SELECT from the table using that concatenation.
May 28, 2009 at 10:31 am
GSquared I will pull these things together. I did read the etiquette article. My tables are rather big so I will have to trim everything down in test and create a small example that will be manageable.
Steve, I did a
Select * from ISFfactory where POBuyersItemKey = '5967241' + '|' + '19-62006-9-20'
and it pulled a record out of the table. If you can point me to an example of displaying the intermediate values that would help. That went write over my head.
Thanks for jumping in there I will pull things together.
Dave
May 28, 2009 at 10:48 am
Something like this:
Select POBuyersItemKey
, substring( POBuyersItemKey, 1, 7)
, substring( POBuyersItemKey, 8, 1)
, '5967241' + '|' + '19-62006-9-20'
from ISFfactory
order by POBuyersItemKey
Be sure that you have matching rows, or find the samples that match. You might have things that aren't matched up. Maybe add a WHERE to catch the 8th character in the key that's a pipe and see if you lose rows.
May 28, 2009 at 11:27 am
I started creating a small database to understand better what was happening. I was not able to recreate the issue. I happen to look at the item file and found that one of the items matched the criteria. Most of the items have the same buyersitem number or style. In this purchase order the items had different buyers item numbers.
So the question restated correctly is how one pulls only orders that have all items that meet this criteria.
(as above)
where a.pokey = b.POnbr and
a.POextFlag = 'N' and
b.VendorStyleNbrPO109 = d.StyleKey and
(c.POBuyersItemKey = b.ponbr + '|' + b.BuyersItemNbrP0107)
I will continue to build a test scenerio for better assistance but didn't want everyone to be confused with my post.
Dave
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply