Archives of the TeradataForum

Message Posted: Fri, 13 Jun 2003 @ 16:08:45 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is my macro.

REPLACE MACRO circuit3sc
(
  PREFX CHAR(2)
 ,SVCCD CHAR(2)
 ,MODFR CHAR(2)
 ,SERNO INTEGER
 ,SUFFX CHAR(3)
 ,COASG CHAR(4)
 ,SPNPA SMALLINT
 ,CENTO SMALLINT
 ,LINNO SMALLINT
 ,EXTNO CHAR(5)
)
AS
(
  SELECT NI_CKTID
        ,SPL_PREFIX
        ,SPL_SVC_CODE
        ,SPL_MODIFIER
        ,SPL_SER_NO
        ,SPL_SUFFIX
        ,SPL_CO_ASGN
        ,SPL_NPA
        ,SPL_CENT_OFF
        ,SPL_EXT_NO
        ,SPL_LINE_NO
    FROM CIRCUIT3
   WHERE SPL_PREFIX   = :PREFX
     AND SPL_SVC_CODE = :SVCCD
     AND SPL_MODIFIER = :MODFR
     AND SPL_SER_NO   = :SERNO
     AND SPL_SUFFIX   = :SUFFX
     AND SPL_CO_ASGN  = :COASG
     AND SPL_NPA      = :SPNPA
     AND SPL_CENT_OFF = :CENTO
     AND SPL_LINE_NO  = :LINNO
     AND SPL_EXT_NO   = :EXTNO;
);

The PI is ( SPL_MODIFIER ,SPL_PREFIX ,SPL_CO_ASGN ,SPL_SER_NO , SPL_SUFFIX ,SPL_SVC_CODE ,SPL_CENT_OFF ,SPL_EXT_NO ,SPL_LINE_NO , SPL_NPA ), which is every field of the WHERE clause. But due to bad design, not all of fields will ever be populated.

On my V2R3.0.3.110 system I can exec the macro two different ways and it returns the row.

exec circuit3sc (' ','dz','ec',773970,' ','ati',,,,);

 *** Query completed. One row found. 11 columns returned.
 *** Total elapsed time was 1 second.

 NI_CKTID  SPL_PREFIX  SPL_SVC_CODE  SPL_MODIFIER  SPL_SER_NO
SPL_SUFFIX  SPL_C
O_ASGN  SPL_NPA  SPL_CENT_OFF  SPL_EXT_NO  SPL_LINE_NO

---------  ----------  ------------  ------------  ----------
----------  -----
------  -------  ------------  ----------  -----------

505148162              DZ            EC                773970

  ATI
              ?             ?  ?                     ?

exec circuit3sc (' ','dz','ec',773970,' ','ati',null,null,null,null);

 *** Query completed. One row found. 11 columns returned.

 *** Total elapsed time was 1 second.

 NI_CKTID  SPL_PREFIX  SPL_SVC_CODE  SPL_MODIFIER  SPL_SER_NO
SPL_SUFFIX  SPL_C
O_ASGN  SPL_NPA  SPL_CENT_OFF  SPL_EXT_NO  SPL_LINE_NO

---------  ----------  ------------  ------------  ----------
----------  -----
------  -------  ------------  ----------  -----------

505148162              DZ            EC                773970
  ATI
              ?             ?  ?                     ?

On my V2R4.1.3.30 system the row is not found.

exec circuit3sc (' ','ta','bc',982931,'004','ati',,,,);

 *** Query completed. No rows found.

 *** Total elapsed time was 1 second.

exec circuit3sc (' ','ta','bc',982931,'004','ati',null,null,null,null);

 *** Query completed. No rows found.
 *** Total elapsed time was 1 second.

Did something change in V2R4 with sending in nulls that it will no longer find the row or is this a bug? Is there an easy fix for this? Changing the table is not an option and changing all the macros and application code would be a great undertaking.

Thanks,

Shelly Conley

	<Prev	Next>		<<First	<Prev	Next>	Last>>