Re: Embedded SQL in ILERPG

[Ravi Viswanathan <ravi@xxxxxxxxxxxxx>]

John,

I tried this out as a workaround yesterday, but I get a run time error when my
program is executed. The error is ORA-00904: Invalid column name.
Is this because "coalesce" is not valid in Oracle?

Thanks

JohnHester@eaton.com wrote:

> You probably have null fields rather than blanks. In oracle there is a
> funvtion called NVL, the DB2/400 equivalent is coalesce. You can either
> user coalesc in your emmbedded SQL select or use null indicators. I would
> suggest using coalesce as in
>
> SELECT field1, field2, COALESCE(field3, '  '), field4
>           FROM file1
> This will put blanks in the field.
>
> Regards
>
> John
>
> -------------
> Original Text
> From: C=US/A=INTERNET/DDA=ID/MIDRANGE-L(a)midrange.com, on 16/03/99 23:05:
>  Hello all,
>
> I am trying to retrieve data from an Oracle database using Oracle Access
> Manager
> and embedded SQL in ILERPG.
>
> I am selecting 12 columns from the ORacle table and inserting into a PF
> locally.
> I have problems with one of the Oracle columns and if I remove this column
> everything works fine.
>
> The problem is this: On  Oracle the field is defined as :
> DESCRIPTION_2_TXT      VARCHAR2(30)
> In the records that I need to retrieve, all the values are blank in this
> column .
> After I FETCH the cursor, SQLCA has the following values ( I've just pasted
> the
> msgs that I think are relevant)
>
>   SQLCA                 DS
>       SQLCOD              BIN(9,0)             -000000305.      'FFFFFECF'X
>
>     SQLERM              CHAR(70)             '  DESC2
>                              VALUE IN HEX
> '0009C4C5E2C3F24040404040404040404
>                              41
> '404040404040404040404040404040404
>
>    SQLERR              CHAR(24)             '                        '
>                       VALUE IN HEX
> '000000000000000000000000000000000
>
>   SQLSTT              CHAR(5)              '22002'          'F2F2F0F0F2'X
>
> The host variable that I am retrieving this into is defined as 30A. No
> values
> from any of the other columns are retrieved. If I get rid of this field
> from my
> cursor definition, the code works.  How can I "monitor" for this scenario ?
> Also,
> where do I find more information on these error codes? Can't find anything
> in the
> manuals........
>
> Any help will be appreciated.....
>
> Thanks
>
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> -
>
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---