SQL Add Fiscal Year Column

I using the script below and would like to add a FY Column based on C_COMPLETE_REQUEST_RECVD_DT.
The FYs are
FY2002 Between 10/1/2001 and 9/30/2002
FY2015 Between  10/1/2014 and 9/30/2015
FY2016 Between  10/1/2015 and 9/30/2016
FY2017 Between  10/1/2016 and 9/30/2017
FY2018 Between  10/1/2017 and 9/30/2018
FY2019 Between  10/1/2018 and 9/30/2019

Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"


From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Is C_COMPLETE_REQUEST_RECVD_DT a field?

Which table is it in?
0
shieldscoAuthor Commented:
Yes
0
Bill PrewCommented:
Try adding this for the fiscal year field:

SELECT CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() ELSE YEAR()+1 END AS Fiscal_Year

Open in new window


»bp
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

shieldscoAuthor Commented:
java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"
, CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() ELSE YEAR()+1 END AS Fiscal_Year



From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
Bill PrewCommented:
SELECT CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR(C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year

Open in new window


»bp
0
shieldscoAuthor Commented:
Bill java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier
0
Bill PrewCommented:
See my last comment, I forgot to pass the field to YEAR(), sorry...


»bp
0
shieldscoAuthor Commented:
java.sql.SQLException: ORA-03001: unimplemented feature

,CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR()(C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR()(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year
0
Bill PrewCommented:
You have a typo, please check my prior post and compare to yours, you have extra parens after the YEAR function.


»bp
0
shieldscoAuthor Commented:
java.sql.SQLException: ORA-03001: unimplemented feature



,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year
0
Bill PrewCommented:
You still have a typo...

,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year


»bp
0
shieldscoAuthor Commented:
java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier
,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year
0
Bill PrewCommented:
Well, YEAR() is a valid Oracle SQL function, so not sure the exact context of the query.  Or if the line break after YEAR could be causing a problem.  How are you executing this from Java?


»bp
0
shieldscoAuthor Commented:
Anybody with other thoughts
0
NorieAnalyst Assistant Commented:
shieldsco

You need to tell us how you are executing the query, without that information it's just kind of guesswork.:)
0
shieldscoAuthor Commented:
I'm executing from entellitrak platform (https://www.micropact.com/products/entellitrak/)
0
Bill PrewCommented:
I'd suggest you connect to the database via SQL Developer, or TOAD, and get your query working first.  Then adapt it for the delivery environment as needed.


»bp
0
shieldscoAuthor Commented:
I have run from SQL developer with the same error
0
Bill PrewCommented:
Please post the full query you are executing.


»bp
0
shieldscoAuthor Commented:
Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"
,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year


From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
shieldscoAuthor Commented:
New Script
I'm getting the following error java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
on this line ,TO_CHAR(C_COMPLETE_REQUEST_RECVD_DT, 'YY') < 10 Then (C_COMPLETE_REQUEST_RECVD_DT, 'YY') Else (C_COMPLETE_REQUEST_RECVD_DT, 'YYYY')+1 AS "FY"

Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as "Appeal_Age"
, W.C_NAME as "Status"
,TO_CHAR(C_COMPLETE_REQUEST_RECVD_DT, 'YY') < 10 Then (C_COMPLETE_REQUEST_RECVD_DT, 'YY') Else (C_COMPLETE_REQUEST_RECVD_DT, 'YYYY')+1 AS "FY"



From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
Bill PrewCommented:
Okay, this should work for the statement we added...

,Case WHEN EXTRACT(MONTH FROM C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN EXTRACT(YEAR FROM C_COMPLETE_REQUEST_RECVD_DT) ELSE EXTRACT(YEAR FROM C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Thanks very much... good job
0
Bill PrewCommented:
Welcome, glad we got that one.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.