Re: SQL Statement failing in COBOL program

[qsrvbas@xxxxxxxxxxxx]

Jim Essinger wrote:
>    4. Re:  SQL Statement failing in COBOL program (Jim Essinger)
>
> date: Thu, 23 Aug 2007 10:41:57 -0600
>
> Ok - here is the solution.  Count the variables correctly and things will
> work. I had repeated one field in the declare cursor statement, and 
> when I
> removed the duplicate field, I get the result that I desired.  It sure 
> helps
> when SQL can match up the number of variables!

Jim:

Glad you have the resolution. Sorry I had no time to contribute to it. 
(And nice info on the recent CAST() enhancements! Thanks.)

This is for general list comment..., I sent my first reply simply 
because I saw the comments "The statement works in STRSQL but the exact 
same statement fails in embedded SQL." (Paraphrased, but shows the point.)

/Far/ too often, the "exact same statement" will fail because (a) you 
can't run the same statements in STRSQL as in embedded SQL, (b) the 
statement really isn't "exact same" but has been modified for host 
variables, etc., or (c) additional statements and/or definitions exist 
in the program that modify _how_ the "exact same" statement runs. So, 
when I see that kind of comment, the first thing that comes to mind is 
to ask to see everything that supports the statement -- host variable 
definitions, related SQL statements such as FETCH or whatever else.

It didn't look as if there was progress in helping you being made by the 
list, so I threw in the "Can you show the rest?" question.

Now that I have a few moments to catch up, it looks like others started 
narrowing things down real fast.

Anyone using both STRSQL and embedded SQL should always keep the 
differences in mind.

A decade or so ago, the answer for strange bugs was regularly "Fix your 
library list." Nowadays for SQL, I think we need a new 'default'. I'm 
not sure how to word it, but maybe something like "Show me that it's 
'exactly the same'."

Tom Liotta

> On comparing the character version of a number with an actual number 
> field,
> I found that
>
> cast(1234567.89 as Char(10))
>
> leaves the decimal in place.  What I needed was the entire number without
> the decimal.
>
> replace(1234567.89,".","")
> or
> digits(1234567..89)
>
> seems to do what I need in one step.  The result is a valid compare 
> with the
> character number "123456789".