RE: Embedded Sql problem -- MIDRANGE-L

My example is similar but it works (OPM though)

In the input specs :

0031.00      I                                    B  52  530NMIND

In the calcs:

0035.00      C                     MOVE *ZERO     NMIND

Then the SQL statement:

0037.00      C/EXEC SQL DECLARE C1 CURSOR FOR
0038.00      C+ SELECT EMLNAM,EMFNAM,EMMNAM,PCCK#,PCRUN#,EMLOC,LCNAME,PRCKDT
0039.00      C+     FROM PPAI612S INNER JOIN PPAY600H ON PCRUN# = PRRUN#
0040.00      C+     INNER JOIN PEIS301 ON PCSSN = EMSSAN
0041.00      C+     LEFT OUTER JOIN PEIS127 ON EMLOC = LCCODE
0041.01      C+     WHERE PCTYP = '1'
0042.00      C+     ORDER BY EMLOC,EMLNAM,EMFNAM,EMMNAM
0043.00      C/END-EXEC

then the fetch:

0054.00      C/EXEC SQL
0055.00      C+ FETCH C1 INTO
:LAST,:FIRST,:MIDDLE,:CHECK,:RUN,:LOC,:LCNAM:NMIND
0056.00      C+               :CKDAT
0057.00      C/END-EXEC


Took me a while to get it right, like Rob said: It IS a tad finicky about
such things

Pete Helgren
Timpanogos Technologies


> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
> Sent: Tuesday, January 20, 2004 11:18 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Embedded Sql problem
>
>
> 1)  SQL can be a little b!tchy about qualified data structures.  The SQL
> precompiler team is coming to life and this may change in the future.
> 2)  there is NO comma between the variable and the indicator variable for
> example
> FETCH Next From CtlCurs INTO :SMSO#,:SMSCD,:SMCRW,:UPRTE,
> :UPSAD,:SCEST, :NullIND
> becomes
> FETCH Next From CtlCurs INTO :SMSO#,:SMSCD,:SMCRW,:UPRTE,
> :UPSAD,:SCEST :NullIND
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
> 01/20/2004 12:33 PM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> RE: Embedded Sql problem
>
>
>
>
>
>
> Rob,
> I tried that.  However, I'm now getting the unusual message SQL0303 that
> is telling me the
>  Host variable PRMDS.UPSAD not compatible.
>  SQLCOD = -000000303.
>
> My fetch statement now looks like
> FETCH Next From CtlCurs INTO :SMSO#,:SMSCD,:SMCRW,:UPRTE,
> :UPSAD,:SCEST, :NullIND
>
> I've switched locations in the list for the NullInd, because I wasn't
> sure exactly where it needs to go.  No difference.
> I've tried making using host variable names.  No difference.
>
> I've written hundreds of programs like these(however not usually with a
> specific Outer join.) and never had a problem like this.
>
> I have a feeling it has to do with the fact, that the record that isn't
> showing up doesn't have a record with the UPRM file so that UPSAD and
> UPRTE are blank.  However, I kind of thought that was what the indicator
> variable was handling.
> -----Original Message-----
> From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
> Sent: Tuesday, January 20, 2004 11:26 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Embedded Sql problem
>
>
> Mike, in your earlier desired result you listed two columns.  Is that
> all
> you really want?  If so, then drop the SELECT * and instead just select
> the two desired columns.
> then it would be, (assuming that only the first column supports null
> values):
> C/EXEC SQL
> C+ Fetch NEXT FROM CTLCURS Into :ColA :indA ,:ColB
> C/END-EXEC
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 01/20/2004 11:07 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc
>
> Fax to
>
> Subject
> RE: Embedded Sql problem
>
>
>
>
>
>
> Well, I've found that I'm getting an -SQL0305 error which indicates that
> I need a Indicator Variable.
>
> Some snippets of the code
>
>  * DATA STRUCTURES
> D SODS          E DS                  EXTNAME(SCMS) INZ
> D PrmDS         E DS                  EXTNAME(UPRM) INZ
> D CodDS         E DS                  EXTNAME(SOCD) INZ
>
> C                   Eval      SelectOne = %TRIM(SQLARY(1)) + ' ' +
> C                                         %TRIM(SQLARY(2)) + ' ' +
> C                                         %TRIM(SQLARY(3)) + ' ' +
> C                                         %TRIM(SQLARY(4)) + ' ' +
> C                                         %TRIM(SQLARY(5)) + ' ' +
> C                                         %TRIM(SQLARY(6)) + ' ' +
> C                                         %TRIM(SQLARY(7)) + ' ' +
> C                                         %TRIM(SQLARY(8)) + ' ' +
> C                                         %TRIM(SQLARY(9)) + ' ' +
> C                                         Where + ' ' +
> C                                         'Order By ' + %trim(OrderBy)
>
> C/EXEC SQL
> C+  PREPARE SEL FROM :SelectOne
> C/END-EXEC
> C/EXEC SQL
> C+  Declare CTLCURS SCROLL Cursor  For SEL
> C/END-EXEC
>
>  *OPEN THE CURSOR
> C/EXEC SQL
> C+  Open CTLCURS using :sodat
> C/END-EXEC
>
>
> I fetch using the following
> C/EXEC SQL
> C+ Fetch NEXT FROM CTLCURS Into :SoDS ,:prmDS, :CodDS
> C/END-EXEC
>
>
>
> I can't figure out how to incorporate a indicator variable the way I
> have it set up
>
>
>
>
>
>
> _______________________________________________
> 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.
>
>
> _______________________________________________
> 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.
>