FW: Embedded SQL with Coalesce

In my haste to get out of here last night, I mistakenly just replied to Scott.

Thanks to all for your suggestions. The problem was a mis-match between the Declare Select and the fetch statements as I told Scott below.

The suspicious line Birgitta and Scott saw was just for explanation as I told Scott last night.

Thanks everyone, it's working now.

Any questions, let me know please.

Thanks,

Dave Boettcher

Two rules to eliminate stress:
1. Don't sweat the small stuff. 2. It's all small stuff.

-----Original Message-----
From: Dave Boettcher
Sent: Tuesday, May 29, 2012 5:18 PM
To: 'Scott Klement'
Subject: RE: Embedded SQL with Coalesce

Hi Scott,

The line you cite was intended to show the difference between the one that ran in iNav with 2011 and the embedded SQL with :RPTTXYR - a program variable.
However, you were right about the fetch. Over the process of testing and debugging my fetch statement at the end of the loop no longer matched the declare cursor statement.

Thanks for all the help,

Any questions, let me know please.

Thanks,

Dave B

"It is not real work unless you would rather be doing something else."   J.M.BARRIE


-----Original Message-----
From: Scott Klement [mailto:rpg400-l@xxxxxxxxxxxxxxxx]
Sent: Tuesday, May 29, 2012 4:58 PM
To: RPG programming on the IBM i / System i
Cc: Dave Boettcher
Subject: Re: Embedded SQL with Coalesce

Hi Dave,

This line looks suspicious to me:

and A.artxyr = 2011 || :RPTTXYR

Are you truly trying to concatenate a number (2011) to a variable? If so, SQL will have to implicitly do some casting to make it work, which might be where the issue is.

The other thing that comes to mind is the FETCH statement -- I'm mainly suspicious of the FETCH because you say it works in iNav, which implies the issue is something you wouldn't have done in iNav (such as a fetch)

It's possible that SQL is implicitly casting between a numeric/character data type in the fetch... maybe because the receiver data type doesn't match the database data type. Worth checking into...

-SK



On 5/29/2012 4:49 PM, Dave Boettcher wrote:

Kurt, Good guess. I still receive the error either way.

Jonathan , You're right it was defined as character. I had great hopes
for this one but still receive the error. I should only need the
coalesce on the Declare statement not the fetch, right?

Tom, the phones are signed numeric and so I tried the + signs. It
seems that a zero shouldn't need to have a sign but it was worth a
shot.