Creating a view

I need the following information in a view that I am using for my Document Management solution.

Customer Order #
Packlist #
Part ID
Customer Name
Cust PO #
Order Date
Serial Numbers shipped on this packlist

Any help is appreciated

Find more posts tagged with

Comments

Legacy Contributor

Most company's are charging 250 - 1000 dollars for custom views - especially when tapping multiple tables as the one above is.

[Updated on 10/27/2010 1:18 PM]

Edited to correct an error !

Legacy Contributor

what ever happened to admins helping admins? he’s obviously a newbie when it comes to programing in sql. This is what I hate about infor support they leave you in the dark when it comes to stuff like this and your on your own to figure it out. So bocman here you go... I'm not sure what you meant by serial numbers shipped on this packlist so it has everything you need except that which should get you started.

SELECT dbo.CUST_ORDER_LINE.CUST_ORDER_ID, dbo.CUSTOMER.NAME, dbo.CUST_ORDER_LINE.PART_ID, dbo.CUST_ORDER_LINE.CUSTOMER_PART_ID,
dbo.CUSTOMER_ORDER.CUSTOMER_PO_REF, dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.SHIPPER.PACKLIST_ID
FROM dbo.CUST_ORDER_LINE INNER JOIN
dbo.CUSTOMER_ORDER ON dbo.CUST_ORDER_LINE.CUST_ORDER_ID = dbo.CUSTOMER_ORDER.ID INNER JOIN
dbo.CUSTOMER ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUSTOMER.ID INNER JOIN
dbo.SHIPPER ON dbo.CUST_ORDER_LINE.CUST_ORDER_ID = dbo.SHIPPER.CUST_ORDER_ID

Heres my price....... 0$ it took not even 5 mins to create this.

fourms were created for a place to get help not make money.

Legacy Contributor

Well Done Mr. Chronister ! And going forward I'll heed your advice and help when I can !

Serial (trace) numbers are in the trace table - I think you go from the packlist to the inventory trans to the trace trans.

Where does he install the view? I'm a newbie to programming too.

Legacy Contributor

Got it !

SELECT CUSTOMER_ORDER.ID, SHIPPER_LINE.PACKLIST_ID, CUSTOMER.NAME, CUST_ORDER_LINE.PART_ID, CUSTOMER_ORDER.CUSTOMER_PO_REF, CUSTOMER_ORDER.ORDER_DATE, TRACE_INV_TRANS.TRACE_ID
FROM (CUSTOMER INNER JOIN CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID) INNER JOIN (CUST_ORDER_LINE LEFT JOIN (SHIPPER_LINE LEFT JOIN (INVENTORY_TRANS LEFT JOIN TRACE_INV_TRANS ON (INVENTORY_TRANS.PART_ID = TRACE_INV_TRANS.PART_ID) AND (INVENTORY_TRANS.TRANSACTION_ID = TRACE_INV_TRANS.TRANSACTION_ID)) ON (SHIPPER_LINE.CUST_ORDER_LINE_NO = INVENTORY_TRANS.CUST_ORDER_LINE_NO) AND (SHIPPER_LINE.CUST_ORDER_ID = INVENTORY_TRANS.CUST_ORDER_ID)) ON (CUST_ORDER_LINE.LINE_NO = SHIPPER_LINE.CUST_ORDER_LINE_NO) AND (CUST_ORDER_LINE.CUST_ORDER_ID = SHIPPER_LINE.CUST_ORDER_ID)) ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID
ORDER BY CUSTOMER_ORDER.ID;

Legacy Contributor

Hi Guys,

Since you're newbies...just one more piece of advice. You don't need to write out "dbo.CUST_ORDER_LINE." in front of every column reference. You can shorten them down with references, like this:

SELECT COL.CUST_ORDER_ID, COL.LINE_NO, COL.PART_ID, CO.ORDER_DATE FROM dbo.CUST_ORDER_LINE COL INNER JOIN dbo.CUSTOMER_ORDER CO ON COL.CUST_ORDER_ID = CO.ID WHERE CO.ORDER_DATE BETWEEN '2010/01/01' AND '2010/12/31'

(as an example). In your "FROM" section you just put a "nickname" after the table name and you can reference that in your query everywhere else you need to put the full table reference. Makes the queries a little easier to read, I find. But, to each his own!

Legacy Contributor

you create a view by right clicking on the views folder under your DB name in MS SQL Server Management Studio and clicking "New View..." then you hit the save icon...

Legacy Contributor

If you want all serials shipped on a single line returned on one line (rather than linking directly to trace_inventory_trans and returning a new row per serial), this may be useful:

This works in MSSQL 2005. Not sure about 2000

SELECT i.CUST_ORDER_ID, SHIPPER_LINE.PACKLIST_ID, i.PART_ID, CUSTOMER.NAME AS CUST_NAME, CUSTOMER_ORDER.CUSTOMER_PO_REF,
CUSTOMER_ORDER.ORDER_DATE, CONVERT(text, stuff
((SELECT ',' + t .ID
FROM TRACE_INV_TRANS ti INNER JOIN
TRACE AS t ON t .PART_ID = ti.PART_ID AND t .ID = ti.TRACE_ID
WHERE ti.TRANSACTION_ID = i.TRANSACTION_ID
ORDER BY t .NPROPERTY_4 FOR xml path('')), 1, 1, '')) AS serials
FROM CUSTOMER INNER JOIN
CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID INNER JOIN
SHIPPER_LINE INNER JOIN
INVENTORY_TRANS AS i ON SHIPPER_LINE.CUST_ORDER_ID = i.CUST_ORDER_ID AND SHIPPER_LINE.CUST_ORDER_LINE_NO = i.CUST_ORDER_LINE_NO ON
CUSTOMER_ORDER.ID = SHIPPER_LINE.CUST_ORDER_ID

Copyright © 2025 Infor. All rights reserved.