[SOLVED]If then else statement wrapped around a where statement?
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
September 21, 2010, 01:51 PM
fatboyjim
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
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
September 21, 2010, 03:08 PM
Erney
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
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
September 21, 2010, 03:30 PM
njsden
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
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.
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
September 21, 2010, 04:58 PM
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 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
September 22, 2010, 11:10 AM
ABT
...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 '.
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
September 23, 2010, 11:40 AM
Erney
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
