Decode statement limitation

[taketime]

Is there a limitation to the number of operators you can have in a decode statement? ie I want to calculate annual 401k eligible earnings - so Gross pay less 24 earnings codes. Is 24 too many. I'm returning an immediate blank report. My derrived field has 0 for all null values. I remove that particular field and the report runs... any help?

 

[CharlesCook]

I have run into this also. I think the problem is not the decode but the total length of the SQL command. Try using a single letter alias for each table. You can also test this by removing other sql derived fields.

This could also be a data issue. If you tried to do math and the data had char values in it this will happen (data exception).

Try the SQL length issue first.

Hope this helps...

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[taketime]

Charles - thanks for the suggestions. I went down the mathmatical road regarding non numericals but will look at using aliases.

Do you happen to know the character limitation - binary something?

 

[CharlesCook]

i have never had the time to come up with the number. i think oracle will say something like "No practical limit" (ie it depends).

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[dlblack]

I have to agree with Charles Cooke. We are using ReportSmith 4.2.2, Oracle, and ADP 4.0 SR4. We use 50 earnings codes (a mixture of 2 digit numeric and single character alpha identifiers), in our decode for the derived field of pensionable earnings without any problem. I go against the check view earnings and YTD Accumulator views. We have less than 300 salaried employees. Don't know if this is of any help, but I can tell you it works.

David Black, CQA
Teepak, LLC