WalMart...

[StephenY]

Just curious, do any of your companies sell to WalMart?

They have this requirement to send them a file periodically so that
they can track orders coming in, and I've been trying to automate the
darned thing with a SQL query.

This is what I've got so far:

--- Begin code ---

use m2mdata01
select 'whatever' as 'Supplier Account Number', somast.fcustpono as
'WalMart PO Number', ' ' as 'Wal-Mart Store Number',
somast.fsono as 'Supplier Sales/Work Order Number', somast.fduedate as
'Estimated Ship Date', shmast.fshipdate as 'Actual Ship Date',
(shmast.fshipdate + 5) as 'Estimated Date of Arrival', ' ' as 'Ship to
Name', sorels.fshptoaddr as 'Ship to Street Address', syaddr.fccity as
'Ship to City',
syaddr.fcstate as 'Ship to State', ' ' as 'Carrier SCAC (Standard
Carrier Alpha Code) Number', ' ' as 'Pro Number or Load Number',
shmast.fbl_lading as 'Bill of Lading Number', ' ' as 'Pallet ID
Number', 'PALLET' as 'Container Type', ' ' as 'Container Number',
'1' as 'Quantity in Container', soitem.fcustpart as 'Wal-Mart Item
Part Number', soitem.fpartno as 'Supplier Item Part Number',
inmast.fdescript as 'Supplier Item Part Number Description', 'NULL' as
'Wal-Mart Component Part Number',
'NULL' as 'Supplier Component Part Number', 'NULL' as 'Supplier
Component Description',
soitem.fquantity as 'Supplier Quantity Ordered',' ' as 'Supplier
Quantity Shipped', ' ' as 'Status', ' ' as 'Ship Condition',
' ' as 'Part Type' from somast
join soitem on soitem.fsono = somast.fsono
join sorels on sorels.fsono = somast.fsono
join shmast on shmast.fcsono = somast.fsono
join inmast on inmast.fpartno = soitem.fpartno and inmast.frev =
soitem.fpartrev
join syaddr on syaddr.fcaddrkey = sorels.fshptoaddr
where somast.fcustno='034745' and somast.fstatus='OPEN'
order by somast.fsono

--- End code ---

Anyway, I added the join to pull the shipping address from table
SYADDR (Yes, I know.. I'm displaying the sorels.fshiptoaddr key
instead of the actual street address right now) to see that it pulls
the correct city and state, and I get this wonderful error message in
Query Analyzer..

"Server: Msg 8650, Level 13, State 127, Line 2
Intra-query parallelism caused your server command (process ID #91) to
deadlock. Rerun the query without intra-query parallelism by using the
query hint option (maxdop 1)."

What does that mean exactly?

TIA

--- Stephen

[Glenn Tobey]

Stephen,

I ran your query (less the customer number in the where clause) and it ran
w/o error. You might want to modify the two lines in your From clause to as
follows. I'm using SQL 2008's equivalent of the query analyzer.

join inmast on inmast.fpartno = soitem.fpartno and inmast.frev =

soitem.fpartrev and InMast.fac = SoItem.fac
join syaddr on fcAlias = 'SLCDPM' and SyAddr.fcAliasKey = SoMast.fCustNo and
SyAddr.fcAddrType = 'S' and syaddr.fcaddrkey = sorels.fshptoaddr

The facility is just in case you ever go to multiple facilities in the
future, the query will still work.
The other Syaddr fields are to make sure you get the Ship To address for the
right customer.

Regardless, I ran it before the modifications w/o any errors.

Glenn Tobey
Oakridge Data Mining - 951.488.9513

[StephenY]

Thanks Glenn! That worked great!

For anyone else that sells to WalMart, here's my completed code:

--- Begin code ---

use m2mdata01

select 'Your Number Here' as 'Supplier Account Number',

somast.fcustpono as 'WalMart PO Number', ' ' as 'Wal-Mart Store
Number',
somast.fsono as 'Supplier Sales/Work Order Number', somast.fduedate as
'Estimated Ship Date', shmast.fshipdate as 'Actual Ship Date',

(shmast.fshipdate + 5) as 'Estimated Date of Arrival', (syaddr.fcfname
+ ' ' + syaddr.fclname) as 'Ship to Name', syaddr.fmstreet as 'Ship to

Street Address', syaddr.fccity as 'Ship to City',
syaddr.fcstate as 'Ship to State', ' ' as 'Carrier SCAC (Standard
Carrier Alpha Code) Number', ' ' as 'Pro Number or Load Number',
shmast.fbl_lading as 'Bill of Lading Number', ' ' as 'Pallet ID
Number', 'PALLET' as 'Container Type', ' ' as 'Container Number',
'1' as 'Quantity in Container', soitem.fcustpart as 'Wal-Mart Item
Part Number', soitem.fpartno as 'Supplier Item Part Number',
inmast.fdescript as 'Supplier Item Part Number Description', 'NULL' as
'Wal-Mart Component Part Number',
'NULL' as 'Supplier Component Part Number', 'NULL' as 'Supplier
Component Description',

soitem.fquantity as 'Supplier Quantity Ordered', shitem.fshipqty as

'Supplier Quantity Shipped', ' ' as 'Status', ' ' as 'Ship Condition',

' ' as 'Part Type', somast.fstatus from somast

join soitem on soitem.fsono = somast.fsono
join sorels on sorels.fsono = somast.fsono
join shmast on shmast.fcsono = somast.fsono
join inmast on inmast.fpartno = soitem.fpartno and inmast.frev =

soitem.fpartrev and InMast.fac = SoItem.fac
join syaddr on fcAlias = 'SLCDPM' and SyAddr.fcAliasKey =
SoMast.fCustNo and SyAddr.fcAddrType = 'S' and syaddr.fcaddrkey =
sorels.fshptoaddr

join shitem on shitem.fshipno = shmast.fshipno
where somast.fcustno='xxxxxx'and somast.fstatus='OPEN'
order by somast.fsono desc

--- End code ---

[StephenY]

Hmm.. I just realized I have a problem with this code. If the item
hasn't shipped, it does not find a result, probably because there
won't be any entries in the SHMAST or SHITEM tables until it ships.

That's not good. How do I fill in those null results to get the rest
of the line to show up?

[StephenY]

Doh! Left join on SHMAST and SHITEM...!

yay!