SQL Server View Issue

MSAccessRookie

MSAccessRookie

AWF VIP
Local time
Today, 16:40
Joined
May 2, 2008
Messages
3,423
I have an SQL Server View that identifies records that are required for shipping. We are attempting to automate the UPS WorldShip Application using this View. Currently, the View identifies the correct shipping records, but the information is not correct, because UPS WorldShip Application requests one box per record, and the Shipment data contains multiple boxes per record. It looks like I will need to create multiple records for each shipment record that represents more than one box.

The Column NoOfPackages (see below) varies from as little as 1 to (on occasion) over 50. I expect that I will need to use that Column to replicate the record the number of times required to have a single entry per box. Is there a way to do this?

Please note that the code is still under development, and any suggestions will be welcomed.

Code: 
[/FONT]
[FONT=Courier New]USE [CMPNYDB][/FONT]
[FONT=Courier New]GO[/FONT]
[FONT=Courier New]/****** Object:  View [dbo].[Shipping_CmpnyToUPS]    Script Date: 09/11/2009 17:00:43 ******/[/FONT]
[FONT=Courier New]SET ANSI_NULLS ON[/FONT]
[FONT=Courier New]GO[/FONT]
[FONT=Courier New]SET QUOTED_IDENTIFIER ON[/FONT]
[FONT=Courier New]GO[/FONT]
[FONT=Courier New]ALTER View [dbo].[ Shipping_CmpnyToUPS] As[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]SELECT T0.Institution_ID, T2.Institution, T6.InvoiceNumber As InvoiceNumber, T6.ShippingDetailsID, [COLOR=green][B]T6.Boxes As NoOfPackages[/B][/COLOR], [/FONT]
[FONT=Courier New]Cast(T6.InvoiceNumber As VarChar) + Cast(T6.ShippingDetailsID As VarChar) As Inv, T0.PO_No As PONum, T3.Department, T3.Address, [/FONT]
[FONT=Courier New]T3.City, IsNull(T3.State, T3.State_NotUSA) As State, IsNull(T3.PostalCode, PostalCode_NotUSA) As PostalCode, T0.ShipToAddressID, [/FONT]
[FONT=Courier New]IsNull(T3.Country_NOTUSA,'USA') As Country, T0.ShipToAttnID As ContactID, [/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]IsNull(T4.L_Name, '') + CASE IsNull(T4.L_Name, '') WHEN '' THEN '' ELSE CASE IsNull(T4.F_Name, '') WHEN '' THEN '' ELSE ', ' END END + IsNull(T4.F_Name, '') As ShiptoName, [/FONT]
[FONT=Courier New]IsNull(T5.L_Name, '') + CASE IsNull(T5.L_Name, '') WHEN '' THEN '' ELSE CASE IsNull(T5.F_Name, '') WHEN '' THEN '' ELSE ', ' END END + IsNull(T5.F_Name, '') As ResearcherName, [/FONT]
[FONT=Courier New]IsNull(T4.Email, '') As ShiptoEmail, IsNull(T5.Email, '') As ResearcherEmail,[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]CASE IsNull(T4.Email, '') WHEN '' THEN CASE IsNull(T5.Email, '') WHEN '' THEN '' ELSE T5.Email END ELSE T4.Email END As TheEmail,[/FONT]
[FONT=Courier New]'Y' As QVNotify, 'Y' As ShipNotify, 'Y' As ExceptionNotify, 'Y' As DeliveryNotify, [/FONT]
[FONT=Courier New]11 As BoxLength, 9 As BoxWidth, 12 As BoxHeight[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]FROM tblworkorders T0 [/FONT]
[FONT=Courier New]   INNER JOIN INSTITUTIONS T2 on T0.Institution_ID = T2.Institution_ID [/FONT]
[FONT=Courier New]   LEFT JOIN tblAddresses T3 ON T0.ShiptoAddressID = T3.AddressID [/FONT]
[FONT=Courier New]   LEFT JOIN tblPeople T5 ON T5.Person_id = T0.ResearcherID [/FONT]
[FONT=Courier New]   LEFT JOIN tblPeople T4 ON T4.Person_id = T0.ShiptoAttnID [/FONT]
[FONT=Courier New]   INNER JOIN (SELECT T0.* FROM tblShippingDetails T0 INNER JOIN tblShippingRoute T2 ON T0.ShipRoute = T2.ShippingRouteID[/FONT]
[FONT=Courier New]               WHERE (T2.shippingroute like '%UPS%')) As T6 ON T0.InvoiceNumber = T6.InvoiceNumber[/FONT]
[FONT=Courier New]   WHERE ((T6.SHIPDATE IS NULL) AND (T6.Boxes <> 0))[/FONT]
[FONT=Courier New]
 
I don't have enough focus to read the whole view right now but wouldn't joining with the table that has the 50 records give you the requisite duplicate rows?
 
Beautified the code.

Code: 
SELECT t0.institution_id, 
       t2.institution, 
       t6.invoicenumber                                                          AS invoicenumber, 
       t6.shippingdetailsid, 
       t6.boxes                                                                  AS noofpackages, 
       Cast(t6.invoicenumber AS VARCHAR) + Cast(t6.shippingdetailsid AS VARCHAR) AS inv, 
       t0.po_no                                                                  AS ponum, 
       t3.department, 
       t3.address, 
       t3.city, 
       Isnull(t3.state,t3.state_notusa)                                          AS state, 
       Isnull(t3.postalcode,postalcode_notusa)                                   AS postalcode, 
       t0.shiptoaddressid, 
       Isnull(t3.country_notusa,'USA')                                           AS country, 
       t0.shiptoattnid                                                           AS contactid, 
       Isnull(t4.l_name,'') + CASE Isnull(t4.l_name,'') 
                                WHEN '' 
                                THEN '' 
                                ELSE CASE Isnull(t4.f_name,'') 
                                       WHEN '' 
                                       THEN '' 
                                       ELSE ', ' 
                                     END 
                              END + Isnull(t4.f_name,'') AS shiptoname, 
       Isnull(t5.l_name,'') + CASE Isnull(t5.l_name,'') 
                                WHEN '' 
                                THEN '' 
                                ELSE CASE Isnull(t5.f_name,'') 
                                       WHEN '' 
                                       THEN '' 
                                       ELSE ', ' 
                                     END 
                              END + Isnull(t5.f_name,'') AS researchername, 
       Isnull(t4.email,'')                                                       AS shiptoemail, 
       Isnull(t5.email,'')                                                       AS researcheremail, 
       CASE Isnull(t4.email,'') 
         WHEN '' 
         THEN CASE Isnull(t5.email,'') 
                WHEN '' 
                THEN '' 
                ELSE t5.email 
              END 
         ELSE t4.email 
       END AS theemail, 
       'Y'                                                                       AS qvnotify,
       'Y'                                                                       AS shipnotify, 
       'Y'                                                                       AS exceptionnotify, 
       'Y'                                                                       AS deliverynotify, 
       11                                                                        AS boxlength, 
       9                                                                         AS boxwidth,
       12                                                                        AS boxheight
FROM   tblworkorders t0 
       INNER JOIN institutions t2 
         ON t0.institution_id = t2.institution_id 
       LEFT JOIN tbladdresses t3 
         ON t0.shiptoaddressid = t3.addressid 
       LEFT JOIN tblpeople t5 
         ON t5.person_id = t0.researcherid 
       LEFT JOIN tblpeople t4 
         ON t4.person_id = t0.shiptoattnid 
       INNER JOIN (SELECT t0.* 
                   FROM   tblshippingdetails t0 
                          INNER JOIN tblshippingroute t2 
                            ON t0.shiproute = t2.shippingrouteid 
                   WHERE  (t2.shippingroute LIKE '%UPS%')) AS t6 
         ON t0.invoicenumber = t6.invoicenumber 
WHERE  ((t6.shipdate IS NULL) 
        AND (t6.boxes <> 0))
 
Can you print out the result (a bogus result will suffice, mainly just to show how it is laid out) you get with this derived table t6?

Code: 
(SELECT t0.* 
                   FROM   tblshippingdetails t0 
                          INNER JOIN tblshippingroute t2 
                            ON t0.shiproute = t2.shippingrouteid 
                   WHERE  (t2.shippingroute LIKE '%UPS%')) AS t6

I think that's where we want to look if we want to "pivot" the table (not sure if that is really what we want) as George suggested.
 
MSAccessRookie,
You cannot process multi-package shipments in UOWS in this manner. Your view must supply only a single record for the Reference Key, and the User must manually supply the weight and dimensions (LxWxH) for each package.
 
Thanks for replying to my request for assistance. I apologize for the delay, but other work requirements got in the way.

ByteMyzer:
I agree that UOWS processes a single record at a time. This is why I am looking to create Duplicate records with unique Box Nmber IDs. So far, we have created a compound Key (Inv), and successfully inserted all of the items in the View except for the Number of Packages. The update works when the number of packages is 1, and the users do not need to supply any information. They only need to verify that the information is correct.
​
Banana:
Attached is a spreadsheet containing sample results from the actual view. As you can see, the number of Packages is a value stored in the Shipping Details Table.​
 

Attachments

Rookie,

Add a new table:

tblBoxes
========
id - identity
BoxNumber - Int
TotalBoxes - Int

Sample Data:

1 1 1
2 1 2
3 2 2
4 1 3
5 2 3
6 3 3
7 1 4
8 2 4
9 3 4

and so on

