AW: SQL Indicator?

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

an SQL indicator is a variable defined as 5I0 which must be specified
immediately after the host variable that must hold the result.
An indicator variable can/must be declared and specified for each column
that may contain NULL values.
If a NULL value is returned the host variable will not be changed, but the
indicator variable will be set to -1. 
If no NULL value is returned the indicator variable will be set to *Zeros

Exec SQL   Select Sum(TQTY), Count(ColX)
              Into :Issues :IndIssues, :MyCount :IndMyCount
              From ...
              Where ...;
If IndIssues < *Zeros;
   //A NULL value is returned in Issues
Else;
   //A value is returned in Issues
EndIf;

If IndMyCount < *Zeros
   // A NULL value is returned in MyCount
Else;
   // A value is returned in MyCount
EndIf;

Just FYI:
When using aggregate functions such as COUNT, SUM, AVG NULL values will be
ignored.
That means if you count 5 rows of the column MyCol with the following
Values: 1, 1, NULL, NULL, NULL
Count(MyCol) = 2
Avg(MyCol) = 1
Count(*) = 5 (even if all columns contain a NULL value)

When converting a NULL value into a default value, all rows are considered:
Count(Coalesce(MyCol, 0)) = 5
Avg(Coalesce(MyCol, 0))   = 0,4

Coalesce(Sum(Count(MyCol), 0) = 2
Coalesce(Avg(Count(MyCol), 0) = 1

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
macwheel99@xxxxxxxxxx
Gesendet: Saturday, 24. January 2009 01:40
An: List M-L; KE E Al Macintyre
Betreff: 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)