Re: SQL equivalent of query needed -- MIDRANGE-L

Don't forget that RTVQMQRY will NOT retrieve the correct SQL if your Query/400 query uses type 2 or 3 for "Type of Join". If you use type 2 or type 3 join in your query, you'll need to manipulate the SQL retrieved from the query to make it work properly...

Steve

From the Help text for "Type of Join" in Query/400:


1=Matched records
    Selects only the records that have matching records in
    all  the joined files.  This type of join uses only
    records from each file that has a match with at least
    one record in each and every one of the other selected
    files.


2=Matched records with primary file
    Selects  every  record in one file (the primary file),
    and includes all the matching records from all the
    other (secondary) files.  Every record in the primary
    file is selected whether or not it has a match.  (The
    primary file is always the first file specified in
    your query definition.)

    If a secondary file has no record that matches the
    join specifications of the primary file's record,
    system defined defaults like blanks (for character
    fields) or zeros (for numeric fields) are used as the
    data for that secondary file's selected fields.  Date,
    time, and timestamp fields in the iSeries format will
    show the default value.  Date and timestamp fields in
    SAA formats will show year one, month one, and day
    one.

    However, if the secondary file was defined using DDS,
    and if the DFT keyword was used to define default
    values for any of the fields used here, those values
    are used instead of the blanks and zeros.

    For more information about the DFT keyword, refer to
    the  DDS Reference  manual.


3=Unmatched records with primary file
    Selects, from the primary file, only records that have
    no match in at least one of the secondary files.  That
    is, every primary record is selected that does not
    have a matching record in  all  the secondary files.
    For example, if four files are joined and only two of
    the three secondary files have matching records, then
    a record containing the selected information in the
    primary and two secondary files is included as a
    single record in the query output.

    As with the previous type of join, blanks or zeros, or
    default values defined in DDS, are used for the
    character and numeric fields for a missing record in a
    secondary file.  For date, time, and timestamp data,
    the default values, if specified, are used.

    Note:   For each type of join, if either join value is
    NULL, the record will not be selected.

----- Original Message ----- From: <rob@xxxxxxxxx> To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> Sent: Monday, March 07, 2005 10:50 AM Subject: RE: SQL equivalent of query needed

Yes, even if you do not have STRQM, STRSQL, etc loaded on your system, you
can still use RTVQMQRY against a Query/400 query.   Many, many years ago I
wrote an article on this in News 3X/400.  Now, when I do my SQL I may do
things differently - like using JOINs instead of just WHERE clauses.  So I
wouldn't rely on RTVQMQRY being your sole method of learning how to code a
SQL statement.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

michael@xxxxxxxxxxxxxxxxxx
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/07/2005 07:33 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL equivalent of query needed


Does that work for Query/400?

-------- Original Message --------
Subject: RE: SQL equivalent of query needed
From: "Barton, Mike" <Mike.Barton@xxxxxxxxxxxxxxxxx>
Date: Mon, March 07, 2005 7:24 am
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>

Murali
               I'm no expert on SQL but....

Since u have a query in existence, u can use the RTVQMQRY command

Create a source file first then:
RTVQMQRY QMQRY(QRYLIB/QRY) SRCFILE(YOURLIB/QMQSRC) ALWQRYDFN(*YES)

This will extract an SQL equivalent.

HTH

Mike


                               -----Original Message-----
                               From:           murali dhar

[mailto:hydchap1@xxxxxxxxx]

                               Sent:           07 March 2005 12:13
                               To:             midrange-l@xxxxxxxxxxxx
                               Subject:                SQL equivalent of

query needed


                               Hi
                               Very good morning, I need SQL equivalent

of below query ,

can some one please help...
                               I have written query easily, but I want

to check on SQL...

                               QTEMP/YX1                  QTEMP/YY1
                               Type of join = 1 matched records

                               QUERY Conditions:
                               T01.MOFIC EQ  T02.MOFIC
                               T01.MACCT  EQ  T02.MACCT

                               DEFINE ReSULT FIELDS:
                               IMRCHG       T02.IMR01-T01.IMR01

                               OTECHG       T02.OTE01-T01.OTE01
                               SELECT RECORDS ARE AS BELOW:
                               10 T01.MCLASS
                               20 T01.MSUBCL
                               30 T01.MFIRM
                               40 TOTALCALL         IMRCHG - OTECHG
                               50 IMRCHG            T02.IMR01 -

T01.IMR01

60 OTECHG T02.OTE01 -

T01.OTE01

Thanks very much for your help in

advance,


__________________________________________________
                               Do You Yahoo!?
                               Tired of spam?  Yahoo! Mail has the best

spam protection

around http://mail.yahoo.com -- 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 e-mail has been scanned for viruses

by MCI's Internet

Managed Scanning Services - powered by MessageLabs. For further

information

visit http://www.mci.com
###########################################
This message has been scanned by F-Secure Anti-Virus for Microsoft

Exchange.

For more information, connect to http://www.F-Secure.com/

**********************************************************************
This communication and the information it contains: - (a) Is intended

for the person(s) or organisation(s) named above and for no other
person(s) or organisation(s). Access to this mail by anyone else is
unauthorised. (b) Is confidential, and may be legally privileged or
otherwise protected in law. Unauthorised use, circulation, copying or
disclosure of any part of this communication may be unlawful. (c) May be
susceptible to interference, and should not be assumed that it has come in
its original form and/or from the stated sender or PinkRoccade UK accepts
no responsibility for information, errors or omissions in this e-mail or
use or misuse thereof or any act done or omitted to be done in connection
with this communication. If you are not the intended recipient, please
inform postmaster@xxxxxxxxxxxxxxxxx immediately and delete it and all
copies from your system.


www.pinkroccade.co.uk
**********************************************************************

--
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.