rgolenor
asked on
nest or loop sql output to xml
I have an SQL table that I'm exporting to xml. I would like however to nest or loop the data.
These are the columns in the table: DOC_NO, SCHEDULED_DATE, ORDER_DATE, OPS_NOTES, ITEM_CODE, NOTE, QTY_ORDERED, DELIV_METH,
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY,
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
I would like the xlm file to be organized so the file would be something like this:
Order 1
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
item 3
exworks
op_notes
Order 2
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
exworks
op_notes
Order 3
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
item 3
item 4
exworks
op_notes
Here is the code I'm using to export the table in case it is of any use:
SELECT @FILENAME = '\\IC08ST\ORDERS\AG_ORDERS
SELECT @SQLCMD = 'BCP ' +
'"SELECT * ' +
' FROM EVEREST.DBO.A6_AG ' +
' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
' QUERYOUT ' +
@FILENAME +
' -c -T '
EXECUTE MASTER..XP_CMDSHELL @SQLCMD
-----
Is this possible when exporting from an SQL table?
Thank in advance for your assistance.
These are the columns in the table: DOC_NO, SCHEDULED_DATE, ORDER_DATE, OPS_NOTES, ITEM_CODE, NOTE, QTY_ORDERED, DELIV_METH,
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY,
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
I would like the xlm file to be organized so the file would be something like this:
Order 1
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
item 3
exworks
op_notes
Order 2
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
exworks
op_notes
Order 3
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
item 3
item 4
exworks
op_notes
Here is the code I'm using to export the table in case it is of any use:
SELECT @FILENAME = '\\IC08ST\ORDERS\AG_ORDERS
SELECT @SQLCMD = 'BCP ' +
'"SELECT * ' +
' FROM EVEREST.DBO.A6_AG ' +
' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
' QUERYOUT ' +
@FILENAME +
' -c -T '
EXECUTE MASTER..XP_CMDSHELL @SQLCMD
-----
Is this possible when exporting from an SQL table?
Thank in advance for your assistance.
yes please, and I have a particular question or two...
Is that information really in just one table?
or is that a temporary table produced by a stored procedure or query?
It may be far more efficient to use the normalized base tables.
e.g. your list above indicates a variable number of items per Order Detail, so I would have expected more than one table to be involved.
so if EVEREST.DBO.A6_AG is not the origin of the information, please provide the table definitions that are the origin.
Is that information really in just one table?
or is that a temporary table produced by a stored procedure or query?
It may be far more efficient to use the normalized base tables.
e.g. your list above indicates a variable number of items per Order Detail, so I would have expected more than one table to be involved.
so if EVEREST.DBO.A6_AG is not the origin of the information, please provide the table definitions that are the origin.
ASKER
Paul, that is a temporary table.. The data is accumulated from tables: invoice, x_invoice and address. Invoice contains the basic order information, x_invoice contains the line items associated with the order and address contains the shipping and contact address information.
Saurv, I will see about gathering some sample data.
Saurv, I will see about gathering some sample data.
>> I will see about gathering some sample data.
from this I hope:
basic order information, x_invoice contains the line items associated with the order and address contains the shipping and contact address information.
could we see the query/proc that produces the temp table too?
from this I hope:
basic order information, x_invoice contains the line items associated with the order and address contains the shipping and contact address information.
could we see the query/proc that produces the temp table too?
ASKER
Here is the query that is producing the temporary table: Forgive anything silly in the query .. I'm self taught and do what I know works..
SELECT A.DEP, A.DOC_ALIAS, A.DOC_NO, A.DATE_FLD AS SCHEDULED_DATE, A.ORDER_DATE, A.PO AS CUST_PO, B.ITEM_CODE, B.NOTE,
A.SHIPPED, B.ITEM_QTY, B.QTY_SHIP AS QTY_SHIPPED, C.NAME AS SHIP_NAME, C.STREET_ADDRESS AS SHIP_ADDRESS, C.CITY AS SHIP_CITY,
C.STATE AS SHIP_STATE, C.ZIP AS SHIP_ZIP, C.COUNTRY AS SHIP_COUNTRY, A.DELIV_METH, A.CUSTMEMO4 AS AG_NOTES, A.CUSTCHAR9
AS EXWORKS, B.SEQUENCE, A.CONT_CODE
INTO A1_AG
FROM INVOICES A, X_INVOIC B, ADDRESS C
WHERE A.STATUS = 8 AND A.DATE_FLD >= DATEADD (DAY, -60, GETDATE( ))AND A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
AND A.SHIP_CODE = C.ADDR_CODE AND A.DOC_ALIAS = 'AG SALES ORDER' AND A.DEP = 'AG'
ORDER BY A.DOC_NO, B.SEQUENCE ASC
-->>ADD THE S.O. CONTACT INFORMATION
SELECT *
INTO A2_AG
FROM A1_AG A LEFT OUTER JOIN ADDRESS B
ON A.CONT_CODE = B.ADDR_CODE
-->>GRAB THE COLUMNS WE WANT (ADD CONTACT INFO TO LIST)
SELECT DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, AG_NOTES, ITEM_CODE, NOTE, ITEM_QTY AS QTY_ORDERED, QTY_SHIPPED, DELIV_METH,
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, NAME AS CONTACT_NAME, STREET_ADDRESS AS CONTACT_ADDRESS,
CITY AS CONTACT_CITY, STATE AS CONTACT_STATE, ZIP AS CONTACT_ZIP, COUNTRY AS CONTACT_COUNTRY, EXWORKS, SEQUENCE
INTO A3_AG
FROM A2_AG
ORDER BY DOC_NO, SEQUENCE ASC
-->>GRAB ITEMS TABLE DATA FOR ITEM DESCRIPTIONS
SELECT *
INTO A4_AG
FROM A3_AG A LEFT OUTER JOIN ITEMS B
ON A.ITEM_CODE = B.ITEMNO
-->>GRAB THE COLUMNS WE WANT (ADD DESCRIPTIONS TO LIST)
SELECT DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, AG_NOTES, ITEM_CODE, NOTE, DESCRIPT, QTY_ORDERED, QTY_SHIPPED, DELIV_METH,
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY,
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
INTO A5_AG
FROM A4_AG
-->>ADD THE DESCRIPTIONS TO THE PART NUMBERS (WE ARE ONLY KEEPING THE NOTES COLUMN - DROPPING DESCRIPT)
UPDATE A5_AG
SET NOTE = DESCRIPT
WHERE ITEM_CODE IS NOT NULL AND NOTE IS NULL
-->>GRAB THE COLUMNS WE WANT (DROPPING DESCRIPT) AND ORDERING THE ORDERS
SELECT DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, AG_NOTES, ITEM_CODE, NOTE, QTY_ORDERED, QTY_SHIPPED, DELIV_METH,
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY,
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
INTO A6_AG
FROM A5_AG
ORDER BY DOC_NO, SEQUENCE ASC
-->>CLEAR EMPTY SO LINES
DELETE FROM A6_AG
WHERE ITEM_CODE IS NULL AND NOTE IS NULL OR ITEM_CODE LIKE '' AND NOTE LIKE ''
-->>clear the tables no longer needed
DROP TABLE A1_AG, A2_AG, A3_AG, A4_AG, A5_AG
Thanks for that code - it certainly helps me understand what's happening 'under the hood' as it were.
The conundrum here is that you have 'flattened' the data into a single table, but you are requesting a 'nested' (hierarchy) as an output; and these are at opposite ends of the spectrum.
I :think: the following might help generate some sample data. We don't need much in any table but all tables should have some and ideally represent something that would allow a result like the one in your question - this might be achieved by reducing the number of days allowed in the where clause, you will see that indicated below. The insert into table names may be changed but they should reflect the base table names. Hope this helps.
& note you know this data better than I do, if I missed anything please include it.
The conundrum here is that you have 'flattened' the data into a single table, but you are requesting a 'nested' (hierarchy) as an output; and these are at opposite ends of the spectrum.
I :think: the following might help generate some sample data. We don't need much in any table but all tables should have some and ideally represent something that would allow a result like the one in your question - this might be achieved by reducing the number of days allowed in the where clause, you will see that indicated below. The insert into table names may be changed but they should reflect the base table names. Hope this helps.
& note you know this data better than I do, if I missed anything please include it.
SELECT
A.ORDER_NO
, A.STATUS
, A.SHIP_CODE
, A.DEP
, A.CONT_CODE
, A.CUSTCHAR9 --AS EXWORKS
, A.CUSTMEMO4 --AS AG_NOTES
, A.DATE_FLD --AS SCHEDULED_DATE
, A.DELIV_METH
, A.DOC_ALIAS
, A.DOC_NO
, A.ORDER_DATE
, A.PO --AS CUST_PO
, A.SHIPPED
INTO XM_INVOICES
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
--INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
AND A.DOC_ALIAS = 'AG SALES ORDER'
AND A.DEP = 'AG'
SELECT
B.ORDER_NO
, B.STATUS
, B.ITEM_CODE
, B.NOTE
, B.ITEM_QTY
, B.QTY_SHIP --AS QTY_SHIPPED
INTO XM_X_INVOIC
FROM INVOICES A
INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
--INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
AND A.DOC_ALIAS = 'AG SALES ORDER'
AND A.DEP = 'AG'
-- NB the following is a union all
SELECT
C.ADDR_CODE
, C.NAME --AS SHIP_NAME
, C.STREET_ADDRESS --AS SHIP_ADDRESS
, C.CITY --AS SHIP_CITY
, C.STATE --AS SHIP_STATE
, C.ZIP --AS SHIP_ZIP
, C.COUNTRY --AS SHIP_COUNTRY
INTO XM_ADDRESS
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
AND A.DOC_ALIAS = 'AG SALES ORDER'
AND A.DEP = 'AG'
UNION ALL
SELECT
C.ADDR_CODE
, C.NAME --AS CONTACT_NAME
, C.STREET_ADDRESS --AS CONTACT_ADDRESS
, C.CITY --AS CONTACT_CITY
, C.STATE --AS CONTACT_STATE
, C.ZIP --AS CONTACT_ZIP
, C.COUNTRY --AS CONTACT_COUNTRY
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
INNER JOIN ADDRESS C ON A.CONT_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
AND A.DOC_ALIAS = 'AG SALES ORDER'
AND A.DEP = 'AG'
-->>GRAB ITEMS TABLE DATA FOR ITEM DESCRIPTIONS
SELECT
B.*
INTO XM_ITEMS
FROM XM_X_INVOIC A
INNER JOIN ITEMS B
ON A.ITEM_CODE = B.ITEMNOSELECT
C.ADDR_CODE
, 'C.NAME' AS NAME -- use real field names, not aliases
, 'C.STREET_ADDRESS' AS STREET_ADDRESS -- use real field names, not aliases
, C.CITY
, C.STATE
, C.ZIP
, C.COUNTRY
INTO XM_ADDRESS
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
AND A.DOC_ALIAS = 'AG SALES ORDER'
AND A.DEP = 'AG'ASKER
Paul, I've attached a file with sample data. Note, that the address table contains 2 types of addresses that are indicted by status number (contact and ship). I also added the sequence column to x_invoic as it is needed to format the lines appropriately (given that notes are placed above and below items at times). Also note that not all orders will have contact information. Thank for you help.
xm-sample-data.xls
xm-sample-data.xls
ASKER
Note: the sample data has been scrubbed for privacy. The actual data was replaced with fictitious data.
Great, I have (I think) managed to get the sample data working. I had to fiddle with the items.itemno as there didn't seem to be any valid relationships for those. You can inspect that sample data here: http://sqlfiddle.com/#!3/30b26/3
**MS SQL Server 2008 Schema Setup**:
CREATE TABLE INVOICES
([ORDER_NO] int, [STATUS] int, [SHIP_CODE] int, [DEP] varchar(2), [CONT_CODE] varchar(6), [CUSTCHAR9] varchar(22), [CUSTMEMO4] varchar(22), [DATE_FLD] datetime, [DELIV_METH] varchar(9), [DOC_ALIAS] varchar(14), [DOC_NO] int, [ORDER_DATE] datetime, [PO] varchar(8), [SHIPPED] varchar(1))
;
INSERT INTO INVOICES
([ORDER_NO], [STATUS], [SHIP_CODE], [DEP], [CONT_CODE], [CUSTCHAR9], [CUSTMEMO4], [DATE_FLD], [DELIV_METH], [DOC_ALIAS], [DOC_NO], [ORDER_DATE], [PO], [SHIPPED])
VALUES
(121370, 8, 137495, 'AG', '337495', 'EXW / EXWORKS Lakewood', 'WO 121470', '2013-09-30 00:00:00', 'UPSGround', 'AG SALES ORDER', 121370, '2013-09-30 00:00:00', '3454', 'F'),
(121372, 8, 136538, 'AG', '336538', 'EXW / EXWORKS Lakewood', NULL, '2013-09-30 00:00:00', 'UPSGround', 'AG SALES ORDER', 121372, '2013-09-30 00:00:00', '302780-A', 'F'),
(121381, 8, 138383, 'AG', NULL, 'EXW / EXWORKS Lakewood', 'need weights and dims.', '2013-10-01 00:00:00', 'TBD', 'AG SALES ORDER', 121381, '2013-10-01 00:00:00', '6811', 'F'),
(121383, 8, 138207, 'AG', NULL, 'EXW / EXWORKS Lakewood', NULL, '2013-10-01 00:00:00', 'WillCall', 'AG SALES ORDER', 121383, '2013-10-01 00:00:00', '2926', 'F'),
(121386, 8, 137495, 'AG', NULL, 'EXW / EXWORKS Lakewood', 'WO 122386 / Mapcargo', '2013-10-01 00:00:00', 'WillCall', 'AG SALES ORDER', 121386, '2013-10-01 00:00:00', '3465', 'F')
;
CREATE TABLE X_INVOIC
([ORDER_NO] int, [STATUS] int, [ITEM_CODE] varchar(15), [NOTE] varchar(30), [ITEM_QTY] varchar(4), [QTY_SHIP] varchar(4), [SEQUENCE] int)
;
INSERT INTO X_INVOIC
([ORDER_NO], [STATUS], [ITEM_CODE], [NOTE], [ITEM_QTY], [QTY_SHIP], [SEQUENCE])
VALUES
(121370, 8, NULL, '6 MONTHS WARRANTY', '0', '0', 1),
(121370, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
(121370, 8, 'WD7500BPKT-FR', NULL, '38', '0', 2),
(121372, 8, NULL, '90 DAY WARRANTY', '0', '0', 1),
(121372, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
(121372, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 5),
(121372, 8, 'ST31000340NS-FR', NULL, '5', '0', 4),
(121372, 8, 'WD2500AAJS-FR', NULL, '20', '0', 2),
(121381, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
(121381, 8, NULL, NULL, '0', '0', 4),
(121381, 8, NULL, '6 month warranty', '0', '0', 1),
(121381, 8, 'ST380215A-SFR', NULL, '500', '0', 2),
(121383, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
(121383, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 5),
(121383, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 7),
(121383, 8, NULL, '6 month warranty', '0', '0', 1),
(121383, 8, 'ST31000340NS-FR', NULL, '260', '0', 4),
(121383, 8, 'W0A57911', NULL, '200', '0', 2),
(121383, 8, 'WDBAAF0020HBK', NULL, '85', '0', 6),
(121386, 8, NULL, '6 MONTHS WARRANTY', '0', '0', 1),
(121386, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 4),
(121386, 8, NULL, NULL, '0', '0', 2),
(121386, 8, 'W0J11563', NULL, '222', '0', 3)
;
CREATE TABLE ITEMS
([ACTIVE] varchar(1), [AUTOSERIAL] varchar(1), [ITEMNO] varchar(15), [CATEGORY] varchar(8), [DESCRIPT] varchar(36), [Q_ON_ORDER] int, [Q_ON_RESER] int, [QTY_STK] int)
;
INSERT INTO ITEMS
([ACTIVE], [AUTOSERIAL], [ITEMNO], [CATEGORY], [DESCRIPT], [Q_ON_ORDER], [Q_ON_RESER], [QTY_STK])
VALUES
('T', 'T', 'ST31000340NS-FR', 'SATA2.5', '50GB 5400RPM 2.5" 8MB SATA 9.5MM', 0, 1222, 38),
('T', 'T', 'ST380215A-SFR', 'SATA2.5', '180GB 5400RPM 2.5" 8MB SATA 9.5MM', 0, 451, 784),
('T', 'T', 'W0A57911', 'EXTERNAL', '2TB 3.5" USB2.0 EXTERNAL COLOR:BLACK', 70, 85, 95),
('T', 'T', 'W0J11563', 'SATA', '750GB 7200RPM 2.5" 16MB SATA 3GBs', 500, 38, 7357),
('T', 'T', 'WD2500AAJS-FR', 'SATA', '250GB 7200RPM 3.5" 8MB SATA 3GBs', 0, 20, 4373),
('T', 'T', 'WD7500BPKT-FR', 'ATA3.5', '80GB 7200RPM 3.5" 2MB ATA100', 0, 500, 1608),
('T', 'T', 'WDBAAF0020HBK', 'SATA', '1TB 7200RPM 3.5" 32MB SATA 3GBs', 0, 3535, 4373)
;
CREATE TABLE ADDRESS
([ADDR_CODE] int, [NAME] varchar(28), [STREET_ADDRESS] varchar(21), [CITY] varchar(16), [STATE] varchar(2), [ZIP] int, [COUNTRY] varchar(13), [STATUS] int)
;
INSERT INTO ADDRESS
([ADDR_CODE], [NAME], [STREET_ADDRESS], [CITY], [STATE], [ZIP], [COUNTRY], [STATUS])
VALUES
(137495, 'Graphic Automation Ltd', '556 Hills Summit', 'WESTLAKE VILLAGE', 'CA', 91362, 'UNITED STATES', 5),
(136538, 'Profit Software Tech', '9512 Small Crest', 'MILTON', 'NH', 03851, 'UNITED STATES', 5),
(138383, 'Global Computation Resources', '1954 Noble Hollow', 'BELL Gardens', 'CA', 90201, 'UNITED STATES', 5),
(138207, 'Internal Opportunity Studio', '1654 Big Prairie Cove', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 5),
(137495, 'North American Memory', '785 Golden Dell', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 5),
(337495, 'Graphic Automation Ltd', '556 Hills Summit', 'WESTLAKE VILLAGE', 'CA', 91362, 'UNITED STATES', 6),
(336538, 'Profit Software Tech', '9512 Small Crest', 'MILTON', 'NH', 03851, 'UNITED STATES', 6),
(338383, 'Global Computation Resources', '1954 Noble Hollow', 'BELL Gardens', 'CA', 90201, 'UNITED STATES', 6),
(338207, 'Internal Opportunity Studio', '1654 Big Prairie Cove', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 6),
(337495, 'North American Memory', '785 Golden Dell', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 6)
;**Query 1**:
/*
Order 1
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date
item 1
item 2
item 3
exworks
op_notes
*/
SELECT
I.ORDER_NO
, I.STATUS
, I.SHIP_CODE
, I.DEP
, I.CONT_CODE
, I.CUSTCHAR9 AS EXWORKS
, I.CUSTMEMO4 AS AG_NOTES
, convert(varchar(10),I.DATE_FLD,121) AS SCHEDULED_DATE
, I.DELIV_METH
, I.DOC_ALIAS
, I.DOC_NO
, convert(varchar(10),I.ORDER_DATE,121) AS ORDER_DATE
, I.PO AS CUST_PO
, I.SHIPPED
, X.ORDER_NO as x_ord_no --join
, X.STATUS
, X.ITEM_CODE
, X.NOTE
, X.ITEM_QTY
, X.QTY_SHIP AS QTY_SHIPPED
, S.ADDR_CODE as s_addr_code --join
, S.NAME AS SHIP_NAME
, S.STREET_ADDRESS AS SHIP_ADDRESS
, S.CITY AS SHIP_CITY
, S.STATE AS SHIP_STATE
, S.ZIP AS SHIP_ZIP
, S.COUNTRY AS SHIP_COUNTRY
, C.ADDR_CODE as c_addr_code --join
, C.NAME AS CONTACT_NAME
, C.STREET_ADDRESS AS CONTACT_ADDRESS
, C.CITY AS CONTACT_CITY
, C.STATE AS CONTACT_STATE
, C.ZIP AS CONTACT_ZIP
, C.COUNTRY AS CONTACT_COUNTRY
, M.ACTIVE
, M.AUTOSERIAL
, M.ITEMNO
, M.CATEGORY
, M.DESCRIPT
, M.Q_ON_ORDER
, M.Q_ON_RESER
, M.QTY_STK
FROM INVOICES I
INNER JOIN X_INVOIC X ON I.ORDER_NO = X.ORDER_NO AND I.STATUS = X.STATUS
LEFT JOIN ADDRESS S ON I.SHIP_CODE = S.ADDR_CODE
LEFT JOIN ADDRESS C ON I.CONT_CODE = C.ADDR_CODE
LEFT JOIN ITEMS M ON X.ITEM_CODE = M.ITEMNO
WHERE I.STATUS = 8
--AND I.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
AND I.DOC_ALIAS = 'AG SALES ORDER'
AND I.DEP = 'AG'
**[Results][2]**:
| ORDER_NO | STATUS | SHIP_CODE | DEP | CONT_CODE | EXWORKS | AG_NOTES | SCHEDULED_DATE | DELIV_METH | DOC_ALIAS | DOC_NO | ORDER_DATE | CUST_PO | SHIPPED | X_ORD_NO | ITEM_CODE | NOTE | ITEM_QTY | QTY_SHIPPED | S_ADDR_CODE | SHIP_NAME | SHIP_ADDRESS | SHIP_CITY | SHIP_STATE | SHIP_ZIP | SHIP_COUNTRY | C_ADDR_CODE | CONTACT_NAME | CONTACT_ADDRESS | CONTACT_CITY | CONTACT_STATE | CONTACT_ZIP | CONTACT_COUNTRY | ACTIVE | AUTOSERIAL | ITEMNO | CATEGORY | DESCRIPT | Q_ON_ORDER | Q_ON_RESER | QTY_STK |
|----------|--------|-----------|-----|-----------|------------------------|------------------------|----------------|------------|----------------|--------|------------|----------|---------|----------|-----------------|--------------------------------|----------|-------------|-------------|------------------------------|-----------------------|------------------|------------|----------|---------------|-------------|------------------------|------------------|------------------|---------------|-------------|-----------------|--------|------------|-----------------|----------|--------------------------------------|------------|------------|---------|
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | 6 MONTHS WARRANTY | 0 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | 337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | 6 MONTHS WARRANTY | 0 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | 337495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | 6 MONTHS WARRANTY | 0 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | 337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | 6 MONTHS WARRANTY | 0 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | 337495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | 337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | 337495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | 337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | 337495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | WD7500BPKT-FR | (null) | 38 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | 337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | T | T | WD7500BPKT-FR | ATA3.5 | 80GB 7200RPM 3.5" 2MB ATA100 | 0 | 500 | 1608 |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | WD7500BPKT-FR | (null) | 38 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | 337495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | T | T | WD7500BPKT-FR | ATA3.5 | 80GB 7200RPM 3.5" 2MB ATA100 | 0 | 500 | 1608 |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | WD7500BPKT-FR | (null) | 38 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | 337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | T | T | WD7500BPKT-FR | ATA3.5 | 80GB 7200RPM 3.5" 2MB ATA100 | 0 | 500 | 1608 |
| 121370 | 8 | 137495 | AG | 337495 | EXW / EXWORKS Lakewood | WO 121470 | 2013-09-30 | UPSGround | AG SALES ORDER | 121370 | 2013-09-30 | 3454 | F | 121370 | WD7500BPKT-FR | (null) | 38 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | 337495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | T | T | WD7500BPKT-FR | ATA3.5 | 80GB 7200RPM 3.5" 2MB ATA100 | 0 | 500 | 1608 |
| 121372 | 8 | 136538 | AG | 336538 | EXW / EXWORKS Lakewood | (null) | 2013-09-30 | UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A | F | 121372 | (null) | 90 DAY WARRANTY | 0 | 0 | 136538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | 336538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121372 | 8 | 136538 | AG | 336538 | EXW / EXWORKS Lakewood | (null) | 2013-09-30 | UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A | F | 121372 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 136538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | 336538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121372 | 8 | 136538 | AG | 336538 | EXW / EXWORKS Lakewood | (null) | 2013-09-30 | UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A | F | 121372 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 136538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | 336538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121372 | 8 | 136538 | AG | 336538 | EXW / EXWORKS Lakewood | (null) | 2013-09-30 | UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A | F | 121372 | ST31000340NS-FR | (null) | 5 | 0 | 136538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | 336538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | T | T | ST31000340NS-FR | SATA2.5 | 50GB 5400RPM 2.5" 8MB SATA 9.5MM | 0 | 1222 | 38 |
| 121372 | 8 | 136538 | AG | 336538 | EXW / EXWORKS Lakewood | (null) | 2013-09-30 | UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A | F | 121372 | WD2500AAJS-FR | (null) | 20 | 0 | 136538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | 336538 | Profit Software Tech | 9512 Small Crest | MILTON | NH | 3851 | UNITED STATES | T | T | WD2500AAJS-FR | SATA | 250GB 7200RPM 3.5" 8MB SATA 3GBs | 0 | 20 | 4373 |
| 121381 | 8 | 138383 | AG | (null) | EXW / EXWORKS Lakewood | need weights and dims. | 2013-10-01 | TBD | AG SALES ORDER | 121381 | 2013-10-01 | 6811 | F | 121381 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 138383 | Global Computation Resources | 1954 Noble Hollow | BELL Gardens | CA | 90201 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121381 | 8 | 138383 | AG | (null) | EXW / EXWORKS Lakewood | need weights and dims. | 2013-10-01 | TBD | AG SALES ORDER | 121381 | 2013-10-01 | 6811 | F | 121381 | (null) | (null) | 0 | 0 | 138383 | Global Computation Resources | 1954 Noble Hollow | BELL Gardens | CA | 90201 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121381 | 8 | 138383 | AG | (null) | EXW / EXWORKS Lakewood | need weights and dims. | 2013-10-01 | TBD | AG SALES ORDER | 121381 | 2013-10-01 | 6811 | F | 121381 | (null) | 6 month warranty | 0 | 0 | 138383 | Global Computation Resources | 1954 Noble Hollow | BELL Gardens | CA | 90201 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121381 | 8 | 138383 | AG | (null) | EXW / EXWORKS Lakewood | need weights and dims. | 2013-10-01 | TBD | AG SALES ORDER | 121381 | 2013-10-01 | 6811 | F | 121381 | ST380215A-SFR | (null) | 500 | 0 | 138383 | Global Computation Resources | 1954 Noble Hollow | BELL Gardens | CA | 90201 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | T | T | ST380215A-SFR | SATA2.5 | 180GB 5400RPM 2.5" 8MB SATA 9.5MM | 0 | 451 | 784 |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | (null) | 6 month warranty | 0 | 0 | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | ST31000340NS-FR | (null) | 260 | 0 | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | T | T | ST31000340NS-FR | SATA2.5 | 50GB 5400RPM 2.5" 8MB SATA 9.5MM | 0 | 1222 | 38 |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | W0A57911 | (null) | 200 | 0 | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | T | T | W0A57911 | EXTERNAL | 2TB 3.5" USB2.0 EXTERNAL COLOR:BLACK | 70 | 85 | 95 |
| 121383 | 8 | 138207 | AG | (null) | EXW / EXWORKS Lakewood | (null) | 2013-10-01 | WillCall | AG SALES ORDER | 121383 | 2013-10-01 | 2926 | F | 121383 | WDBAAF0020HBK | (null) | 85 | 0 | 138207 | Internal Opportunity Studio | 1654 Big Prairie Cove | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | T | T | WDBAAF0020HBK | SATA | 1TB 7200RPM 3.5" 32MB SATA 3GBs | 0 | 3535 | 4373 |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | (null) | 6 MONTHS WARRANTY | 0 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | (null) | 6 MONTHS WARRANTY | 0 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | (null) | CONDITION: FACTORY REFURBISHED | (null) | (null) | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | (null) | (null) | 0 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | (null) | (null) | 0 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | W0J11563 | (null) | 222 | 0 | 137495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE | CA | 91362 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | T | T | W0J11563 | SATA | 750GB 7200RPM 2.5" 16MB SATA 3GBs | 500 | 38 | 7357 |
| 121386 | 8 | 137495 | AG | (null) | EXW / EXWORKS Lakewood | WO 122386 / Mapcargo | 2013-10-01 | WillCall | AG SALES ORDER | 121386 | 2013-10-01 | 3465 | F | 121386 | W0J11563 | (null) | 222 | 0 | 137495 | North American Memory | 785 Golden Dell | AGOURA HILLS | CA | 91301 | UNITED STATES | (null) | (null) | (null) | (null) | (null) | (null) | (null) | T | T | W0J11563 | SATA | 750GB 7200RPM 2.5" 16MB SATA 3GBs | 500 | 38 | 7357 |
[1]: http://sqlfiddle.com/#!3/30b26/3
[2]: http://sqlfiddle.com/#!3/30b26/3/0ASKER
Thanks Paul for the Query1.. that is so much better than using all those temporary tables. I ran it on the live data and it worked great. I apologize if I messed something up with the sample data as I was trying to make changes to keep the data private.
Does it look like it might be possible to nest the data in an xml output based on the sample data?
Again, many thanks for your help and that sample query!
Does it look like it might be possible to nest the data in an xml output based on the sample data?
Again, many thanks for your help and that sample query!
ASKER
Paul, I'll check out the sql fiddle in the morning.. it's late for me now.
I have an admission to make, forming xml from sql isn't a strength of mine. but yes, it can certainly be done.
My principal concern was that you provided a sample schema and data that was not 'flat' because that is best for the xml you need.
I'm hoping other experts can chip in now with that xml piece
- but i'll plug away on the xml if no-one else can whip it up.
My principal concern was that you provided a sample schema and data that was not 'flat' because that is best for the xml you need.
I'm hoping other experts can chip in now with that xml piece
- but i'll plug away on the xml if no-one else can whip it up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, I should add, I had to introduce "SELECT TOP 1" twice in the query above to limit ship-to and contact information to one each - may be an issue with the data but there seem to be more than one for an order. This may be important to note.
ASKER
Thanks for the help and effort Paul. I've attempted to run and tweak the code above. I am stuck currently on there being no nested child level for order details. After </shipped> I get <SHIP_CODE>134595<
I think if once I get the order details nested and figure out how to export the compiled data to an xml file I'll be golden.
Thanks again for all your help.
I think if once I get the order details nested and figure out how to export the compiled data to an xml file I'll be golden.
Thanks again for all your help.
Hi, I understand the frustration, I tried tweaking at one point ans also got those > < entities...
I'm going to throw this one at you, it looks like it might actually be helpful but I haven't tried it (yet anyway). I did start out using the 'explicit' method of tsql's xml - but it's just way too complex. This "trick" appears to be lots easier.
http://www.modhul.com/2008/01/23/an-easier-way-to-do-complex-for-xml-explicit/
I'm going to throw this one at you, it looks like it might actually be helpful but I haven't tried it (yet anyway). I did start out using the 'explicit' method of tsql's xml - but it's just way too complex. This "trick" appears to be lots easier.
http://www.modhul.com/2008/01/23/an-easier-way-to-do-complex-for-xml-explicit/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Saurv
this still remains this: "no nested child level for order details"
and I'm not strong in the xml/sql area
any tips/clues/solution?
&
FOR XML PATH ('ORDER') , Root('All')
darn it, I went crazy trying to get a root node - BIG thank you!
(naturally it just had to be that obvious grrr)
this still remains this: "no nested child level for order details"
and I'm not strong in the xml/sql area
any tips/clues/solution?
&
FOR XML PATH ('ORDER') , Root('All')
darn it, I went crazy trying to get a root node - BIG thank you!
(naturally it just had to be that obvious grrr)
OK, now that I know how to insert a root node (I used 'ORDERS' not 'ALL'), and changing 'DETAILS' to 'ORDER_DETAILS', below is the current output,
It should be noted that there is a mixture of one-to-one and one-to-many in your original format:
Order 1
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date -- << one-to-one
item 1 -- << one-to-many
item 2 -- << one-to-many
item 3 -- << one-to-many
exworks -- << one-to-one
op_notes -- not sure
Hence I'm not sure what the final output should really look like.
Could you edit the current results to demonstrate the specific changes that are needed please?
It should be noted that there is a mixture of one-to-one and one-to-many in your original format:
Order 1
Ship info
ship name, address etc
Contact info
contact name, address etc
Order details
order date -- << one-to-one
item 1 -- << one-to-many
item 2 -- << one-to-many
item 3 -- << one-to-many
exworks -- << one-to-one
op_notes -- not sure
Hence I'm not sure what the final output should really look like.
Could you edit the current results to demonstrate the specific changes that are needed please?
<ORDERS>
<ORDER>
<ORDER_NO>121370</ORDER_NO>
<STATUS>8</STATUS>
<DEP>AG</DEP>
<EXWORKS>EXW / EXWORKS Lakewood</EXWORKS>
<AG_NOTES>WO 121470</AG_NOTES>
<SCHEDULED_DATE>2013-09-30T00:00:00</SCHEDULED_DATE>
<DELIV_METH>UPSGround</DELIV_METH>
<DOC_ALIAS>AG SALES ORDER</DOC_ALIAS>
<DOC_NO>121370</DOC_NO>
<ORDER_DATE>2013-09-30T00:00:00</ORDER_DATE>
<CUST_PO>3454</CUST_PO>
<SHIPPED>F</SHIPPED>
<SHIP_CODE>137495</SHIP_CODE>
<SHIP_NAME>Graphic Automation Ltd</SHIP_NAME>
<SHIP_ADDRESS>556 Hills Summit</SHIP_ADDRESS>
<SHIP_CITY>WESTLAKE VILLAGE</SHIP_CITY>
<SHIP_STATE>CA</SHIP_STATE>
<SHIP_ZIP>91362</SHIP_ZIP>
<SHIP_COUNTRY>UNITED STATES</SHIP_COUNTRY>
<CONTACT_CODE>337495</CONTACT_CODE>
<CONTACT_NAME>Graphic Automation Ltd</CONTACT_NAME>
<CONTACT_ADDRESS>556 Hills Summit</CONTACT_ADDRESS>
<CONTACT_CITY>WESTLAKE VILLAGE</CONTACT_CITY>
<CONTACT_STATE>CA</CONTACT_STATE>
<CONTACT_ZIP>91362</CONTACT_ZIP>
<CONTACT_COUNTRY>UNITED STATES</CONTACT_COUNTRY>
<ORDER_DETAILS>
<ITEM_CODE>WD7500BPKT-FR</ITEM_CODE>
<STATUS>8</STATUS>
<ITEM_QTY>38</ITEM_QTY>
<QTY_SHIPPED>0</QTY_SHIPPED>
<ACTIVE>T</ACTIVE>
<AUTOSERIAL>T</AUTOSERIAL>
<ITEMNO>WD7500BPKT-FR</ITEMNO>
<CATEGORY>ATA3.5</CATEGORY>
<DESCRIPT>80GB 7200RPM 3.5" 2MB ATA100</DESCRIPT>
<Q_ON_ORDER>0</Q_ON_ORDER>
<Q_ON_RESER>500</Q_ON_RESER>
<QTY_STK>1608</QTY_STK>
</ORDER_DETAILS>
</ORDER>
<ORDER>
<ORDER_NO>121372</ORDER_NO>
<STATUS>8</STATUS>
<DEP>AG</DEP>
<EXWORKS>EXW / EXWORKS Lakewood</EXWORKS>
<SCHEDULED_DATE>2013-09-30T00:00:00</SCHEDULED_DATE>
<DELIV_METH>UPSGround</DELIV_METH>
<DOC_ALIAS>AG SALES ORDER</DOC_ALIAS>
<DOC_NO>121372</DOC_NO>
<ORDER_DATE>2013-09-30T00:00:00</ORDER_DATE>
<CUST_PO>302780-A</CUST_PO>
<SHIPPED>F</SHIPPED>
<SHIP_CODE>136538</SHIP_CODE>
<SHIP_NAME>Profit Software Tech</SHIP_NAME>
<SHIP_ADDRESS>9512 Small Crest</SHIP_ADDRESS>
<SHIP_CITY>MILTON</SHIP_CITY>
<SHIP_STATE>NH</SHIP_STATE>
<SHIP_ZIP>3851</SHIP_ZIP>
<SHIP_COUNTRY>UNITED STATES</SHIP_COUNTRY>
<CONTACT_CODE>336538</CONTACT_CODE>
<CONTACT_NAME>Profit Software Tech</CONTACT_NAME>
<CONTACT_ADDRESS>9512 Small Crest</CONTACT_ADDRESS>
<CONTACT_CITY>MILTON</CONTACT_CITY>
<CONTACT_STATE>NH</CONTACT_STATE>
<CONTACT_ZIP>3851</CONTACT_ZIP>
<CONTACT_COUNTRY>UNITED STATES</CONTACT_COUNTRY>
<ORDER_DETAILS>
<ITEM_CODE>ST31000340NS-FR</ITEM_CODE>
<STATUS>8</STATUS>
<ITEM_QTY>5</ITEM_QTY>
<QTY_SHIPPED>0</QTY_SHIPPED>
<ACTIVE>T</ACTIVE>
<AUTOSERIAL>T</AUTOSERIAL>
<ITEMNO>ST31000340NS-FR</ITEMNO>
<CATEGORY>SATA2.5</CATEGORY>
<DESCRIPT>50GB 5400RPM 2.5" 8MB SATA 9.5MM</DESCRIPT>
<Q_ON_ORDER>0</Q_ON_ORDER>
<Q_ON_RESER>1222</Q_ON_RESER>
<QTY_STK>38</QTY_STK>
</ORDER_DETAILS>
<ORDER_DETAILS>
<ITEM_CODE>WD2500AAJS-FR</ITEM_CODE>
<STATUS>8</STATUS>
<ITEM_QTY>20</ITEM_QTY>
<QTY_SHIPPED>0</QTY_SHIPPED>
<ACTIVE>T</ACTIVE>
<AUTOSERIAL>T</AUTOSERIAL>
<ITEMNO>WD2500AAJS-FR</ITEMNO>
<CATEGORY>SATA</CATEGORY>
<DESCRIPT>250GB 7200RPM 3.5" 8MB SATA 3GBs</DESCRIPT>
<Q_ON_ORDER>0</Q_ON_ORDER>
<Q_ON_RESER>20</Q_ON_RESER>
<QTY_STK>4373</QTY_STK>
</ORDER_DETAILS>
</ORDER>
</ORDERS>ASKER
Thank you Paul and Saurv! I've made some great progress today with your help.
Here's where I'm at.. I've made some changes and additions and have the xml output looking great. The last piece I need to figure out now is how to export this to a file.
Here's the query at present in case you were curious:
Here's where I'm at.. I've made some changes and additions and have the xml output looking great. The last piece I need to figure out now is how to export this to a file.
Here's the query at present in case you were curious:
SELECT
I.ORDER_NO
, I.ORDER_DATE
, I.DATE_FLD AS DELIVERY_DATE
, I.DEP
, I.CUSTCHAR9 AS INCOTERM
, I.CUSTMEMO4 AS AGIL_ORDER_NOTES
, I.DELIV_METH
, I.PO AS BUYER_PO
, I.INV_AMOUNT AS ORDERVALUE
, (
SELECT
S.ADDR_CODE AS SHIP_CODE
, S.NAME AS SHIP_NAME
, S.STREET_ADDRESS AS SHIP_ADDRESS
, S.CITY AS SHIP_CITY
, S.STATE AS SHIP_STATE
, S.ZIP AS SHIP_ZIP
, S.COUNTRY AS SHIP_COUNTRY
, s.EMAIL AS SHIP_EMAIL
, s.TEL1 AS SHIP_TEL
FROM ADDRESS S
WHERE I.SHIP_CODE = S.ADDR_CODE
FOR XML PATH ('') , TYPE
)
, (
SELECT
B.ADDR_CODE AS BILL_CODE
, B.NAME AS BILL_NAME
, B.STREET_ADDRESS AS BILL_ADDRESS
, B.CITY AS BILL_CITY
, B.STATE AS BILL_STATE
, B.ZIP AS BILL_ZIP
, B.COUNTRY AS BILL_COUNTRY
, b.EMAIL AS BILL_EMAIL
FROM ADDRESS B
WHERE I.BILL_CODE = B.ADDR_CODE and b.ACTIVE = 't'
FOR XML PATH ('') , TYPE
)
, (
SELECT
C.ADDR_CODE AS CONTACT_CODE
, C.NAME AS CONTACT_NAME
, C.STREET_ADDRESS AS CONTACT_ADDRESS
, C.CITY AS CONTACT_CITY
, C.STATE AS CONTACT_STATE
, C.ZIP AS CONTACT_ZIP
, C.COUNTRY AS CONTACT_COUNTRY
, C.TEL1 AS CONTACT_TEL
, c.EMAIL AS CONTACT_EMAIL
FROM ADDRESS C
WHERE I.CONT_CODE = C.ADDR_CODE
FOR XML PATH ('') , TYPE
)
, (
SELECT
X.ORDER_NO
, X.ITEM_CODE
, X.NOTE
, X.ITEM_QTY
, X.QTY_SHIP AS QTY_SHIPPED
, M.MODEL AS MANUFACTURERSKU
, M.CATEGORY
, M.DESCRIPT
, x.ITEM_PRICE AS UNITPRICE
, X.SEQUENCE
FROM X_INVOIC X
LEFT JOIN ITEMS M ON X.ITEM_CODE = M.ITEMNO
WHERE I.ORDER_NO = X.ORDER_NO AND I.STATUS = X.STATUS
ORDER BY SEQUENCE ASC
FOR XML PATH ('SHIPMENTORDERDETAIL') , TYPE
)
FROM INVOICES I
WHERE I.STATUS = 8
AND I.DATE_FLD >= DATEADD(DAY, -30, GETDATE())
AND I.DOC_ALIAS = 'ag SALES ORDER'
AND I.DEP = 'ag'
AND I.ORDER_NO IN (SELECT ORDER_NO FROM AG_ORDERS) --<<view I setup to indicate orders need to be pulled
FOR XML PATH ('SHIPMENTORDER') , Root('All')
I would suggest:
"All" was just something I used -manually- to help me format the output (here)
, Root('ORDERS')
or
, Root('SHIPMENTORDERS')
"All" was just something I used -manually- to help me format the output (here)
ASKER
Thanks again Paul and Saurv! The last piece I need to work out is how to take this and export it to a file. Any ideas on this?
writing the xml to file.... mmmm
not as simple as "click this" I'm afraid, and there are options.
to answer may require knowing more about what you use and are able to to do, but I'd suggest starting here:
If you can use ado.net then saving a query result to file uses: File.WriteAllText()
and there's probably other ways e.g. using C# and so on.
so - I've attempted an answer - but it may not be the best one and I'd prefer you sought a more experienced file writer's answer.
by the way, is this effort required for export? or a web service?
if asking a new question please provide the end objective - it may help determine the best path to follow.
not as simple as "click this" I'm afraid, and there are options.
to answer may require knowing more about what you use and are able to to do, but I'd suggest starting here:
Reading and Writing Files in SQL Server using T-SQL
which proposes a stored procedure "spWriteStringToFile"
code for that here
this requires OLE to be enabled which is documented here:
http://msdn.microsoft.com/en-us/library/ms191188(v=sql.105).aspx
to use it will be something like the following:
DECLARE @xmloutxml xml
DECLARE @xmloutstr nvarchar(max)
SET @xmloutxml = (
/* the xml query here */
)
SET @xmloutstr = cast(@xmloutxml as nvarchar(max))
EXEC spWriteStringToFile @xmloutstr,'d:/data/','my_
disclaimer: you probably need someone with more experience in doing this as I don't (it's a long story not relevant here); and you might want to open a new question about this topic "how to write xml to file using tsql?"which proposes a stored procedure "spWriteStringToFile"
code for that here
this requires OLE to be enabled which is documented here:
http://msdn.microsoft.com/en-us/library/ms191188(v=sql.105).aspx
to use it will be something like the following:
DECLARE @xmloutxml xml
DECLARE @xmloutstr nvarchar(max)
SET @xmloutxml = (
/* the xml query here */
)
SET @xmloutstr = cast(@xmloutxml as nvarchar(max))
EXEC spWriteStringToFile @xmloutstr,'d:/data/','my_
If you can use ado.net then saving a query result to file uses: File.WriteAllText()
and there's probably other ways e.g. using C# and so on.
so - I've attempted an answer - but it may not be the best one and I'd prefer you sought a more experienced file writer's answer.
by the way, is this effort required for export? or a web service?
if asking a new question please provide the end objective - it may help determine the best path to follow.
ASKER
Thank you for the help Paul, I guess I might have mislead with the question topic as I was thinking of xml as a file output. I'll research this more and possibly open another question. Thanks again for all your effort and time.
It will help us to resolve the query.
Thanks,
Saurabh