Hi All,

I am having an issue where I am trying to look at a variable and identify if it is not filled and is blank. If it is blank I would like to pass a section of the code as shown below.

SQL DB2 SET ISOLATION NC

-IF &TEAM_UPD EQ '' THEN GOTO NEXT_1 ELSE GOTO TEAM_START; 
-TEAM_START

ENGINE DB2 SET DEFAULT_CONNECTION Y
SQL DB2
UPDATE WFUSRFLE/BUY_TBL_GL SET TEAM = '&TEAM_UPD'
WHERE UNQID = &UNQID_UPD;
END

-NEXT_1
-IF &BNAME_UPD EQ '' THEN GOTO NEXT_2 ELSE GOTO BNAME_START; 

-BNAME_START
ENGINE DB2 SET DEFAULT_CONNECTION Y
SQL DB2
UPDATE WFUSRFLE/BUY_TBL_GL SET BNAME = '&BNAME_UPD'
WHERE UNQID = &UNQID_UPD;
END

This works with the exception of when I enter a 0 in &TEAM_UPD or &BNAME_UPD as it skips those update statements even though I want to update the field to 0. The reason this is happening is because the -IF statement is returning a true value for the ''=0 and skipping the update statement.

I figure I need to use a Missing/Null/Blank command instead of the '' as part of my IF statement but none of those are working and I am at a loss for how to define that correctly in the dialogue manager.

Any help is appreciated, thanks in advance for taking a look.

-Luke

This message has been edited. Last edited by: Luke Forster,

Very good question - why is a zero being treated as a blank?

-SET &TEAM_UPD = '0';
-SET &BNAME_UPD = '0';

Why are these being treated as a blank, they look like multi-character strings to me?

-SET &TEAM_UPD = ' 0 ';
-SET &BNAME_UPD = ' 0 ';

-SET &TEAM_UPD = ' 0. ';
-SET &BNAME_UPD = ' 0. ';

-SET &TEAM_UPD = ' 0- ';
-SET &BNAME_UPD = ' 0- ';

-SET &TEAM_UPD = ' 0+ ';
-SET &BNAME_UPD = ' 0+ ';

Some code:

-SET &TEAM_UPD = '0';
-SET &BNAME_UPD = '0';

-IF &TEAM_UPD EQ '' THEN GOTO NEXT_1 ELSE GOTO TEAM_START;

-TEAM_START
-TYPE INSIDE TEAM_START / &TEAM_UPD / &BNAME_UPD

-NEXT_1
-TYPE INSIDE NEXT_1 / &TEAM_UPD / &BNAME_UPD

-IF &BNAME_UPD EQ '' THEN GOTO NEXT_2 ELSE GOTO BNAME_START;

-BNAME_START
-TYPE INSIDE BNAME_START / &TEAM_UPD / &BNAME_UPD

-NEXT_2
-TYPE INSIDE NEXT_2 / &TEAM_UPD / &BNAME_UPD

IBI had to find a use for ASIS:

  
-SET &TEAM_UPD  = '0';
-SET &BNAME_UPD = ' 0 ';
-IF &BNAME_UPD EQ ASIS(&TEAM_UPD) GOTO ONE;
-TYPE TEAM_UPD: &TEAM_UPD EQ BNAME_UPD: &BNAME_UPD NOT TRUE
-QUIT
-ONE
-TYPE TEAM_UPD: &TEAM_UPD EQ BNAME_UPD: &BNAME_UPD TRUE
-EXIT

Tom, thanks for the reminder! I've used ASIS before but forgot all about it! Pretty silly but valuable function.

That and .EVAL are needed now; YES, silly!!!

It's almost as if a zero evaluates to "false" and two single quotes also evaluates to "false"... two false boolean values tested for equality gives a "true".

Ya learn something new every day, I guess...

I added the ASIS function to the update procedure and it is now working. Thanks so much for your help, I would never have been able to figure that out by myself.

I will mark this as solved.

-Luke

 SQL DB2 SET ISOLATION NC

-IF ASIS(&TEAM_UPD) EQ '' THEN GOTO NEXT_1 ELSE GOTO TEAM_START; 
-TEAM_START

ENGINE DB2 SET DEFAULT_CONNECTION Y
SQL DB2
UPDATE WFUSRFLE/BUY_TBL_GL SET TEAM = '&TEAM_UPD'
WHERE UNQID = &UNQID_UPD;
END

-NEXT_1
-IF ASIS(&BNAME_UPD) EQ '' THEN GOTO NEXT_2 ELSE GOTO BNAME_START; 

-BNAME_START
ENGINE DB2 SET DEFAULT_CONNECTION Y
SQL DB2
UPDATE WFUSRFLE/BUY_TBL_GL SET BNAME = '&BNAME_UPD'
WHERE UNQID = &UNQID_UPD;
END