DERIVED DATES - CALCULATING RESULT

[dee49]

I've read all the threads and am still having problems with a derived field to calculate a date. I thought that the statement below would do the trick, but am getting the infamous Missing right parenthesis message. I am wanting to find everyone who has a cmpny_seniority_dt = (25, 30, 35, 40, 45 or 50). This would be based on a variable date. I plopped in ReportsEffectiveDate to use.

Is this the appropriate statement?

CASE
WHEN TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 )) = 25 THEN 25
WHEN TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 )) = 30 THEN 30
WHEN TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 )) = 35 THEN 35
WHEN TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 )) = 40 THEN 40
WHEN TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 )) = 45 THEN 45
WHEN TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 )) = 50 THEN 50
ELSE X
END)


I've been working on this for several hours.

Thanks

 

[CharlesCook]

here is one problem:

END)
^
|
|

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[dee49]

I had played around with it so much didn't see the parenthesis. Now, however, I'm getting missing expression although I can't seem to find it.

 

[CharlesCook]

Put this in a derived field by it's self:

TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 ))

What do you get?

If the above works then replace ELSE X with ELSE 1 OR ELSE 'X'. What happens?

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[dee49]

I still get "missing expression" message. If I put CASE in front, I get "missing right parenthesis". Is there another way to go about this?

 

[CharlesCook]

So you put this in a Derived Field by it's self:

TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT" / 365.25 ))

And you get an error. What we need to do is find the error in this statement. I think you are missing a ) after SENIORITY_DT. Try this:

TRUNC(((<<"ReportsEffectiveDate", "TO_DATE("yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> - "EMPLOY"."CMPNY_SENIORITY_DT") / 365.25))

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting