• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Updating a COALESCE

I have a query below that all i want to do is update the c_tracking_no

The problem is that to get the c_tracking_no from the query  it uses

COALESCE (p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no)  

Is it possible to update this
SELECT     oe_pick_ticket.pick_ticket_no, oe_pick_ticket.print_date, COALESCE (p21_view_clippership_return_10004.carrier_name, address.name) AS c_carrier, 
                      COALESCE (p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no) AS c_tracking_no, 
                      COALESCE (p21_view_clippership_return_10004.total_charge, oe_pick_ticket.freight_out) AS c_freight_out, oe_pick_ticket.freight_in, 
                      oe_pick_ticket.pick_and_hold, COALESCE (p21_view_clippership_return_10004.shipped_date, oe_pick_ticket.ship_date) AS c_ship_date, 
                      oe_pick_ticket.invoice_no, oe_pick_ticket.auxiliary, oe_pick_ticket.direct_shipment, oe_pick_ticket.delete_flag, oe_pick_ticket.order_no, 
                      oe_pick_ticket.total_tax, freight_code.freight_cd, oe_pick_ticket.freight_code_uid, oe_hdr.packing_basis, oe_hdr.company_id, 
                      oe_pick_ticket.oe_pick_ticket_type_cd, oe_pick_ticket.outgoing_freight_cost, 
                      CASE WHEN oe_pick_ticket_type_cd = 1921 THEN 'Parts Pick Ticket' WHEN oe_pick_ticket_type_cd = 1922 THEN 'Service Pick Ticket' WHEN oe_pick_ticket.direct_shipment
                       = 'Y' THEN 'Direct ptshipment' WHEN oe_pick_ticket.auxiliary = 'Y' THEN 'Auxiliary Pick Ticket' WHEN oe_pick_ticket.pick_and_hold = 'Y' THEN CASE WHEN
                       oe_hdr.packing_basis = 'Tag and Hold' THEN 'Tag And Hold' ELSE 'Pick And Hold' END ELSE CASE ptshipment.transaction_type_cd WHEN 222 THEN 'Shipped via Order Entry'
                       ELSE 'Regular Pick Ticket' END END + CASE WHEN COALESCE (p21_view_clippership_return_10004.clippership_return_uid, 0) 
                      <> 0 THEN ' (Clippership)' WHEN COALESCE (ptshipment.shipment_uid, 0) 
                      <> 0 THEN ' (' + carriertype.code_description + ')' ELSE '' END AS shipment_type_display, 
                      CASE ptshipment.transaction_type_cd WHEN 222 THEN oe_hdr.order_no ELSE oe_pick_ticket.pick_ticket_no END AS transaction_no, 
                      ptshipment.shipment_uid, p21_view_clippership_return_10004.clippership_return_uid, address.carrier_type_cd, 
                      COALESCE (ptshipment.ship_location_id, oe_pick_ticket.location_id) AS Expr1, ptshipment.transaction_type_cd
FROM         oe_pick_ticket INNER JOIN
                      oe_hdr ON oe_hdr.order_no = oe_pick_ticket.order_no LEFT OUTER JOIN
                      p21_view_clippership_return_10004 ON p21_view_clippership_return_10004.pick_ticket_no = oe_pick_ticket.pick_ticket_no AND 
                      p21_view_clippership_return_10004.delete_flag = 'N' LEFT OUTER JOIN
                      freight_code ON freight_code.freight_code_uid = oe_pick_ticket.freight_code_uid LEFT OUTER JOIN
                      shipment AS ptshipment ON ptshipment.row_status_flag = 2175 AND ptshipment.transaction_type_cd = 1000 AND 
                      ptshipment.transaction_no = oe_pick_ticket.pick_ticket_no LEFT OUTER JOIN
                      address ON address.id = COALESCE (ptshipment.carrier_id, oe_pick_ticket.carrier_id) LEFT OUTER JOIN
                      code_p21 AS carriertype ON carriertype.code_no = address.carrier_type_cd
WHERE     (NOT (oe_pick_ticket.delete_flag = 'Y')) AND (oe_hdr.order_no = '1086114') OR
                      (oe_hdr.order_no = '1086114') AND (NOT (oe_pick_ticket.pick_and_hold = 'Y'))

Open in new window

  • 4
  • 3
1 Solution
What exactly you need updated? The query seems correct it will give check for all the 3 values and return the 1st not null value but if all are nuull then it will return null. You could use this to return 'N/A' when all of them are null
COALESCE (p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no, 'N/A') AS c_tracking_no

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot "update" a query like this directly.
you can only update table rows directly, or views if they have a INSTEAD OF trigger defined.

to update, you have to run a UPDATE statement, and not a select.
can you please clarify what you mean, hence, by "update"
JiggensAuthor Commented:
i want to update c_tracking_no i want to put in there the tracking number that is given to me from UPS Ground shipping
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JiggensAuthor Commented:
I am trying to update the c_tracking_no to have a tracking number so its prints on my order.  Select query i didnt write when i go to the c_tracking_no the only way i see it is when COALESCE is being used.  I dont know where the actual data lives for c_tracking_no

COALESCE(p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no) c_tracking_no,  this is auto generated from my application.

I need to update order 1086114 with 1Z10A2R10394238914.

Thats my goal.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the general syntax to update 1 table from another:
   SET c_tracking_no = gs.c_tracking_no
  FROM p21_view_clippership_return_10004 d
  JOIN [UPS ground shipping]  gs
    ON <join condition>

Open in new window

JiggensAuthor Commented:
angellll  i understand how i update a view but when i do that the data is not there. I tried just updating the table clippership_return_10004

and it says 0 row(s) affected

Any ideas where this data could actually be stored in my Database.  Is there a way i can search for this data?
JiggensAuthor Commented:
I guess i am missing some functionality. but when i go to either table and try selecting trackin_no on the table there is no data??
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am not sure what/how you did that... but ( 0 rows affected ) means  exactly that: no rows have been updated.
means that either
* the "join" condition made that 0 rows are matching
* there are no rows at all in either tables

as you might remember, I do know nothing about your data, and have only minimal information about the tables design.

please clarify in simpler terms, you will eventually "see" the issue yourself.
otherwise, explaining more details will help us see what you are doing "wrong".
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now