BTEQ - conditional branching

Database
Enthusiast

BTEQ - conditional branching

I have BTEQ (SQL) which access Teradata tables. I would like to conditional bypass portion of the BTEQ based on prefix of account number

SUBSTR(SRC_ACCT_NB,1,2) = ('87')         -> these I want to bypass

SUBSTR(SRC_ACCT_NB,1,2) = ('48')         --> continue to process BTEQ

 

The Teradata table will have both '87' and '48'

 

I have tried:

                                                 
  SELECT COUNT(*)                                
  FROM ZSNT20RV.ZSNV203_LA_ACCT                  
   WHERE SUBSTR(SRC_ACCT_NB,1,2) = ('87')        
   HAVING COUNT(*) > 0;                          
                                                 
 .IF ACTIVITYCOUNT = 0 THEN .GOTO CONTINUE207    
 .IF ACTIVITYCOUNT = 1 THEN .GOTO SKIPCLEANUP    
                                                 
                                                 
 .LABEL CONTINUE207                              
 SELECT COUNT(*)                                 
 FROM ZSNV207_SRC_ACCT_DETAIL                    
 WHERE SUBSTR(SRC_ACCT_NB,1,2) IN (48,83,84)     
 HAVING COUNT(*) > 1;           

 

                       
.LABEL SKIPCLEANUP     
.LOGOFF                
.EXIT 0                

 

When this is executed

results -  find '87'

 

SELECT COUNT(*)   FROM ZSNT20RV.ZSNV203_LA_ACCT    WHERE SUBSTR(SRC_A
                                                      Count(*)      
                                                   -----------      
                                                          1918      

  .IF ACTIVITYCOUNT = 1 THEN .GOTO SKIPCLEANUP     
 .GOTO SKIPCLEANUP          

 

However, it doesn't find '48' & process these accounts --> it doesn't find & execute this

              --> IF ACTIVITYCOUNT = 0 THEN .GOTO CONTINUE207   

rather skips rest of BTEQ

 

            

Note, this is limited portion of BTEQ. I just want to see how to bypass '87' & then process '48' for rest of BTEQ.

 

Any suggestions? Thank you                             

13 REPLIES
Senior Apprentice

Re: BTEQ - conditional branching

Hi,

 

I think that BTEQ is doing exactly what you've told it to, I think you'll have to change your logic.

 

In your example, what you've told BTEQ is:

"if you find ANY '87's then bypass the rest of my code"

 

Which is effectively the same as saying

"if you find ANY '87's then logoff"

 

Is that what you want?

 

If you have different processing requirements for '87''s and '48's then why not put that selection criteria (the SUBSTR function) into the WHERE clause (or a CASE expression) for the appropriate SQL statement(s)?

For instance if you want to:

- add 1 to ColumnX for '87's

- add 2 to ColumnX for '48's

 

 then you could code it as:

UPDATE table
  SET ColumnX = CASE
                WHEN SUBSTR(SRC_ACCT_NB,1,2) = '87' THEN ColumnX+1
                WHEN SUBSTR(SRC_ACCT_NB,1,2) = '48' THEN ColumnX+2
                END
WHERE SUBSTR(SRC_ACCT_NB,1,2) = '87'
  OR SUBSTR(SRC_ACCT_NB,1,2) = '48';

Does this help?

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ - conditional branching

Thanks for your feedback.

What I want to happen is for substr(src_acct_nb,1,2) = '87' bypass current logic in BTEQ / SQL that access Teradata tables.

Where as for substr(src_acct_nb,1,2) in (48, 83, 84) then process using logic in BTEQ/SQL that access Teradata tables.

I would like to set up CASE statement with .GOTO Label when substr(src_acct_nb,1,2) = '87' 

I don't think CASE statements can have .GOTO Label

The situation is the Teradata tables have substr(src_acct_nb,1,2) = '87'  as well as '48', '83' & '84'

However the process within the BTEQ we don't want substr(src_acct_nb,1,2) = '87'  to process.

 

Any other suggestions?

 

Senior Apprentice

Re: BTEQ - conditional branching

Hi,

 

I still think you  you need ot be very specific about what logic you want executed.

 

For example, assume that you have the following data:

src_acct_nb
871234
481453
837654
849876

Given the data above, do you want ANY processing done?

If the answer is 'NO' (because of the '87's) then use the following:

SELECT COUNT(*)
FROM table
WHERE SUBSTR(src_acct_nb,1,2) = '87'
HAVING COUNT(*)>0;
.if activtitycount > 0 then .quit 0;

This will cause BTEQ to terminate and NO other processing will be done.

 

If the answer to the question is 'YES' (because of the presence of rows other than the '87's) then you'll need different logic.

 

When you say "However the process within the BTEQ we don't want substr(src_acct_nb,1,2) = '87'  to process." do you mean just the rows with '87' should not be processed or NO ROWS should be processed?

 

Could you give some examples of data and some SQL statements that should or should not be processed?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: BTEQ - conditional branching

I should also have said that you cannot place a 'goto' logic in the CASE expression itself.

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ - conditional branching

On the table there are SUBSTR(SRC_ACCT_NB,1,2) – ‘87’  as well as SUBSTR(SRC_ACCT_NB,1,2) – ‘48', '83' '84'

For '87' the logic needs to be bypassed

for '48' '83' & '84' - lotic need to be done 

 

Any further suggestions?

Enthusiast

Re: BTEQ - conditional branching

Logic (not lotic)

Senior Apprentice

Re: BTEQ - conditional branching

Hi,

 

You've said the following:

"On the table there are SUBSTR(SRC_ACCT_NB,1,2) – ‘87’  as well as SUBSTR(SRC_ACCT_NB,1,2) – ‘48', '83' '84'

For '87' the logic needs to be bypassed

for '48' '83' & '84' - lotic need to be done"

 

When you say "For '87' the logic needs to be bypassed" do you mean bypass the logic for all rows (the '87' rows as well as '48',' 83' etc.), or do you mean bypass the logic for only the '87' rows?

 

These are two different requirements and will (typically) be coded two different ways. Either is possible but I'm still not sure which one you want.

 

Once you can tell me which one you want I can provide a suggestion for you.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: BTEQ - conditional branching

Only bypass for ALL the '87' rows, Yet, still process for rows with '48', '83' & '84'

Senior Apprentice

Re: BTEQ - conditional branching

Hi,

Thanks for the clarrification.

In this case you do not need 'conditional branching', you just code the appropriate select criteria.

Assume that what you need to do is to add 1 to the value in Column_X for the '43', '83' and '84' accounts.

You can use:

UPDATE table
  SET column_x = column_x + 1
WHERE SUBSTR(SRC_ACCT_NB,1,2) IN ('43','83','84');

The '87' rows will not be changed at all because they fail the WHERE clause.

Does that solve it for you?

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com