× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. May 2006

RE: SQL Ideas Please

Great.  Thanks for your input, Charles.

Brian.

-----Original Message-----
From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx] 
Sent: Tuesday, May 16, 2006 4:08 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Ideas Please

Ah, good point.

I don't think you can.

You could use a Global temporary table instead.

DECLARE GLOBAL TEMPORARY TABLE AS (
  SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) AS FDATE 
  FROM SSW85
  GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM
);


DELETE FROM SSW85 A
Where not exists( select * 
                  from (preserve P
                  where A.SCACODE  = P.SCACODE  AND 
                        A.TRLRNUM  = P.TRLRNUM  AND 
                        A.ASNNUM   = P.ASNNUM   AND
                        A.ORDERTYP = P.ORDERTYP AND
                        A.ORDERNUM = P.ORDERNUM AND 
                        A.FDATE   = P.FDATE
                 );

Notice, that you have two separate statements now.


If you want a single statement, you could try...
DELETE FROM SSW85 A
Where not exists( select * 
                  from ( SELECT
SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) AS FDATE 
                         FROM SSW85
                         GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP,
ORDERNUM) P
                  where A.SCACODE  = P.SCACODE  AND 
                        A.TRLRNUM  = P.TRLRNUM  AND 
                        A.ASNNUM   = P.ASNNUM   AND
                        A.ORDERTYP = P.ORDERTYP AND
                        A.ORDERNUM = P.ORDERNUM AND 
                        A.FDATE   = P.FDATE
                 )

But I've got doubts on performance....


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
> Sent: Tuesday, May 16, 2006 3:45 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL Ideas Please
> 
> Can you use common table expressions with the DELETE 
> statement?  I have just
> come out of V5R1, and haven't played with V5R3 much yet....
> 
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-297-2863 or ext. 1863
> 
> 
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Wilt, Charles
> Sent: Tuesday, May 16, 2006 2:36 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL Ideas Please
> 
> 
> Can you use a join-table clause in a delete statement?
> 
> I didn't think so, but maybe.
> 
> If not try:
> WITH preserve AS (
>   SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) 
> AS FDATE 
>   FROM SSW85
>   GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM
> )
> DELETE FROM SSW85 A
> Where not exists( select * 
>                   from preserve P
>                   where A.SCACODE  = P.SCACODE  AND 
>                         A.TRLRNUM  = P.TRLRNUM  AND 
>                         A.ASNNUM   = P.ASNNUM   AND
>                         A.ORDERTYP = P.ORDERTYP AND
>                         A.ORDERNUM = P.ORDERNUM AND 
>                         A.FDATE   = P.FDATE
>                  )
> 
> 
> The same format would work for the first request to:
> WITH preserve AS (
>   SELECT distinct SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM 
>   FROM SSW85
> )
> DELETE FROM SSW85 A
> Where not exists( select * 
>                   from preserve P
>                   where A.SCACODE  = P.SCACODE  AND 
>                         A.TRLRNUM  = P.TRLRNUM  AND 
>                         A.ASNNUM   = P.ASNNUM   AND
>                         A.ORDERTYP = P.ORDERTYP AND
>                         A.ORDERNUM = P.ORDERNUM
>                 )
> 
> HTH,
> 
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>   
> 
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx 
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
> > Sent: Tuesday, May 16, 2006 3:23 PM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: RE: SQL Ideas Please
> > 
> > Brian, I'm not sure I fully understood you (especially 
> > DISTINCT part - SQL
> > keyword?) so make sure you test this on a dummy/copied file first.  
> > Alternatively, just run the SELECT part on its own to verify 
> > that projected
> > rows are ones you want to preserve.
> > 
> > WITH preserve AS (
> >   SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) 
> > AS FDATE 
> >   FROM SSW85
> >   GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM)
> > DELETE FROM SSW85 A, preserve P
> > WHERE A.SCACODE  = P.SCACODE  AND 
> >       A.TRLRNUM  = P.TRLRNUM  AND 
> >       A.ASNNUM   = P.ASNNUM   AND
> >       A.ORDERTYP = P.ORDERTYP AND
> >       A.ORDERNUM = P.ORDERNUM AND 
> >       A.FDATE   <> P.FDATE
> > 
> > Elvis
> > 
> > -----Original Message-----
> > Subject: SQL Ideas Please
> > 
> > Hi All,
> > 
> > (This is a two-part SQL question)
> > 
> > Can someone tell me if there is an easy way to delete records from a
> > table where there are only four data elements on which I 
> wish to base
> > the delete routine?  For example, I have a table that has numerous
> > records in it.  I want to delete all records other than those 
> > that have
> > a distinct SCACCODE, TRLRNUM, ASNNUM, ORDERTYP and 
> ORDERNUM.  However,
> > I'm not sure how to express this in SQL, but in pseudo-code, 
> > I'd like to
> > do this:
> > 
> > DELETE ALL RECORDS IN SSW85 EXECPT THOSE RECORDS THAT HAVE 
> A DISTINCT
> > (SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM).
> > 
> > My second SQL statement needs to pass over the records 
> again and only
> > keep records that have the minimum number for a specific 
> column.  For
> > example, if I have these records:
> > 
> > A          B          C                      D          E          F
> > 
> > ABCD   1000     10039210          2          105049  206052971
> > ABCD   1000     10039210          2          105049  206052951
> > ABCD   1000     10039210          2          105049  206052991
> > ABCD   1000     10039210          2          105049  206053001
> > 
> > I only want to keep one of these records that has the 
> minimum value in
> > column F.  So if I ran a min(COLUMN_F) on this, I would 
> only get back
> > the second record (since it is the lowest number for Column F).
> > Pseudo-code wise, I'd need something like:
> > 
> > DELETE ALL RECORDS IN SSW85 EXCEPT EACH DISTINCT (SCACCODE, TRLRNUM,
> > ASNNUM, ORDERTYP, ORDERNUM) WITH THE LOWEST KDLOTSTR.
> > 
> > Is there an easy way to do both of these statements (or combine them
> > into one statement)?
> > 
> > Thanks!
> > 
> > Brian. 
> > 
> > 
> > -- 
> > 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.
> 
> 
> -- 
> 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.
> 
>

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.