[SOLVED] Help with IF statement

February 04, 2011, 11:06 AM

I have the following IF statment in my webfocus HTML Launch page that calls a report - The report itself is working correctly but the if statement isnt quite right.

When this statement drops down into the where statement in LAB1 it seems like its pulling back everything that has a notice_type with it and ignoring the AND statement. Im just looking for all notice_types within a License_no, but like I said it pulls back every entry that has a Notice_type in it.

Now one question i have is the Notice_type on the html launch page actually has a "ALL" value in the dropdown list. Will this affect the report someway and make it bypass the AND statement.

-IF &LicenseNumber NE '' THEN GOTO LAB1;
-ELSE IF &StartDate NE '' THEN GOTO LAB2;
-ELSE GOTO LAB3;
-LAB1
WHERE (Notice_Type EQ '&NoticeType' AND License_No EQ '&LicenseNumber')
-GOTO ENDLAB;
-LAB2
WHERE ( Processed_Date GE DT(&StartDate) AND Processed_Date LE DT(&EndDate) AND Notice_Type EQ '&NoticeType')
-GOTO ENDLAB;
-LAB3
WHERE ( Notice_Type EQ '&NoticeType')
-ENDLAB

This message has been edited. Last edited by: Erney,

February 04, 2011, 11:38 AM

j.gross

I gather the Where is being inserted in a TABLE request (as opposed to SQL SELECT), so I suggest breaking it into two Where phrases. In fact, you can then factor out the Notice_type test, as below.

You also have semicolons where they don't belong, and are missing ones that are called for.
-GOTO does not take a semicolon;
-IF does;
and WHERE allows (and deserves) it

WHERE (Notice_Type EQ '&NoticeType');

-IF &LicenseNumber NE '' THEN GOTO LAB1
-ELSE IF &StartDate NE '' THEN GOTO LAB2
-ELSE GOTO LAB3;

-LAB1
WHERE (License_No EQ '&LicenseNumber');
-GOTO ENDLAB

-LAB2
WHERE ( Processed_Date FROM DT(&StartDate) TO DT(&EndDate) );
-GOTO ENDLAB

-LAB3
-ENDLAB

February 04, 2011, 01:49 PM

DavSmith

J, good stuff.

I'll also add that even if it is an SQL select, the use of FOC_NONE in the amper variable works. Just make sure the construct is on a line by itself (note: include the connecting AND/OR if needed). Example:

-SET &SEL1='FOC_NONE';
-SET &SEL2='{value}';
select *
from {file}
where 
     {field1} eq '&SEL1' and 
     {field2} eq '&SEL2'}
;

The WHERE line with &SEL1 will be ignored, meaning get ALL values for {field1}.

We can also use FOC_NONE to write somewhat more compact, cleaner code. Using your eample J, we can remove the GOTO's and LABELS.

-SET &LicenseNumber  =IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate      =IF &StartDate     EQ '' THEN FOC_NONE ELSE &StartDate;
WHERE (Notice_Type EQ '&NoticeType');
WHERE (License_No EQ '&LicenseNumber');
WHERE ( Processed_Date FROM DT(&StartDate) TO DT(&EndDate) );

Here's a very simple example using the CAR file. If the &SELECT variable is 1 then I want all Jaguar records and I don't care about the country. If &SELECT is 2, then I want all cars from Germany, I don't care about the car.

-SET &SELECT=1;
-SET &CAR     =DECODE &SELECT(1 'JAGUAR'    ELSE 'FOC_NONE');
-SET &COUNTRY =DECODE &SELECT(2 'W GERMANY' ELSE 'FOC_NONE');
TABLE FILE CAR
PRINT CAR COUNTRY
WHERE CAR     EQ '&CAR';
WHERE COUNTRY EQ '&COUNTRY';
END

I could use GOTO and LABLEs like this example:

-SET &SELECT=2;
-SET &CAR    =IF &SELECT EQ 1 THEN 'JAGUAR'  ELSE '';
-SET &COUNTRY=IF &SELECT EQ 2 THEN 'W GERMANY' ELSE '';
TABLE FILE CAR
PRINT CAR COUNTRY
-IF &COUNTRY NE '' GOTO COUNTRY;
WHERE CAR     EQ '&CAR';
-GOTO ENDRPT
-COUNTRY
WHERE COUNTRY   EQ '&COUNTRY';
-ENDRPT
END

To me, all the GOTO's have always been messy.

February 04, 2011, 02:12 PM

Doug

Please refer to this post for a very similar post.

Are you guys (you and paulI)working together?

February 04, 2011, 04:24 PM

paulI

No Doug we are not. Must be great minds think alike. See my post for what I ended up with. Thank Doug.

February 04, 2011, 09:50 PM

Doug

quote:
great minds think alike

Yeap. And the more there are here, on Focal Point, the better off we all are.

"No one can do everything. But, if everyone does something then everything gets done." ~ Millicent

February 07, 2011, 10:14 AM

Erney

I actually ended up using the code below, and it worked like a charm. Thank you.

-SET &LicenseNumber  =IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate      =IF &StartDate     EQ '' THEN FOC_NONE ELSE &StartDate;
WHERE (Notice_Type EQ '&NoticeType');
WHERE (License_No EQ '&LicenseNumber');
WHERE ( Processed_Date FROM DT(&StartDate) TO DT(&EndDate) );

February 08, 2011, 08:47 AM

Ramkumar - Webfous

Hi Erney...

 IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber

I hope It should be other way around... In case of Numeric data, If you pass on FOC_NONE in where clause, You might end up with an error in certain DBs.

Also, A column which is precision less than LENGTH('FOC_NONE') will also throw you an error.