Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Report Query displays no data (apex 4.0, 11g)
Report Query displays no data [message #529984] |
Thu, 03 November 2011 17:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/059d83c0ea33fb5f36f9e3f93b3bb449?s=64&d=mm&r=g) |
hoppy86
Messages: 5 Registered: October 2011
|
Junior Member |
|
|
Hi,
I have a report query(below), and the query's quite long but when I run it in sql developer it still displays.
It passes when I create a region or a page for it but when I run it it displays no data found.
I've tried running it from the APEX sql commands and i keep getting different results, firstly it said no data found then i ran it immediately after and it return the right results, after a little googling and checking i went back and now im getting the error: "ORA-01858: a non-numeric character was found where a numeric was expected"
I double checked in ran in sql developer and its fine. At the moment im hard coding in dates as well not passing any variables in.
Heres the query:
SELECT t.institution icode, c.name inst,
SUM(ROUND(t.income * (i.income_fraction / 100),2)) inc,
SUM(ROUND(t.wtpaid * (i.income_fraction / 100),2)) rwtpaid,
SUM( irtn.withholding_tax_on_income
(t.income
,i.income_fraction
,i.interest_tax_rate)) tax,
SUM(irtn.withholding_tax_balance_to_pay
(t.income
,i.income_fraction
,i.interest_tax_rate
,t.wtpaid)) taxtopay
FROM
(SELECT
t.institution institution,
t.investor investor,
SUM( DECODE( t.account, '1560', 0,t.dollar_value * - 1 ) ) income,
SUM( DECODE( t.account, '1560', t.dollar_value, 0 ) ) wtpaid
FROM sysadmin.trx_detail t,
sysadmin.trx_headers h
WHERE
h.document_date >= (TO_DATE('01-JULY-11') )
AND h.document_date <= (TO_DATE('31-JULY-11'))
AND( NVL( h.payment_date, h.document_date ) >=( TO_DATE('01-JULY-11'))
AND NVL( h.payment_date, h.document_date) <= ( TO_DATE('31-JULY-11') ) )
AND h.document_type IN( 'R', 'J', 'P' )
AND t.account || t.sub_account IN
(SELECT c.account || c.sub_account
FROM chart_of_accounts c,
tax_chart tc
WHERE tc.tax_section = 'NZI' AND tc.item_desc = 'Interest'
AND tc.item_id = c.tax_item_id )
AND h.ledger = 'TFL'
AND h.ledger = t.ledger
AND h.document_type = t.document_type
AND h.document_number = t.document_number
AND t.sub_account != 'FA' group by t.institution , t.investor)t,
sysadmin.institutions c,
sysadmin.tax_investor_rwt_rates i
WHERE t.investor = i.investor
AND i.asat_date = TO_DATE('31-july-11')
AND t.institution = c.institution (+)
GROUP BY t.institution, c.name
ORDER BY 1
;
Is there any reason why apex doesnt like this?
Thanks in advance
|
|
|
|
|
Re: Report Query displays no data [message #530020 is a reply to message #530003] |
Fri, 04 November 2011 02:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Just in case you didn't get Michel's point: you have to provide format mask for TO_DATE function.
What I have noticed is that Apex is VERY sensitive regarding implicit datatype conversion. One might say "Apex is stupid; how come it doesn't know how to do that?" - not only for dates, they are rather complex; even simple WHERE my_character_datatype_column = 1 won't work - it requiresWHERE my_character_datatype_column = '1' single quotes. Which is, actually, good - it forces you to write proper queries that would work virtually anywhere.
Check this: Michel already showed what happens without a format mask. I'll go a step further and provide one:
SQL> select to_date('31-july-11', 'dd-month-yy') my_date from dual;
select to_date('31-july-11', 'dd-month-yy') my_date from dual
*
ERROR at line 1:
ORA-01843: not a valid month
Bummer! What's wrong now? Ah, my settings!
SQL> select value from v$nls_parameters
2 where parameter = 'NLS_DATE_LANGUAGE';
VALUE
---------------------------------------------
CROATIAN
OK then, provide a full syntax:
SQL> select to_date('31-july-11', 'dd-month-yy', 'nls_date_language = english') my_date from dual;
MY_DATE
-------------------
31.07.2011 00:00:00
I hope you got the idea.
|
|
|
Re: Report Query displays no data [message #530136 is a reply to message #530020] |
Fri, 04 November 2011 18:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/059d83c0ea33fb5f36f9e3f93b3bb449?s=64&d=mm&r=g) |
hoppy86
Messages: 5 Registered: October 2011
|
Junior Member |
|
|
Hi, Thanks for the help.
I knew there was a format mask - and probably would have used it in the procedure the code came from if I knew what the date input would look like...
I didn't realize that SQL developer processed things different, for some reason it didn't cross my mind.
Thanks for the clarification.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:21:17 CDT 2024
|