Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Help with IF statement

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Help with IF statement
 Login/Join
 
Erney
Gold member
posted
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,


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
j.gross
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
DavSmith
Platinum Member
posted Hide Post
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.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Doug
Expert
posted Hide Post
Please refer to this post for a very similar post.

Are you guys (you and paulI)working together?




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
paulI
Gold member
posted Hide Post
No Doug we are not. Must be great minds think alike. See my post for what I ended up with. Thank Doug.


Prod: WF 7.7.05, BID, MRE, 7.7.06M Server, Windows 2008, RedHat, Oracle 11gR1, MS Office 2010
Test: I wish we had one!
 
Posts: 75 | Location: Wichita, KS | Registered: February 19, 2008Report This Post
Doug
Expert
posted Hide Post
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





   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Erney
Gold member
posted Hide Post
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) );


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Ramkumar - Webfous
Guru
posted Hide Post
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.


Thanks,

Ramkumar.
WebFOCUS/Tableau
Webfocus 8 / 7.7.02
Unix, Windows
HTML/PDF/EXCEL/AHTML/XML/HTML5
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Report This Post
Doug
Expert
posted Hide Post
quote:
In case of Numeric data, If you pass on FOC_NONE in where clause, You might end up with an error in certain DBs.
That should not happen as FOC_NONE in a WHERE is removed by the interpreter regardless of the format as it never hits the DB.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Help with IF statement

Copyright © 1996-2020 Information Builders