ERROR while implementing if else cond in bteq scipts

Tools & Utilities
Enthusiast

ERROR while implementing if else cond in bteq scipts

i have written a bteq script and have an error while implementing below logic:

IF activity count is 0 then it should exec stmt1,stmt2,stmt3
IF activity count is 1 then it should exec stmt2,stmt3
but tried many logics to implement but failing at some point of time, in some case the stmt are bypassing .
can any one help me in correcting the logic

bteq << EOF 2>>$errorFile 1>>$errorFile
.logon -------------------------
select * from tbl_current_status where tbl_name='sample_employee';
.IF ACTIVITYCOUNT = 1 GOTO .USE
ELSE
stmt1 insert new record into current_status //stmt must execute
.LABEL USE
stmt2 insert into tbl_history by selecting recds from current_status

stmt3 .export data file=LOGS/LastTimeStamp.dat
.set recordmode off
select start_ts from tbl_load_status where tbl_name='sample_employee';
.export reset
.LOGOFF;
.QUIT;
EOF

___________LOGS__________

.IF ACTIVITYCOUNT = 1 THEN .GOTO USE
+---------+---------+---------+---------+---------+---------+---------+----
ELSE
insert new record into TABLE1

ELSE
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and the 'ELSE' keyword.
Statement# 1, Info =6
*** Total elapsed time was 1 second.

4 REPLIES 4
Teradata Employee

Re: ERROR while implementing if else cond in bteq scipts

delete the period before the use and the else

 

.IF ACTIVITYCOUNT = 1 GOTO .USE
ELSE

Teradata Employee

Re: ERROR while implementing if else cond in bteq scipts

Hi Chetan Gowda. You explained...

  • IF activity count is 0 then it should exec stmt1,stmt2,stmt3
  • IF activity count is 1 then it should exec stmt2,stmt3

In case the activity count could also be greater than 1, you might want to consider expanding the script's logic to cover that as well. Also be sure to add in the THEN keyword. You only omit the THEN keyword when you are constructing conditional blocks using use .ELSE, .ELSEIF and .ENDIF commands. For example...

.IF ACTIVITYCOUNT > 0 THEN .GOTO skip1
    stmt1
.LABEL skip1
.IF ACTIVITYCOUNT > 1 THEN .GOTO skip2
    stmt2
    stmt3
.LABEL skip2

The above is fairly clear to understand. But sometimes GOTOs do not result in the most elegant logic. Understanding that each THEN-GOTO is really identifying where to find a logical "else" path generally takes a non-intuitive, studied perspective. So where clarity and extensibility is a concern, consider using only IF blocks instead. This means drop the THEN keyword on the .IF and employ use of .ENDIF, .ELSEIF and .ELSE commands to create blocks. These commands allow you to create multi-instruction IF constructs known as "MI IFs". So another way to code your script could be...

.IF ACTIVITYCOUNT = 0
    stmt1
.ENDIF
.IF ACTIVITYCOUNT < 2
    stmt2
    stmt3
.ENDIF

You will need to be on at least BTEQ version 16.20.00.04 to have support for .ENDIF, .ELSE and .ELSEIF commands -- meaning support exists for identifying a block of multiple instructions to be made conditional. If your version of BTEQ does not recognize the MI IF construct commands then you have to use only GOTOs.

 

Another way using nested IFs...

.IF ACTIVITYCOUNT < 2
  .IF ACTIVITYCOUNT = 0
    stmt1
  .ENDIF
  stmt2
  stmt3
.ENDIF

Another way using GOTO -- just to show it is valid to use a mix of constructs...

.IF ACTIVITYCOUNT < 2
  .IF ACTIVITYCOUNT = 1 THEN .GOTO skip
    stmt1
  .LABEL skip
    stmt2
    stmt3
.ENDIF

Above indentations are not needed. I just did that for clarity.

 

Regarding ELSE use... Each BTEQ command starts with a period. When you omit the period, BTEQ treats the instruction as an SQL request. That is why the script resulted in a database 3706 failure. BTEQ does not validate SQL syntax. It lets the database do that. There are some cases where BTEQ will be lenient about older commands being used without a period. But all newer commands such as ELSE require the leading period. Its generally safest and clearer to always include a period so that only true SQL statements get sent to the database. Let's assume at some point in the future you do have a use case where you actually want to use an ELSE block. The syntax would look like this...

.IF conditions
    stmts
.ELSEIF conditions
    stmts
.ELSE
    stmts
.ENDIF

For completeness, I will also mention the availability of the BRANCHMSG command. If your BTEQ version supports use of MI IFs, then you can also use this command to make the informational messages produced for branching on MI IFs as well as GOTOs terse as opposed to verbose. This can substantially decrease the amount of informational messages BTEQ prints out to the stdout stream. By default, the control's state value is VERBOSE. To have BTEQ switch to terse branch messaging mode, change the setting to TERSE. For example...

.SET BRANCHMSG TERSE

Probably more than you wanted to know ! Just wanted to be complete in my response for others reading this. My main concern was the possible activity count fragility. Happy scripting.  :)

Enthusiast

Re: ERROR while implementing if else cond in bteq scipts

hi  SuzanneA 

Thanks For writing such a understandable and descriptive one.its help the new developers to learn and recode.

#the activity count in 4th line might take the value returned from stm1 , that was not intended.
hoping so that was a examples for the script
of the
.IF ACTIVITYCOUNT = 0 stmt1 .ENDIF .IF ACTIVITYCOUNT < 2 stmt2 stmt3 .ENDIF

 this might solve my problem of using without goto statements , so thanks for that !!

.IF ACTIVITYCOUNT < 2
  .IF ACTIVITYCOUNT = 0
    stmt1
  .ENDIF
  stmt2
  stmt3
.ENDIF

 thanks for spending more time on the above issues , it helps in learning more

Thanks

Chetan

Teradata Employee

Re: ERROR while implementing if else cond in bteq scipts

Hi Chetan. Thank you for catching that and saying something !! Yes, the activity count could change so that second example is not going to work for your case. Glad it was a helpful response and that you like the nested approach.  :)