MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

AW: Summarizing and fixing up some data



fixed

Common Table Expressions are always part of an SELECT Statement and NEVER
EVER of an INSERT or UPDATE statement!

Syntax SQL Statement:
WITH ... as (SELECT ....) ... multiple CTEs (optinal)
Select ....
From ...
Where ... (optional)
Group By ... (optional)
Having ... (optional)
UNION or INTERSECT or EXCEPT (Optional)
Select ...
...
Order By ... (Optional)

Select / From / Where / Group By / Having = Sub-Select
Multiple Sub-Selects can be merged by using a UNION, INTERSECT or EXCEPT
clause
The result of the merged sub-selects can be sorted by adding an ORDER BY
clause = Full-Select

The complete SELECT-Statement can additionally include one or multiple CTE.

Syntax for an INSERT Statement (with Select)
INSERT INTO ...
With ... (optional)
Select ...

Syntax for an Update Statement (with Select)
Update ...
Set (FLD1, Fld2, ... FldN) = (Row-Full-Select)

Or
Update ...
Row = Row-Full-Select

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dennis Lovelady
Gesendet: Wednesday, 03.10 2012 13:57
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Summarizing and fixing up some data

In SQL, there is no way that I know of to delete and update in one
statement.

So here is one solution (I have not tried it), requiring two statements.
NOTE the difference between the T1.RRNBR : RRN(PROBLEMTBL) comparisons in
the UPDATE and the DELETE. This is key to your specs.


WITH T1 AS (SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO, WAVE
HAVING COUNT(*) > 1
)
UPDATE PROBLEMTBL PT
SET WAVE = 'MULTI'
WHERE EXISTS
(SELECT 1
FROM T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR = RRN(PROBLEMTBL)
)



WITH T1 AS (SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO, WAVE
HAVING COUNT(*) > 1
)
DELETE FROM PROBLEMTBL PT
WHERE EXISTS
(SELECT 1
FROM T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR < RRN(PROBLEMTBL)
)


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
The pessimist's ultimate observation:
-- - To make the attempt, is the first step toward faulure.



I have a file that has columns like this:



Shipper, Consignee, PO#, Wave#.



If there is more than one wave# per Shipper/Consignee/PO#, I want to
delete all but one of the records, and change the wave# to 'MULTI'



If there is only one record, leave it alone. Any ideas on how I could
do
this with SQL or RPG?



Thanks.



Jack

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact