RE: SQL Indicator?

Thanks, that eliminated the crazy error message, and still got right data to
the report.

COALESCE is a command function I had not previously known about.

My program is still taking an eternity to execute, so I think I will
replicate the SELECT statement in front with COUNT(*) relevant records, and
if none, don't do the SELECT SUM math, just plug in zero answer.

I am doing an analysis of trends in consumption of raw materials over
multiple years, to help forecast safety stock, re-order quantities, and
identify components whose usage is dropping.

On Fri, 23 Jan 2009 18:50:44 -0600, Dan Kimmel wrote

If you're going to run into situations where no records are selected,
you're going to get a null.

SELECT COALESCE( SUM(TQTY), 0) INTO :ISSUES

Should work.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
macwheel99@xxxxxxxxxx
Sent: Friday, January 23, 2009 6:40 PM
To: List M-L; KE E Al Macintyre
Subject: SQL Indicator?

What should I do to this line, so SQL not gripe about missing indicator
when NULL & what will be the condition of the indicator if the
result is null?

C* SELECT SUM(TQTY) INTO :ISSUES

Here's the error message I am getting:

Message . . . . : Indicator variable required.

Cause . . . . . : A FETCH, an embedded SELECT, a CALL or a SET or
VALUES
INTO statement has resulted in a null value, but an indicator
variable was
not specified for host variable ISSUES. The relative position of
the host variable in the INTO clause or parameter list is 4. If
the host variable name is *N, an SQLDA was specified.
Recovery . . . : Specify an
indicator variable, and precompile the program again.

It says this is happening at program line # 16498
16498
which happens to be a comment
documentation

Here is the code which has been running fine for years:

C*EXEC SQL
C* SELECT SUM(TQTY) INTO :ISSUES
C* FROM ITH WHERE TPROD=:IPROD
C* AND (TTYPE='CI' OR TTYPE='I')
C* AND TTDTE>=:DATLO
C* AND TTDTE<=:DATHI
C* AND TWHS>='21'
C* AND TWHS<='59'
C*END-EXEC

Here is what I added (recent archives of the data) that apparently blew
up

C*EXEC SQL
C* SELECT SUM(TQTY) INTO :ISSUES
C* FROM BAKBP813/ITH WHERE TPROD=:IPROD
C* AND (TTYPE='CI' OR TTYPE='I')
C* AND TTDTE>=:DATLO
C* AND TTDTE<=:DATHI
C* AND TWHS>='21'
C* AND TWHS<='69'
C*END-EXEC

There will be cases where there is no TQTY activity where it is looking
to get a total of inventory consumed in the specified date range.

Al Macintyre

--
WOW! Homepage (http://www.wowway.com)

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options, visit:
http://lists.midrange.com/mailman/listinfo/midrange-l or email:
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment
to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options, visit:
http://lists.midrange.com/mailman/listinfo/midrange-l or email:
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment
to review the archives at http://archive.midrange.com/midrange-l.

--
WOW! Homepage (http://www.wowway.com)