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,

You state: "I have a file with a column that contains the months as 3 letters (Jan, Feb, Mar etc)"

but your code is: "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');"

Are the values in mixed case or upper case?

Put a format on DEF_GEN3_TOTALYEAR, e.g.

DEF_GEN3_TOTALYEAR/A2 = DECODE GEN3_TOTALYEAR ('JAN' '01' ... );

Without that, the assumed format is D12.2 (in which case your WHERE will fail - it would be comparing alpha value to numeric field)

It's a good practice to *always* supply an ELSE clause in you DECODE. When the black swan arrives, that will announce it.



Debugging: PCHOLD FORMAT HTML, then View Source and search for error messages (nearly all begin with "(FOC")

A couple of other issues:

1) In a DEFINE, if you don't specify a format, a numeric format is assumed. You should specify the format:

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');

2) The WHERE syntax may be incorrect. It should be:

WHERE DEF_GEN3_TOTALYEAR GE '11' AND DEF_GEN3_TOTALYEAR LE '12';

It would be far better to decode to an integer.

DEF_GEN3_TOTALYEAR/I2 = DECODE GEN3_TOTALYEAR ('JAN' 1 'FEB' 2 'MAR' 3 'APR' 4 'MAY' 5 'JUN' 6 'JUL' 7 'AUG' 8 'SEP' 9 'OCT' 10 'NOV' 11 'DEC' 12);

Then

WHERE DEF_GEN3_TOTALYEAR GE 11
WHERE DEF_GEN3_TOTALYEAR LE 12

If all else fails....

TNT works well with stumps.

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.

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

DEF_GEN3_TOTALYEAR/A2 = DECODE GEN3_TOTALYEAR ('JAN' '01'.....

....WHERE DEF_GEN3_TOTALYEAR GE '11' AND DEF_GEN3_TOTALYEAR LE '12';....

----------------------------------------

I tried the suggestion by Francis (cut and paste the code in the post) to try the DEFINE and the WHERE syntax (still no html output)

------------------------------------------

I tried a cut and paste from Waz and still not HTML output

Tried using integer

DEF_GEN3_TOTALYEAR/I2 = DECODE GEN3_TOTALYEAR ('JAN' 1 'FEB' 2 'MAR' 3 'APR' 4 'MAY' 5 'JUN' 6 'JUL' 7 'AUG' 8 'SEP' 9 'OCT' 10 'NOV' 11 'DEC' 12);

and

WHERE DEF_GEN3_TOTALYEAR GE 11
WHERE DEF_GEN3_TOTALYEAR LE 12

and tried

WHERE DEF_GEN3_TOTALYEAR FROM '11' TO '12';

Argh!

What I did notice that was a bit funny was that I tried changing my DECODE to just DECODE back to the original values

So

DEF_GEN3_TOTALYEAR/A3 = DECODE GEN3_TOTALYEAR ('JAN' 'JAN' 'FEB' 'FEB' 'MAR' 'MAR' 'APR' 'APR' 'MAY' 'MAY' 'JUN' 'JUN' 'JUL' 'JUL' 'AUG' 'AUG' 'SEP' 'SEP' 'OCT' 'OCT' 'NOV' 'NOV' 'DEC' 'DEC' );

and added the where statement

WHERE DEF_GEN3_TOTALYEAR EQ 'DEC';

And this didnt seem to work either!

GEN3_TOTALYEAR is the original column and has format A14, not sure if this would make a difference?

I know it has to be that WHERE clause and the decode because once i use this

WHERE GEN3_TOTALYEAR EQ 'DEC';

Then the report works fine....

Oh and the report doesnt give any errors or anything, it just says 0 lines returned

Well, you changed the definition to an integer, therefor this

quote:

and tried

WHERE DEF_GEN3_TOTALYEAR FROM '11' TO '12';

Argh!

is wrong.

It's got to be something simple.

Temporarily remove the WHERE statement.

Try something like this:

DEFINE FILE RODNEY
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
PRINT
GEN3_TOTALYEAR
DEF_GEN3_TOTALYEAR
WHERE RECORDLIMIT EQ 100
END
-EXIT

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)

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?

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.

If you change the two ACROSS statements to BY statements and it works, then I'd open a Tech Support case.

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.

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.

Thanks everyone for all the ideas!