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]If then else statement wrapped around a where statement?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]If then else statement wrapped around a where statement?
 Login/Join
 
Gold member
posted
I am trying to do a If statment around a where statement and could use some help.

 If &startdate eq '' then
where (Tax_period EQ '&Tax_period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_period.' 
else
WHERE ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))


Im not really sure how to do this, can anyone lend a hand?

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


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Platinum Member
posted Hide Post
Hi,

Try
  
IF &startdate EQ '' THEN GOTO LAB1;
ELSE GOTO LAB2;
-LAB1
where (Tax_period EQ '&Tax_period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_period.'
-GOTO ENDLAB;
-LAB2
WHERE ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))
-ENDLAB


See if that is what you are looking for.

Best Regards,

Jimmy Pang


DEV: WF 7.6.10
TEST: WF 7.6.10
PROD: WF 7.6.10
MRE: WF 7.6.4
OS/Platform: Windows
Dev Studio: WF 7.7
Output: HTML, EXCEL, PDF, GRAPH, LOTUS, CSV
 
Posts: 117 | Location: Toronto, Ontario, Canada | Registered: February 29, 2008Report This Post
Expert
posted Hide Post
The IF requires a dash, e.g.
-IF &startdate EQ '' THEN GOTO LAB1;


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
This is what i got so far, I must have some syntax wrong because its bombing. I decided to change the if statement around to check tax_period to see if its blank.

-IF &Tax_period EQ MISSING THEN GOTO LAB1;
ELSE GOTO LAB2;
-LAB1
WHERE ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))
GOTO ENDLAB;
-LAB2
WHERE (Tax_period EQ '&Tax_period')
-ENDLAB  


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Platinum Member
posted Hide Post
Hi,

The line below needs dash too.

  
-GOTO ENDLAB;


Best Regards,

Jimmy Pang


DEV: WF 7.6.10
TEST: WF 7.6.10
PROD: WF 7.6.10
MRE: WF 7.6.4
OS/Platform: Windows
Dev Studio: WF 7.7
Output: HTML, EXCEL, PDF, GRAPH, LOTUS, CSV
 
Posts: 117 | Location: Toronto, Ontario, Canada | Registered: February 29, 2008Report This Post
Gold member
posted Hide Post
I love these errors that come back from webfocus.. Lol I get

Error parsing report.
>IF<
-IF &Tax_period EQ ' ' THEN GOTO LAB1;

What does this mean?

I pasted the whole report code below.

TABLE FILE SQLOUT
PRINT 
     Processed_Date/HMDYY AS 'Processed,Date'
     Notice_date/HMDYY AS 'Notice,Date'
     Bus_name AS 'Business,Name'
     Notice_number AS 'Notice,Number'
     Tax_Due
BY License_no AS 'License,Number'
BY Tax_period AS 'Tax,Period'
     
ON License_no SUBTOTAL
     Tax_Due MULTILINES AS ''
HEADING
"Assessments Notice"
" "
-IF &Tax_period EQ ' ' THEN GOTO LAB1;
ELSE GOTO LAB2;
-LAB1
WHERE ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))
-GOTO ENDLAB;
-LAB2
WHERE (Tax_period EQ '&Tax_period')
-ENDLAB
ON TABLE SET PAGE-NUM OFF 
ON TABLE COLUMN-TOTAL AS 'TOTAL' Tax_Due
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N5,
     JUSTIFY=LEFT,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N5,
     JUSTIFY=LEFT,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=1,
     JUSTIFY=CENTER,
$
TYPE=HEADING,
     LINE=2,
     JUSTIFY=CENTER,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N5,
     WRAP=1.180556,
$
ENDSTYLE
END
  


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Virtuoso
posted Hide Post
quote:
-IF &Tax_period EQ MISSING THEN GOTO LAB1;

I am not so sure that MISSING can be used withing the context of Dialog Manager.

I would suggest that you always initialize your variables with "something" so you can easily determine whether or not a value was provided by the user. What about this slimmed-down piece:

-DEFAULTS &startdate  = '19000101';
-DEFAULTS &Tax_period = 'NONE';

-IF &startdate EQ '19000101' THEN GOTO :FTAXPRD;
WHERE ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))
-GOTO -:FEND;
-:FTAXPRD
WHERE (Tax_period EQ '&Tax_period')
-:FEND



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
Your syntax is wrong. If you need to continue a Dialogue Manager command on another line, the first character must be a dash:

-IF &Tax_period EQ '' THEN GOTO LAB1
- ELSE GOTO LAB2;


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
quote:

-IF &Tax_period EQ ' ' THEN GOTO LAB1;
ELSE GOTO LAB2;


That may be the reason of the error. You started an -IF command (which belongs to the realm of Dialog Manager) and in the next line you put an ELSE keyword which belong to [Web]FOCUS language syntax.

What WebFOCUS is actually "seeing" after Dialog Manager is resolved is just an orphan ELSE keyword not belonging to any IF statement.

Try the piece of code I posted previously to see if it makes a difference.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Guru
posted Hide Post
quote:
If &startdate eq '' then
where (Tax_period EQ '&Tax_period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_period.'
else
WHERE ( Processed_Date GE DT(&StartDate) AND Processed_Date LE DT(&EndDate))


Change the WHERE clause to:

WHERE (Tax_period EQ '&Tax_period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_period.' AND &startdate eq '')  OR
     ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))  


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
Try using DM commands
-SET &TEST1=IF&startdate eq '' then
- 'where (Tax_period EQ '&Tax_period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_period.''
- else
'WHERE ( Processed_Date GE DT(&StartDate) AND Processed_Date LE DT(&EndDate';

TABLE FILE WHATEVER
PRINT *
&TEST1
END

You do need to make sure that you use the correct number of '.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
...and parens. ;-)

'WHERE ( Processed_Date GE DT(&StartDate) AND Processed_Date LE DT(&EndDate))';


quote:
Originally posted by RSquared:
Try using DM commands
-SET &TEST1=IF&startdate eq '' then
- 'where (Tax_period EQ '&Tax_period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_period.''
- else
'WHERE ( Processed_Date GE DT(&StartDate) AND Processed_Date LE DT(&EndDate';

TABLE FILE WHATEVER
PRINT *
&TEST1
END

You do need to make sure that you use the correct number of '.


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Guru
posted Hide Post
ABT, you are correct. However, I was just trying to pass along a concept that I have used that might help others with this type of problem. I do have a slight problem with mis-typing and incomplete copying. so thanks.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Gold member
posted Hide Post
This worked, thanks all.

 -IF &Tax_period EQ '' THEN GOTO LAB1;
-ELSE GOTO LAB2;
-LAB1
WHERE ( Processed_Date GE DT(&StartDate) AND  Processed_Date LE DT(&EndDate))
-GOTO ENDLAB;
-LAB2
WHERE (Tax_period EQ '&Tax_period')
-ENDLAB 


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report 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]If then else statement wrapped around a where statement?

Copyright © 1996-2020 Information Builders