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
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 #529992 is a reply to message #529984] Thu, 03 November 2011 20:13 Go to previous messageGo to next message
hoppy86
Messages: 5
Registered: October 2011
Junior Member
Update: Looks like Apex doesnt like hard coded dates. It works fine now.

Re: Report Query displays no data [message #530003 is a reply to message #529992] Fri, 04 November 2011 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select TO_DATE('31-july-11') 
  2  from dual;
select TO_DATE('31-july-11')
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

First correctly use the functions.

Regards
Michel
Re: Report Query displays no data [message #530020 is a reply to message #530003] Fri, 04 November 2011 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 21592
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 requires
WHERE 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
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.
Previous Topic: Pagination Error when filtering in an Interactive Report
Next Topic: change apex 2.1 interface settings
Goto Forum:
  


Current Time: Tue Sep 22 16:59:21 CDT 2020