Code: 
SELECT [B][SIZE="3"]t7.BoxNumber[/SIZE][/B],
       t0.institution_id, 
       t2.institution, 
       t6.invoicenumber                                                          AS invoicenumber, 
       t6.shippingdetailsid, 
       t6.boxes                                                                  AS noofpackages, 
       Cast(t6.invoicenumber AS VARCHAR) + Cast(t6.shippingdetailsid AS VARCHAR) AS inv, 
       t0.po_no                                                                  AS ponum, 
       t3.department, 
       t3.address, 
       t3.city, 
       Isnull(t3.state,t3.state_notusa)                                          AS state, 
       Isnull(t3.postalcode,postalcode_notusa)                                   AS postalcode, 
       t0.shiptoaddressid, 
       Isnull(t3.country_notusa,'USA')                                           AS country, 
       t0.shiptoattnid                                                           AS contactid, 
       Isnull(t4.l_name,'') + CASE Isnull(t4.l_name,'') 
                                WHEN '' 
                                THEN '' 
                                ELSE CASE Isnull(t4.f_name,'') 
                                       WHEN '' 
                                       THEN '' 
                                       ELSE ', ' 
                                     END 
                              END + Isnull(t4.f_name,'') AS shiptoname, 
       Isnull(t5.l_name,'') + CASE Isnull(t5.l_name,'') 
                                WHEN '' 
                                THEN '' 
                                ELSE CASE Isnull(t5.f_name,'') 
                                       WHEN '' 
                                       THEN '' 
                                       ELSE ', ' 
                                     END 
                              END + Isnull(t5.f_name,'') AS researchername, 
       Isnull(t4.email,'')                                                       AS shiptoemail, 
       Isnull(t5.email,'')                                                       AS researcheremail, 
       CASE Isnull(t4.email,'') 
         WHEN '' 
         THEN CASE Isnull(t5.email,'') 
                WHEN '' 
                THEN '' 
                ELSE t5.email 
              END 
         ELSE t4.email 
       END AS theemail, 
       'Y'                                                                       AS qvnotify,
       'Y'                                                                       AS shipnotify, 
       'Y'                                                                       AS exceptionnotify, 
       'Y'                                                                       AS deliverynotify, 
       11                                                                        AS boxlength, 
       9                                                                         AS boxwidth,
       12                                                                        AS boxheight
FROM   tblworkorders t0 
       INNER JOIN institutions t2 
         ON t0.institution_id = t2.institution_id 
       LEFT JOIN tbladdresses t3 
         ON t0.shiptoaddressid = t3.addressid 
       LEFT JOIN tblpeople t5 
         ON t5.person_id = t0.researcherid 
       LEFT JOIN tblpeople t4 
         ON t4.person_id = t0.shiptoattnid 
       INNER JOIN (SELECT t0.* 
                   FROM   tblshippingdetails t0 
                          INNER JOIN tblshippingroute t2 
                            ON t0.shiproute = t2.shippingrouteid 
                   WHERE  (t2.shippingroute LIKE '%UPS%')) AS t6 
         ON t0.invoicenumber = t6.invoicenumber 
       [B][SIZE="3"]Left Join tblBoxes t7 
         On t6.Boxes = t7.TotalBoxes[/SIZE][/B]
WHERE  ((t6.shipdate IS NULL) 
        AND (t6.boxes <> 0))

btw, Thanks Banana for beautifying the code.

Wayne
 
Rookie,

Oops ... that won't work.

When you process a shipment that has multiple orders with the same
numbers of boxes, they'll Cartesian and that is definitely what you want.

I can't think of any type of join that you can do to prevent that condition.
Looks like a Stored Procedure, filling a temp# table, looping for each original
row and using that resultset.

Maybe some more thought will help, but I gotta go back to work.

Interesting problem though.

Wayne
 
Wayne:

I agree that adding a Table would be easy, but populating it might not be. The data is dynamic and changes every day as shipments are prepared for delivery. I had been thinking that a Query (or perhaps another View) might be a better way to handle the dynamic nature of the data, but I have not yet been able to complete the concept in my mind.
 
MSAccessRookie said:
I agree that UOWS processes a single record at a time. This is why I am looking to create Duplicate records with unique Box Nmber IDs. So far, we have created a compound Key (Inv), and successfully inserted all of the items in the View except for the Number of Packages. The update works when the number of packages is 1, and the users do not need to supply any information. They only need to verify that the information is correct.
Click to expand...

Unfortunately, this means that multi-package shipments would be processed as individual shipments, and not a single shipment consisting of multiple packages. UOWS would have no way to aggregate these shipments for the appropriate costing adjustments for Hundredweight or International shipments.

You are trying too hard to dumb it down for the End-Users, who, as Shipping Clerks, need to be able to take some responsibility.
 
You must log in or register to reply here.

Similar threads

D
Query Not sorting?
Replies
2
Views
602
Deleted member 141518
D
C
SQL - Append Query
Replies
2
Views
772
Cotty42
C
Rx_
Concatenate Access SQL to TSQL (SQL Server)
Replies
0
Views
3,570
Rx_
Rx_
MSAccessRookie
Problem locating Next Record using "Next Record" Macro
Replies
10
Views
3,674
MSAccessRookie
MSAccessRookie
MSAccessRookie
Form locking with multiple users
Replies
5
Views
1,469
Banana
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom