RE: Embedded SQL question.

The "." is fine for file.field reference but for library to file
reference it's lib/file the { is no valid but you should be able to
substitute parens for those. 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Hatzenbeler, Tim
Sent: Monday, April 10, 2006 11:48 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Embedded SQL question.

I was actually having trouble the  "." vs "/" difference and the "{" for
the
outer join, doesn't seem to be recognized.

At least when I tested it with strsql...

Thanks, Tim

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Holden Tommy
Sent: Monday, April 10, 2006 9:41 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Embedded SQL question.

First off, when putting this into RPG be sure that you "tell" the
compiler/processor that you are not journalling the file(s).

One way is using the SET OPTION directive in your code,

C/EXEC SQL
C+ SET OPTION COMMIT=*NONE
C/END-EXEC

Another is to add "WITH NC" on your individual SQL statements & lastly
Compile the program setting the compiler option COMMIT(*NONE)

Other than that you should be able to just create a source member of
type SQLRPGLE, cut & paste your statements like so:

C/exec sql
C+  Select * from mylib/myfile 
c/end-exec

Only one SQL statement can be placed between the /exec sql & /end-exec
lines.

HTH...  


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Hatzenbeler, Tim
Sent: Monday, April 10, 2006 11:32 AM
To: RPG programming on the AS400 / iSeries
Subject: Embedded SQL question.


I have this sql statement, that works fine from ops-nav... 

I was wondering, if there is a flag I can set?  Or a conversion tool
available, to change what needs to be changed to work from rpg, or do I
need
to walk through each query, and convert them to the native format?

I have about 50 of these things..

End-result: I would like to embed these sql statements within an RPG
program...

Thanks, Tim

SELECT 
    "MANFILPF"."MNACTC", 
    "MANFILPF"."MNMFG#", 
    "MANFILPF"."MNSIZE", 
    "MANFILPF"."MNDESC", 
    "MANFILPF"."MNUNIT", 
    "PRPPIL"."PILLOC", 
    "PRPPIL"."PILMNQ", 
    "PRPPIL"."PILMXQ", 
    "DANA2"."MONTHUSG", 
    "PRDINVEN"."MIQTY", 
    "PRDINVEN"."MITYPE", 
    "PRPPIL"."PILCO" 
FROM {oj (("EVCLFIN"."CADBFA"."MANFILPF" "MANFILPF" 
LEFT OUTER JOIN 
    "EVCLFIN"."CADBFA"."PRPPIL" "PRPPIL" 
    ON (("MANFILPF"."MNMFG#"="PRPPIL"."PILPRD") 
    AND ("MANFILPF"."MNSIZE"="PRPPIL"."PILSIZ")) 
    AND ("MANFILPF"."MNUNIT"="PRPPIL"."PILIUM")) 
LEFT OUTER JOIN 
    "EVCLFIN"."CADBFA"."PRDINVEN" "PRDINVEN" 
    ON (((("PRPPIL"."PILCO"="PRDINVEN"."MICO") 
    AND ("PRPPIL"."PILLOC"="PRDINVEN"."MILOC")) 
    AND ("PRPPIL"."PILPRD"="PRDINVEN"."MIPROD")) 
    AND ("PRPPIL"."PILSIZ"="PRDINVEN"."MISIZE")) 
    AND ("PRPPIL"."PILIUM"="PRDINVEN"."MIFUM")) 
LEFT OUTER JOIN 
    "EVCLFIN"."DEVTIMH"."DANA2" "DANA2" 
    ON (((("PRPPIL"."PILCO"="DANA2"."COMPANY") 
    AND ("PRPPIL"."PILLOC"="DANA2"."WAREHOUSE")) 
    AND ("PRPPIL"."PILPRD"="DANA2"."ITEM_CODE")) 
    AND ("PRPPIL"."PILSIZ"="DANA2"."SIZE_CODE")) 
    AND ("PRPPIL"."PILIUM"="DANA2"."UOM")} 
WHERE "PRDINVEN"."MITYPE"='AA' 
    AND "PRPPIL"."PILLOC"='FANK2' 
    AND "MANFILPF"."MNACTC"='A' 
    AND "PRPPIL"."PILCO"='  001'