Archives of the TeradataForum

Message Posted: Wed, 26 Jul 2006 @ 13:29:20 GMT

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

Hi all,

We have some been taking some tentative steps with Tactical queries - and have taken on-board some of the comments already made through the forum - but are experiencing a less than ideal solution to retrieving data back from Macros.

In the examples we have so far we've had to resort to Global Temporary tables (GTT) in order to get good performance on the 'Fact' table. Fine, no problem there. However, when the call is made from Java (using JDBC) we get three result sets back from the Macro : one to indicate that a delete has been made to the GTT, one to show that an Insert has been made to the GTT and the third provides the actual result set - the bit we want.

Our programmers - bless them - have resolved this by looping through the three result sets back until we get to the actual business data.

I just feel that this shouldn't be necessary, given that when you execute the same Macro through Queryman you only get the business data back.

Would someone - and I suspect Travelocity might be able to easily answer this - to take a quick look and advise if we're doing it all wrong ?

Is there a better method for navigating to the result set from the Select or is this the correct coding ?

A) Here's the Teradata Macro definition

     REPLACE MACRO SHAREOFWALLET_M01 (CUS_ID1 DECIMAL(10), LOB_ID CHAR(1), MYCT BYTEINT)
     AS
     (

     DEL FROM DEVG188AHE.CUST_XREF_GTT ALL;

     INS INTO DEVG188AHE.CUST_XREF_GTT
     (PRIM_INDX,COUNTRY_CD, ACCT_NR, CUS_ID)
     SEL (HASHBUCKET(HASHROW(CURRENT_TIMESTAMP)) MOD 31),
     LAC_LEGACY_COU_CD, LAC_LEGACY_ACCT_NR, CUS_ID FROM
     DEVLEGACYXREF_V.ACCNTXREF_V01
     WHERE CUS_ID = :CUS_ID1 AND LAC_LEGACY_ACCT_NR <> ' ' AND CUS_ID IS NOT
     NULL;

     SEL  AX1.CUS_ID,
        MYLOB.LOB_ID,
        'EUR' AS CUY_ID,
        SUM(ACC_NET_REV_AM) AS TOT_NETREV_AM_EU

     FROM  DEVNAD_V.CUSTOMERTRADE_V01 CT1
     INNER JOIN
     ( SEL MIN_YRWK, MAX_YRWK
     FROM  DEVCE_V.DTEPERIODS_V01
     WHERE PERIOD_TYPE_CD = 'WK' AND  PERIODS_DIFF = 13) AS MYPERIOD
     ON ((DTE_REPORTING_YR*100) + DTE_REPORTING_WK) BETWEEN MYPERIOD.MIN_YRWK
     AND MYPERIOD.MAX_YRWK
     INNER JOIN   DEVG188AHE.CUST_XREF_GTT AX1
     ON AX1.COUNTRY_CD = CT1.COU_ID
     AND AX1.ACCT_NR=CT1.ACC_NR
     INNER JOIN
     (SELECT  SUBSTR(XVC_SRCE_1_CD,1,1) AS LOB_ID,
           XVC_CONV_1_CD AS DIV_ID,
           XVC_CONV_2_CD AS PRD_ID
     FROM  DEVCMNSALES_P.GENERICXREF_V01
     WHERE XVR_CONV_SET_NR = '103' AND LOB_ID = :LOB_ID OR 1=:MYCT
     GROUP BY 1,2,3) AS MYLOB
     ON MYLOB.DIV_ID = CT1.DIV_DIVISION_CD AND MYLOB.PRD_ID =
     CT1.PRD_PRODUCT_CD
     GROUP BY 1,2,3
     ORDER BY 1,2,3
     ;
     );

B) Here's the Sample Java Code

     String strMacro = "EXEC HAREOFWALLETTST_M01(?,,1);";

     // Create statement
     statement = connection.prepareStatement(strMacro);
     System.out.println("\nStatement created...");


     // Execute the MACRO
     System.out.println("Macro= "+strMacro);
     statement.setLong(1, customerId);

     boolean bResultSetFound = statement.execute();
     int updateCount = statement.getUpdateCount();
     System.out.println("MACRO executed...");

     int attemptCtr = 0; //used only for counting purpose
     while (bResultSetFound || (updateCount != -1)) {
           System.out.println("Attempt "+(++attemptCtr)+". ResultSet found= "
     +bResultSetFound+". Update Count= "+updateCount);
           if(bResultSetFound) {
                 rs = statement.getResultSet();
                 while(rs.next()) {
                       StringBuffer data = new StringBuffer();
                       data.append(" CUS_ID=").append(rs.getLong("CUS_ID"));
                       data.append("
     LOB_ID=").append(rs.getString("LOB_ID"));
                       data.append("
     CUY_ID=").append(rs.getString("CUY_ID"));
                       data.append(" TOT_NETREV_AM_EU=").append(rs.getDouble(
     "TOT_NETREV_AM_EU"));
                       System.out.println("data=["+data.toString()+"]");
                 }
           }
           bResultSetFound = statement.getMoreResults();
           updateCount = statement.getUpdateCount();
     }

thanks and free 'virtual beers' in advance

Dave Clough
Database Designer
Express ICS

www.tnt.com

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