SQL & PL/SQL

I want to display null if CameraIN_Top_Count is 0 and CameraIN_Top if CameraIN_Top_Count > 0.

Where Camera_Image is BLOB. Oracle version 10g

Where query for CameraIN_Top may some times return no records found, in that case CameraIN_Top_Count returns 0

WITH
TransactionID AS (SELECT distinct Transaction_ID as Transaction_ID FROM TBL_CAMERA WHERE Transaction_ID=11)
,CameraIN_Front AS (SELECT Camera_Image AS CameraIn_Front FROM TBL_CAMERA WHERE IN_TYPE=0 AND Camera_Type=0 AND Transaction_ID=11)
,CameraIN_Rear AS (SELECT Camera_Image AS CameraIn_Rear FROM TBL_CAMERA WHERE IN_TYPE=0 AND Camera_Type=1 AND Transaction_ID=11)
,CameraIN_Top AS (SELECT Camera_Image AS CameraIn_Top FROM TBL_CAMERA WHERE IN_TYPE=0 AND Camera_Type=2 AND Transaction_ID=11)
,CameraIN_Top_Count AS (SELECT count(1) AS CameraIn_Top_count FROM TBL_CAMERA WHERE IN_TYPE=0 AND Camera_Type=2 AND Transaction_ID=11)
,CameraOut_Front AS (SELECT Camera_Image AS CameraOut_Front FROM TBL_CAMERA WHERE IN_TYPE=1 AND Camera_Type=0 AND Transaction_ID=11)
,CameraOut_Rear AS (SELECT Camera_Image AS CameraOut_Rear FROM TBL_CAMERA WHERE IN_TYPE=1 AND Camera_Type=1 AND Transaction_ID=11)
SELECT
Transaction_ID
,CameraIn_Front
,CameraIn_Rear
,CameraIn_Top_count
,decode(CameraIn_Top_count,0,null,CameraIN_Top) as Camera_IN_TOP
,CameraOut_Front
,CameraOut_Rear
from
TransactionID
,CameraIN_Front
,CameraIN_Rear
,CameraIN_Top
,CameraIN_Top_Count
,CameraOut_Front
,CameraOut_Rear




Error at Command Line:15 Column:36
Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:




Regards,
Yogesh.

Edited by: Yogesh_Nigal on Jan 31, 2013 4:41 PM

Edited by: Yogesh_Nigal on Jan 31, 2013 4:45 PM