I am trying to pull some awards attributes using BI Publisher report in Oracle Cloud Fusion - Grants Management. My query includes a field which need a arithmetic calculation and converting to a character from there,
My BIP Query is like the following,
SELECT
GMS_AWDINFO.CONTRACT_NUMBER AWARD_NUMBER
,GMS_AWDINFO.CONTRACT_NAME AWARD_NAME
,GMS_AWDINFO.ATTRIBUTE2 BILL_TYPE
,GMS_AWDINFO.STS_CODE AWARD_STATUS
,TO_CHAR(GMS_AWDINFO.END_DATE,'MM/DD/YYYY') AS AWARD_END_DATE
,(CASE
WHEN to_date(GMS_AWDINFO.END_DATE, 'yyyy-mm-dd') - trunc(sysdate) > 30
THEN 'Over 30 Days'
WHEN to_date(GMS_AWDINFO.END_DATE, 'yyyy-mm-dd') - trunc(sysdate) between 30 and 1 THEN 'Within 30 Days'
WHEN to_date(GMS_AWDINFO.END_DATE, 'yyyy-mm-dd') - trunc(sysdate) < 1 THEN 'Expired'
END) AS AWARD_EXPIRATION_STATUS
FROM
GMS_AWARD_HEADERS_INFO_V GMS_AWDINFO
WHERE
GMS_AWDINFO.CONTRACT_NUMBER IS NOT NULL
AND (GMS_AWDINFO.CONTRACT_NUMBER IN (:P_AWARD_NUMBER) OR 'ALL' IN (:P_AWARD_NUMBER||'ALL'))
AND (GMS_AWDINFO.STS_CODE IN (:P_AWARD_STATUS) OR 'ALL' IN (:P_AWARD_STATUS||'ALL'))
AND (
CASE WHEN :P_EXPIRATION_STATUS = 'Over 30 Days'
THEN (to_date(GMS_AWDINFO.END_DATE, 'yyyy-mm-dd') - trunc(sysdate)) > 30
WHEN :P_EXPIRATION_STATUS = 'Within 30 Days'
THEN (to_date(GMS_AWDINFO.END_DATE, 'yyyy-mm-dd') - trunc(sysdate)) between 30 and 1
ELSE (to_date(GMS_AWDINFO.END_DATE, 'yyyy-mm-dd') - trunc(sysdate)) < 1
END
)
GROUP BY
GMS_AWDINFO.CONTRACT_NUMBER
,GMS_AWDINFO.CONTRACT_NAME
,GMS_AWDINFO.ATTRIBUTE2
,GMS_AWDINFO.STS_CODE
,GMS_AWDINFO.END_DATE
In my where clause I need to pass parameter P_EXPIRATION_STATUS variable and need to check the corresponding value after a calculation in where clause.
So here according to the value of variable, need to do checking after a calculation. And I added case statement inside where clause.
But I am getting ORA-00905: missing keyword .
I just started in oracle BIP reporting. So can anyone suggest or guide me to resolve this issue please?
Or Do I need to use any different approach here?
CASE expressions return scalar values, they do not return expressions. Just use AND and OR.
SELECT CONTRACT_NUMBER AS AWARD_NUMBER
,CONTRACT_NAME AS AWARD_NAME
,ATTRIBUTE2 AS BILL_TYPE
,STS_CODE AS AWARD_STATUS
,TO_CHAR(END_DATE,'MM/DD/YYYY') AS AWARD_END_DATE
,CASE
WHEN TRUNC(END_DATE) - trunc(sysdate) > 30
THEN 'Over 30 Days'
WHEN TRUNC(END_DATE) - trunc(sysdate) between 30 and 1
THEN 'Within 30 Days'
WHEN TRUNC(END_DATE) - trunc(sysdate) < 1
THEN 'Expired'
END AS AWARD_EXPIRATION_STATUS
FROM GMS_AWARD_HEADERS_INFO_V
WHERE CONTRACT_NUMBER IS NOT NULL
AND ( CONTRACT_NUMBER IN (:P_AWARD_NUMBER)
OR 'ALL' IN (:P_AWARD_NUMBER||'ALL')
)
AND ( STS_CODE IN (:P_AWARD_STATUS)
OR 'ALL' IN (:P_AWARD_STATUS||'ALL')
)
AND (
( :P_EXPIRATION_STATUS = 'Over 30 Days'
AND TRUNC(END_DATE) - trunc(sysdate) > 30
)
OR ( :P_EXPIRATION_STATUS = 'Within 30 Days'
AND TRUNC(END_DATE) - trunc(sysdate) between 30 and 1
)
OR ( :P_EXPIRATION_STATUS NOT IN ('Over 30 Days', 'Within 30 Days')
AND TRUNC(END_DATE) - trunc(sysdate) < 1
)
)
GROUP BY
CONTRACT_NUMBER
,CONTRACT_NAME
,ATTRIBUTE2
,STS_CODE
,END_DATE
I don't know if BI-Publisher has a pre-processor that rewrites the queries according to the contents of the bind variables, but in SQL CONTRACT_NUMBER IN (:P_AWARD_NUMBER) is identical to CONTRACT_NUMBER = :P_AWARD_NUMBER as the bind variable is treated as a single scalar value (so passing a string with a delimited list would still be one value and not a list of values - unless you have a pre-processor that changes that behaviour). Similarly, 'ALL' IN (:P_AWARD_NUMBER||'ALL') is the same as 'ALL' = :P_AWARD_NUMBER||'ALL' and would match nothing if :P_AWARD_NUMBER is set and is effectively the same as :P_AWARD_NUMBER IS NULL.