Home » Developer & Programmer » Reports & Discoverer » Error in using the CASE query in report button action trigger (Oracle Reports 6i)
| Error in using the CASE query in report button action trigger [message #355675] |
Mon, 27 October 2008 00:53  |
mnaeembaig
Messages: 8
Registered: November 2007
Location: Karachi
|
Junior Member |
|
|
Hi
I am getting the error
"encountered the symbol case when expecting one of the following...."
This is when I am compiling the button action trigger. My query is as follows.
------------------------------------------------
procedure U_1ButtonAction is
cursor c1 is
SELECT temp_view_voucher_header_rows.ac_cd as header_acc_cd, temp_view_voucher_header_rows.ac_name as header_acc_name,
decode(temp_view_Complete_Voucher.vch_type_name,'Purchase Invoice Items','Purchase Invoice Voucher','Sale Invoice Items','Sale Invoice Voucher',temp_view_Complete_Voucher.vch_type_name),
temp_view_voucher_header_rows.vchr_hdr_remarks, temp_view_Complete_Voucher.jrnl_cd,
jrnl_name, temp_view_Complete_Voucher.vchr_dt, temp_view_Complete_Voucher.vchr_no, tax_cd,
vchr_chq_dt, cur_cd, vchr_cur_rate,
vchr_approval1, vchr_approval2, vchr_approval3,
vchr_approval4, vchr_ds_no, temp_view_Complete_Voucher.ac_cd, temp_view_Complete_Voucher.ac_name, cc_cd,
' ' || vchr_dtl_remarks as vchr_dtl_remarks, vchr_dtl_header,
vchr_dtl_ref_jrnl_cd, vchr_dtl_ref_dt, vchr_dtl_ref_no,
vchr_dtl_dr_amount, vchr_dtl_cr_amount, vchr_dtl_cd, vchr_dtl_sort_order
, NULL
, 'Rs', 'Rupees'
, 'Paisa', 'Paisa'
,CASE WHEN temp_view_Complete_Voucher.vch_type_name IN ('Payment Voucher', 'Receipt Voucher')
AND (SELECT DECODE(fy.pv_disp_amnt,NULL,0,FY.PV_DISP_AMNT) FROM TEMP_FISCAL_YEAR fy
Where fy.org_cd = temp_view_voucher_header_rows.org_cd
AND fy.fy_cd = temp_view_voucher_header_rows.fy_cd) = 0
THEN ''
WHEN temp_view_Complete_Voucher.vch_type_name IN ('Payment Voucher', 'Receipt Voucher')
AND (SELECT DECODE(FY.PV_DISP_AMNT,NULL,0,FY.PV_DISP_AMNT) FROM TEMP_FISCAL_YEAR fy
Where fy.org_cd = temp_view_voucher_header_rows.org_cd
AND fy.fy_cd = temp_view_voucher_header_rows.fy_cd) <=
(SELECT ABS(vchr_dtl_dr_amount - vchr_dtl_cr_amount) FROM TEMP_VOUCHER_DETAIL vdtl2
WHERE vdtl2.vchr_dtl_header = 'Yes'
AND vdtl2.org_cd = temp_view_voucher_header_rows.org_cd
AND vdtl2.fy_cd = temp_view_voucher_header_rows.fy_cd
AND vdtl2.jrnl_cd = temp_view_voucher_header_rows.jrnl_cd
AND vdtl2.vchr_dt = temp_view_voucher_header_rows.vchr_dt
AND vdtl2.vchr_no = temp_view_voucher_header_rows.vchr_no)
THEN 'SUM OVER Rs. '
|| (SELECT fy.pv_disp_amnt FROM TEMP_FISCAL_YEAR fy
WHERE fy.org_cd = temp_view_voucher_header_rows.org_cd
AND fy.fy_cd = temp_view_voucher_header_rows.fy_cd)
Else '' END
FROM temp_view_Complete_Voucher
INNER JOIN temp_journal ON
(temp_journal.jrnl_cd = temp_view_Complete_Voucher.jrnl_cd)
AND (temp_journal.org_cd = temp_view_Complete_Voucher.org_cd)
AND (temp_journal.vch_type_name IN (
SELECT vch_type_name FROM TEMP_VOUCHER_TYPES
WHERE scrop_module IS NULL
OR TEMP_VOUCHER_TYPES.scrop_module = 'GL'))
Inner Join temp_view_voucher_header_rows
On (temp_view_voucher_header_rows.org_cd = temp_view_Complete_Voucher.org_cd)
AND (temp_view_voucher_header_rows.fy_cd = temp_view_Complete_Voucher.fy_cd)
AND (temp_view_voucher_header_rows.jrnl_cd = temp_view_Complete_Voucher.jrnl_cd)
AND (temp_view_voucher_header_rows.vchr_dt = temp_view_Complete_Voucher.vchr_dt)
AND (temp_view_voucher_header_rows.vchr_no = temp_view_Complete_Voucher.vchr_no)
WHERE
temp_view_Complete_Voucher.fy_cd = '2008-09'
AND temp_view_Complete_Voucher.org_cd = 'Herb'
AND temp_view_Complete_Voucher.jrnl_cd = :P_JRNL_CD
AND (temp_view_Complete_Voucher.vchr_dt >=:P_VCHR_DATE
AND temp_view_Complete_Voucher.vchr_dt <= :P_VCHR_DATE)
AND temp_view_Complete_Voucher.vchr_no = :P_VCHR_NO
ORDER BY
temp_view_Complete_Voucher.vch_type_name, temp_view_Complete_Voucher.jrnl_cd,
temp_view_Complete_Voucher.vchr_dt, temp_view_Complete_Voucher.vchr_no
, vchr_dtl_sort_order;
FINTRANS_NO NUMBER;
end;
---------------------------------------------------
I am using the same query in report main query pannel at design time and getting no error. any help in this regards would be highly appreciated.
Thanks
M Naeem Baig
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 30 05:52:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
