Re: Strange SQL

Hi Mark,

For a left outer join, if there is no matching record from the secondary
file you still get a record containing fields from the primary file and
nulls or default values in the secondary file's fields.  Sounds like your
test environment may not match your production environment.

hth,
Peter Dow
Dow Software Services, Inc.
909 425-0194 voice
909 425-0196 fax

----- Original Message -----
From: <MWalter@hanoverwire.com>
To: <rpg400-l@midrange.com>; <rpgiv@yahoogroups.com>
Sent: Wednesday, September 12, 2001 7:55 AM
Subject: Strange SQL


> Hello all,
>
> I am trying to create a dynamic sql statement in an ILE RPG Module. When I
> execute the statement:
>
> SELECT a.itnbr, b.itdsc, a.edate, a.itcls, b.itcls, b.lstum, a.price,
> b.unmsr FROM hwoei6.Prcmst a LEFT OUTER JOIN amflib.Itemasa b ON a.itnbr
>  = b.itnbr WHERE cusno =   206800 ORDER BY a.itnbr
> (the tables are not qualified in the RPG Program. Libl is used)
>
> from the Program, all columns returned from the secondary file are
returned
> in HEX therefore not being included into the program. but when I run the
> statement using interactive SQL, it works fine. I copied the statement
into
> the SQL Script tool in OpsNav, it also returns hex for the b file. The
> really funny thing is if I change the amflib qualifier, which is a test
> environment, to amfliba, which is a live environment, the statement
> executes fine in the OpsNav tool.
>
> I'm lost here,
>
>
> Thanks,
>
> Mark
>
>
> Mark Walter
> Sr. Programmer/Analyst
> Hanover Wire Cloth a div of CCX, Inc.
> mwalter@hanoverwire.com
> http://www.hanoverwire.com
> 717.637.3795 Ext.3040
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com