Re: how to use sql distinct in CL?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

AH, since you are getting a table, you can use RUNSQL with something like this -

 CREATE TABLE LIB/FILE AS (select-statement) WITH DATA

No Query/400 needed at all

The QMQRY approach is very powerful, as you can imagine. There are several free SQL-statement-processor commands out there that use it for ad hoc queries.

HTH
Vern

----- Original Message -----
Vern, yes, a table as the result. Thanks for good info to all.


On Tue, Jul 2, 2013 at 9:52 AM, <rob@xxxxxxxxx> wrote:

> Step 1:  Analyze your Query/400 query for conversion to SQL with ANZQRY.
> Step 2:  CRTSRCPF FILE(MYLIB/QQMQRYSRC)
> Step 3:  RTVQMQRY QMQRY(MYLIB/MYQUERY) SRCFILE(MYLIB/QQMQRYSRC)
> ALWQRYDFN(*YES)
> Step 4:  Add distinct with:  EDTF FILE(MYLIB/QQMQRYSRC) MBR(MYQUERY)
> Step 5:  "Compile" query with:  CRTQMQRY QMQRY(MYLIB/MYQUERY)
> SRCFILE(MYLIB/QQMQRYSRC) SRCMBR(MYQUERY)
> Step 6:  Execute query with:  STRQMQRY QMQRY(MYLIB/MYQUERY)
>
> For more information:
>
> http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rzatc/sc415703.pdf
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
> To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
> Date:   07/02/2013 09:30 AM
> Subject:        how to use sql distinct in CL?
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> I have a ad hoc process, where I can make use of the DISTINCT feature of
> SQL
>
> THe CL would first run query400 then  I need the results as
>
> Distinct.
>
> Can you do that SQL statement in a CL?
> --
> 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.