Re: SQL Statement failing in COBOL program

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".