I am really stumped at this, i know what i need to do but i cant quite get my head around this.
I have a file with a column that contains the months as 3 letters (Jan, Feb, Mar etc) and no numbers.
I am feeding in variables from a javascript drop down box but am having problems comparing the values.
DEFINE FILE RODNEY DEF_GEN3_TOTALYEAR = DECODE GEN3_TOTALYEAR ('JAN' '01' 'FEB' '02' 'MAR' '03' 'APR' '04' 'MAY' '05' 'JUN' '06' 'JUL' '07' 'AUG' '08' 'SEP' '09' 'OCT' '10' 'NOV' '11' 'DEC' '12'); END TABLE FILE RODNEY SUM CONDUCTED AS 'Tests Conducted' FAILED AS 'Tests Failed' DEF_GEN3_TOTALYEAR NOPRINT COMPUTE PCTFAILED/D12.2% = FAILED/CONDUCTED * 100; AS 'Fail Pct' BY GEN2_LOCATION AS '2' NOPRINT BY GEN3_LOCATION AS '3' BY GEN5_LOCATION AS '5' ACROSS CALENDARYEAR_CAPTION AS '' ACROSS GEN3_TOTALYEAR AS '' WHERE DEF_GEN3_TOTALYEAR GE '11' AND LE '12'; WHERE CALENDARYEAR_CAPTION EQ '&ADATEY';
This is a small snippet of my code
I am basically trying to use a DEFINE FILE to decode the column GEN3_TOTALYEAR into a number (01, 02, 03)
I then want to use DEF_GEN3_TOTALYEAR and select months in between the selected range
I keep getting a "zero lines in the report" returned with "No HTML Output"
I was able to narrow it down to the bolded line above that if i use the default JAN, FEB, MAR then my report runs fine. When i try to change it into numbers, then i am stumped!This message has been edited. Last edited by: Rodney Chan,
WebFOCUS 7.6 Windows, All Outputs
January 09, 2012, 03:18 PM
Francis Mariani
You state: "I have a file with a column that contains the months as 3 letters (Jan, Feb, Mar etc)"
WHERE DEF_GEN3_TOTALYEAR GE 11 WHERE DEF_GEN3_TOTALYEAR LE 12
If all else fails....
TNT works well with stumps.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
January 10, 2012, 03:52 AM
Wep5622
quote:
2) The WHERE syntax may be incorrect. It should be:
WHERE DEF_GEN3_TOTALYEAR GE '11' AND DEF_GEN3_TOTALYEAR LE '12';
You can also write that as below, which I find a bit more descriptive:
WHERE DEF_GEN3_TOTALYEAR FROM '11' TO '12';
Either notation tends to translate to a BETWEEN x AND y clause in SQL. Mind that these ranges, just like SQL BETWEEN, are inclusive.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
January 10, 2012, 12:19 PM
Rodney Chan
Hey
Sorry, the values are NOT mixed case, all upper case (force of habit to not caps lock everything)
I tried just adding the "A2" formatting and I got no html output
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
January 10, 2012, 02:58 PM
Rodney Chan
Hey Francis
I totally agree with you that it should be something simple which is why i am quite embarassed that i am even asking!
So i tried the code that you posted and i got the following
PAGE 1
GEN3_TOTALYEAR DEF_GEN3_TOTALYEAR JAN 01 FEB 02 MAR 03 APR 04 MAY 05 JUN 06 JUL 07 AUG 08 SEP 09 OCT 10 NOV 11 DEC 12 FlagOperatorTI FalgShowTI FlagFormatTI FlagCustom FlagSequence FlagMQY FlagActual
----------------------------------
While in a some what frustrated attempt at just hacking at the code and seeing if there was anything else that may be affecting the value. In my earlier (first)post i had provided a code snippet (i didnt think that the below line could cause any difference)
While hacking at the code I noticed that if i took out one of the ACROSS then the code seems to work? Not sure if this has anything to do with anything (i dont see how though)
DEFINE FILE RODNEY DEF_LICLASS_MEM/A20=DECODE LICENCECLASS_MEMBER( R1 'R1' R2 'R2' A 'A' O 'O' Z 'Z' C1 'C1' C2 'C2' ); DEF_GEN3_TOTALYEAR/A02 = DECODE GEN3_TOTALYEAR ( JAN '01' FEB '02' MAR '03' APR '04' MAY '05' JUN '06' JUL '07' AUG '08' SEP '09' OCT '10' NOV '11' DEC '12' ); END TABLE FILE RODNEY SUM CONDUCTED AS 'Tests Conducted' FAILED AS 'Tests Failed' DEF_GEN3_TOTALYEAR COMPUTE PCTFAILED/D12.2% = FAILED/CONDUCTED * 100; AS 'Fail Pct' BY GEN2_LOCATION AS '2' NOPRINT BY GEN3_LOCATION AS '3' BY GEN5_LOCATION AS '5' ACROSS CALENDARYEAR_CAPTION AS '' ACROSS GEN3_TOTALYEAR AS '' ACROSS DEF_LICLASS_MEM AS ' ' COLUMNS R1 AND R2 AND A AND O AND Z AND C1 AND C2 WHERE DEF_GEN3_TOTALYEAR GE '11' AND DEF_GEN3_TOTALYEAR LE '12'; -*WHERE GEN3_TOTALYEAR EQ 'DEC'; WHERE CALENDARYEAR_CAPTION EQ '2003'; END -EXIT
So if i took out the above line, the code seems to work (I get output but my report output then looks all wonky)
WebFOCUS 7.6 Windows, All Outputs
January 11, 2012, 03:02 AM
GamP
Just a shot in the dark here, but who knows, it may hit something. You're using C1 and C2. Be aware that these are reserved names and refer to the first and second column of the report. This may be the cause of the problem. What happens if you use some other id, like D1 or C_1?
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
January 11, 2012, 11:21 AM
Rodney Chan
quote:
C1 and C
Shot in the dark is still appreciated
I tried flipping those to other column names, still didnt seem to work....
Im really begining to wonder if the the two DECODE are conflicting, or the ACROSS is throwing it off or something.
WebFOCUS 7.6 Windows, All Outputs
January 11, 2012, 12:02 PM
Francis Mariani
If you change the two ACROSS statements to BY statements and it works, then I'd open a Tech Support case.
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
January 12, 2012, 06:17 AM
j.gross
quote:
ACROSS DEF_LICLASS_MEM AS ' ' COLUMNS R1 AND R2 AND A AND O AND Z AND C1 AND C2
Try quoting the values:
ACROSS DEF_LICLASS_MEM AS ' ' COLUMNS 'R1' AND 'R2' etc.
January 18, 2012, 08:03 PM
Rodney Chan
This is very strange to me, but after speaking to a colleague i stooped to doing the following
For the line
WHERE DEF_GEN3_TOTALYEAR GE '11' AND LE '12';
The values for 11 & 12 are actually fed in from a user input and are months, so they will only go from 1-12.
What I did, and for some reason it works is that I took the variable that was fed in and put it in a decode and used that in the WHERE statement
So something like this
-SET &FROM_MTH=DECODE &VAR1('1' 1 '2' 2.....);
For some reason, THIS seemed to work, im not sure why no other way worked but this seemed to work.
I would dig further, but we are in the middle of upgrading so hopefully in the next version we have this odd bug will be resolved, or when i have more time =)
Just wanted to post an update to this in case anyone stumbles across this.