Using Database functions in the SQL
hi,
I have a problem with the following SQL, this gets executed in the Database where as you get a BIRT exception when I use it in the BIRT tool. This SQL uses the Database functions by name AXLE_WEIGHTS and HEXTOBINARY , These are created by me for some calculation and are working fine. And as a whole the query fetches expected data when I execute in the database. When the same is throwing the following error:
Cannot get the Result set metadata.
SQL statement doesnot return a Resultset object.
SQL error#1: ORA-00904:"HEXTOBINARY":invalid identifier
Reason: A BIRT exception occured.
Version:2.3.0.v200080606
And the SQL Query is:
Select
S.TX_ID,
S.EQUIP_ID,
A.LANE_ID,
S."Axle_weights",
A.VEH_WT,
A.VEH_LEN,
CSEN.TYPE_DESC,
CC.TYPE_DESC,
CSIT.TYPE_DESC,
P.CARRIER_NAME,
A.VEH_CLASS,
A.VEH_SPEED,
I.TAG_RESULT
from
(Select
T_SITE_TX.TX_ID,
T_SITE_TX.EQUIP_ID,
T_SITE_TX.TAG_ID,
axle_weight(T_SITE_TX.TOTAL_AXLES,T_SITE_TX.AXLE_WEIGHTS,T_SITE_TX.TANDEM_AXLES,T_SITE_TX.TRIDEM_AXLES,T_SITE_TX.AXLE_VIOLATIONS,T_SITE_TX.TANDEM_VIOLATIONS,T_SITE_TX.TRIDEM_VIOLATIONS) as "Axle_weights",
CASE WHEN (instr(reverse(HextoBinary(T_SITE_TX.SENSOR_STAT)),'1',1,1)-1)=-1 THEN NULL ELSE(instr(reverse(HextoBinary(T_SITE_TX.SENSOR_STAT)),'1',1,1)-1) END as "Sensor_Flg",
(instr(reverse(HextoBinary(T_SITE_TX.CREDS_STAT)),'1',1,1)-1) as "Creds_Flg",
(instr(reverse(HextoBinary(T_SITE_TX.SITE_STAT)),'1',1,1)-1) as "Site_Flg"
from
T_SITE_TX) S, T_ADV_TX A, T_PRECLEARANCE P, T_ICN_TX I, T_CODES CSEN, T_CODES CC, T_CODES CSIT
where
S.TX_ID=A.TX_ID AND
S."Sensor_Flg"=CSEN.TYPE_ID AND
CSEN.CODE_TYPE= 'WEIGHTS' AND
S."Creds_Flg"=CC.TYPE_ID AND
CC.CODE_TYPE= 'CREDENTIALS' AND
S."Site_Flg"=CSIT.TYPE_ID AND
CSIT.CODE_TYPE= 'PULLIN'AND
S.TAG_ID= P.TAG_ID AND
S.TX_ID=I.TX_ID
____________________
Please help me out in handling this.
Thanks,
Shankar
