Re: SQL CTE with no results

Buck:
Yep, state is 02000. Both the item (spitm1 thru 5) are all 6S 0
externally defined
as well as FXITEM (6s 0). The only message I get while I am stepping thru
is that
SQL7919 shows up with reason 2-Host variable GROSS is a numeric type that
is different than the type of the value being retrieved....hmm. But it
also says the
statement ran correctly. I've now run it with GROSS defined as int as
well as
decimal. Both give same 0 results.



Bill





From: Buck Calabro <kc2hiz@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 12/20/2016 03:26 PM
Subject: Re: SQL CTE with no results
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



On 12/20/2016 3:50 PM, broehmer@xxxxxxxxxxxxxxx
wrote:

My ILE code doesn't work:

Assuming you are getting SQLSTATE 02000?

strsql = 'with column(total) as +
(select spqty1 from spfs where spitm1 = ? +
union all +
select spqty2 from spfs where spitm2 = ? +
union all +
select spqty3 from spfs where spitm3 = ? +
union all +
select spqty4 from spfs where spitm4 = ? +
union all +
select spqty5 from spfs where spitm5 = ?) +
select sum(total) from column';
exec sql Prepare sel from :strsql;
exec sql Declare Cur CURSOR for sel;
exec sql Open Cur using :fxitem, :fxitem, :fxitem, :fxitem,
:fxitem;

exec sql fetch Cur into :gross :NullInds;

What is the dta type and length of SPITM1? FXITEM? Do they match,
exactly?

If not, is it a case where one is right justified? Left justified
(trailing blanks vs leading blanks)? Padded (one longer than the other)?