Home » SQL & PL/SQL » SQL & PL/SQL » problem with parsing parameter (Oracle 10.2.0.1, HP-UX)
problem with parsing parameter [message #445201] Fri, 26 February 2010 07:18 Go to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Hi dear all.

I have faced with very interesting problem during sql creation.

I have the following sql:
    select date_oper, ba, dd_db, dd_cr, arh_db,arh_cr
    from  view_arh_saldo_ls_fil_and_ho a
    where (&filialnumber = -1 or a.filialnumber = &filialnumber)
    and (&licsch = '*' or ba like &licsch||'%')
    and (
      (&date_oper = 0 and (date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy'))) or
      (&date_oper = 1 and date_oper = to_date(&dt1,'ddmmyyyy')) or
      (&date_oper = 2 and date_oper = to_date(&dt2,'ddmmyyyy'))
    )
    and (
      dd_db != arh_db or
      dd_cr != arh_cr
    )


The parameters are:


  1. 1 filialnumber is branch_number (number between 0 and 111).
  2. 2 licsch is account number of a client (varchar2)
  3. 3 date_oper additional parameter that should do devision of dates. (it could be 0 to select dates between, 1 to select first date or 2, to select last date)
  4. 4 dt1 - first date of selection
  5. 5 dt2 - last date of selection


Last clause was written to find our core banking errors and has not matter.

Now my problem is:

when i run select in this manner it shows me more than 30 strings with different dates. But there is only 1 problem in 07.01.2010. And i know it. So result should be only 1 string.

Then i have changed the query to the following:
    select date_oper, ba, dd_db, dd_cr, arh_db,arh_cr
    from  view_arh_saldo_ls_fil_and_ho a
    where (&filialnumber = -1 or a.filialnumber = &filialnumber)
    and (&licsch = '*' or ba like &licsch||'%')
    and (
      date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy')
    )
    and (
      dd_db != arh_db or
      dd_cr != arh_cr
    )


Now it works fine!

So my problem is &DATE_OPER parameter. I have changet it name. I tested it on toad, on pl/sql developer, on query which is running from procedure returning ref cursor.

Is it bug or my query has an error?

Best regards,
Murad Baghirov.
Re: problem with parsing parameter [message #445203 is a reply to message #445201] Fri, 26 February 2010 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any error in your post.
How can I answer to something that I don't see?

Regards
Michel
Re: problem with parsing parameter [message #445204 is a reply to message #445203] Fri, 26 February 2010 07:27 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Dear Michael.

answer of the query should be like this:
07-01-2010	|	250904800000007	|	1,681.04	|	3,853.82	|	4,371.22	|	4,098.82	|	4,371.22	|	1,408.64
07-01-2010	|	450904800000139	|	0	|	200	|	-45	|	200	|	200	|	0


but as i said its not:
05-01-2010	|	480104800000030	|	2,926.7	|	1,285	|	15,920.7	|	1,285
05-01-2010	|	480104800000013	|	38,959.95	|	6,874	|	138,959.95	|	6,874
05-01-2010	|	480104800000018	|	1,366.45	|	2,008.22	|	101,366.45	|	2,008.22
05-01-2010	|	480104800000031	|	181.4	|	7,785.78	|	34,193.4	|	7,785.78
06-01-2010	|	105204800000004	|	80,355.91	|	260,355.91	|	260,355.91	|	260,355.91
07-01-2010	|	381201100102733	|	87.8	|	0	|	101.89	|	0
05-01-2010	|	381201100102858	|	61.92	|	0	|	122.14	|	0
07-01-2010	|	250904800000007	|	3,853.82	|	4,371.22	|	4,098.82	|	4,371.22
05-01-2010	|	868104800000001	|	44,353.53	|	46,438.9	|	46,438.9	|	46,438.9
06-01-2010	|	105204800000002	|	62,569.6	|	1,583,022.42	|	1,162,569.6	|	1,583,022.42
07-01-2010	|	450904800000139	|	200	|	-45	|	200	|	200
07-01-2010	|	868104800000001	|	8,218.38	|	18,218.28	|	18,218.28	|	18,218.28


the dates i have sent are 05.01.2010 and 07.01.2010
date_oper = 0
filialnumber = 0
licsch = '*'

correction 1: i have edited Michaels name bcos it was not from capital letter. Sorry Michael.

[Updated on: Fri, 26 February 2010 07:32]

Report message to a moderator

Re: problem with parsing parameter [message #445206 is a reply to message #445204] Fri, 26 February 2010 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can WE know if it is correct or not?
Then how could WE know what should be modified to be corrected?

The only thing I can say is that something is wrong in your post:
SQL> select to_date(&dat,'DD/MM/YYYY') from dual;
Enter value for dat: 01/01/2010
select to_date(01/01/2010,'DD/MM/YYYY') from dual
                    *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

The query should NEVER work.

Regards
Michel

[Updated on: Fri, 26 February 2010 07:32]

Report message to a moderator

Re: problem with parsing parameter [message #445207 is a reply to message #445206] Fri, 26 February 2010 07:34 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

when i pass parameters that should be a string i pass them with ' symbols. it works.

i say that query has error bcos i have checked it with other queries. it should return only 2 rows. like i have answered before.
Re: problem with parsing parameter [message #445208 is a reply to message #445206] Fri, 26 February 2010 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or to use your format:
SQL> select to_date(&dat,'DDMMYYYY') from dual;
Enter value for dat: 05012010
select to_date(05012010,'DDMMYYYY') from dual
               *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

Do you see the problem?
It is better like this:
SQL> select to_date('&dat','DDMMYYYY') from dual;
Enter value for dat: 05012010
TO_DATE('05012010',
-------------------
05/01/2010 00:00:00

Regards
Michel


Re: problem with parsing parameter [message #445210 is a reply to message #445208] Fri, 26 February 2010 07:42 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

dear Michael.
as i said i pass parameters with ' so problem is not in date parameters..
the problem is in date_oper parameter. may be our misunderstanding is because you use sql*plus and i use pl/sql developer or toad

Regards,
Murad

[Updated on: Fri, 26 February 2010 07:44]

Report message to a moderator

Re: problem with parsing parameter [message #445212 is a reply to message #445210] Fri, 26 February 2010 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
as i said i pass parameters with ' so problem is not in date parameters..

How could we know that if you dont tell us?

Quote:
the problem is in date_oper parameter

How could we trust that as you provide no evidence of this.

Quote:
you use sql*plus and i use pl/sql developer or toad

So use SQL*Plus. Wink

Regards
Michel
Re: problem with parsing parameter [message #445213 is a reply to message #445204] Fri, 26 February 2010 08:02 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
psix666 wrote on Fri, 26 February 2010 13:27

but as i said its not:
05-01-2010	|	480104800000030	|	2,926.7	|	1,285	|	15,920.7	|	1,285
05-01-2010	|	480104800000013	|	38,959.95	|	6,874	|	138,959.95	|	6,874
05-01-2010	|	480104800000018	|	1,366.45	|	2,008.22	|	101,366.45	|	2,008.22
05-01-2010	|	480104800000031	|	181.4	|	7,785.78	|	34,193.4	|	7,785.78
06-01-2010	|	105204800000004	|	80,355.91	|	260,355.91	|	260,355.91	|	260,355.91
07-01-2010	|	381201100102733	|	87.8	|	0	|	101.89	|	0
05-01-2010	|	381201100102858	|	61.92	|	0	|	122.14	|	0
07-01-2010	|	250904800000007	|	3,853.82	|	4,371.22	|	4,098.82	|	4,371.22
05-01-2010	|	868104800000001	|	44,353.53	|	46,438.9	|	46,438.9	|	46,438.9
06-01-2010	|	105204800000002	|	62,569.6	|	1,583,022.42	|	1,162,569.6	|	1,583,022.42
07-01-2010	|	450904800000139	|	200	|	-45	|	200	|	200
07-01-2010	|	868104800000001	|	8,218.38	|	18,218.28	|	18,218.28	|	18,218.28


the dates i have sent are 05.01.2010 and 07.01.2010
date_oper = 0
filialnumber = 0
licsch = '*'


Looking at that it would appear that every row matches your query and parameters (with the possible exception of filialnumber since it's not displayed). Which rows in that do you think you shouldn't be getting back?
Re: problem with parsing parameter [message #445214 is a reply to message #445213] Fri, 26 February 2010 08:09 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

filialnumber is 0 Wink

now
05-01-2010	|	480104800000030	|	2,926.7	|	1,285	|	15,920.7	|	1,285
05-01-2010	|	480104800000013	|	38,959.95	|	6,874	|	138,959.95	|	6,874
05-01-2010	|	480104800000018	|	1,366.45	|	2,008.22	|	101,366.45	|	2,008.22
05-01-2010	|	480104800000031	|	181.4	|	7,785.78	|	34,193.4	|	7,785.78
06-01-2010	|	105204800000004	|	80,355.91	|	260,355.91	|	260,355.91	|	260,355.91
07-01-2010	|	381201100102733	|	87.8	|	0	|	101.89	|	0
05-01-2010	|	381201100102858	|	61.92	|	0	|	122.14	|	0
05-01-2010	|	868104800000001	|	44,353.53	|	46,438.9	|	46,438.9	|	46,438.9
06-01-2010	|	105204800000002	|	62,569.6	|	1,583,022.42	|	1,162,569.6	|	1,583,022.42
07-01-2010	|	868104800000001	|	8,218.38	|	18,218.28	|	18,218.28	|	18,218.28


this rows are errors.
when i pass two dates without date_oper parameter in query that i show in the first post the aswer is correct Smile

my problem is: query is right and logic is right to but answer is incorrect Very Happy or i am going crazy.
Re: problem with parsing parameter [message #445215 is a reply to message #445214] Fri, 26 February 2010 08:16 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
psix666 wrote on Fri, 26 February 2010 14:09
filialnumber is 0 Wink

I know that's the value you used. I can't tell if it's the value in the rows returned as you haven't selected that column.

psix666 wrote on Fri, 26 February 2010 14:09

now
05-01-2010	|	480104800000030	|	2,926.7	|	1,285	|	15,920.7	|	1,285
05-01-2010	|	480104800000013	|	38,959.95	|	6,874	|	138,959.95	|	6,874
05-01-2010	|	480104800000018	|	1,366.45	|	2,008.22	|	101,366.45	|	2,008.22
05-01-2010	|	480104800000031	|	181.4	|	7,785.78	|	34,193.4	|	7,785.78
06-01-2010	|	105204800000004	|	80,355.91	|	260,355.91	|	260,355.91	|	260,355.91
07-01-2010	|	381201100102733	|	87.8	|	0	|	101.89	|	0
05-01-2010	|	381201100102858	|	61.92	|	0	|	122.14	|	0
05-01-2010	|	868104800000001	|	44,353.53	|	46,438.9	|	46,438.9	|	46,438.9
06-01-2010	|	105204800000002	|	62,569.6	|	1,583,022.42	|	1,162,569.6	|	1,583,022.42
07-01-2010	|	868104800000001	|	8,218.38	|	18,218.28	|	18,218.28	|	18,218.28


this rows are errors.


Why? They match your where clause as far as I can see.
Re: problem with parsing parameter [message #445216 is a reply to message #445214] Fri, 26 February 2010 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Our problem is that we can't reproduce yours.

I think you should open a SR and see what Oracle will answer you.

Regards
Michel
Re: problem with parsing parameter [message #445221 is a reply to message #445216] Fri, 26 February 2010 09:20 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Dear Michael.

what shoud date_oper parameter do?

when it is 0 it shoud use between
when it is 1 it shoud use first date
and it should use second date when it is 2

but my problem is.
when i restructure my query and use only between clause without date_oper parameter it works fine and return correct results. 2 rows. i know this because wi have missed some errors in core banking on 07.01.2010. it was 2 operations. i have found it today. after this i wrote that query. i have checked it with dirrect query to tables across view. the result was only 2 operations during all our banks life.. then i tried to add that query to view. after this i just copied some where clauses and they return more rows. i have checked each of the account number. they all are correct even from view when i check them one by one. but in complex query without account it show me more rows that should be.

now.

i would explain meaning of each column



  1. 1 transaction date
  2. 2 account number
  3. 3 debet flow of account calculated from operations that was on the date of 1 column
  4. 4 credit flow of account calculated from operations that was on the date of 1 column
  5. 5 debet flow of account accomulated by program and written in the archive
  6. 6 credit flow of account accomulated by program and written in the archive


as u see 3 and 5 column should be equal as soon as 4 should be equal to 6..

but as i said we have only 2 accounts with errors. and they shown in first post.

now i am in very interesting situation.. i know that query should return 2 rows and logic is right but it didnt...

regards,
Murad.
Re: problem with parsing parameter [message #445222 is a reply to message #445221] Fri, 26 February 2010 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: problem with parsing parameter [message #445223 is a reply to message #445221] Fri, 26 February 2010 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What can I add to my previous answer?

Michel Cadot wrote on Fri, 26 February 2010 15:17
Our problem is that we can't reproduce yours.

I think you should open a SR and see what Oracle will answer you.

Regards
Michel

Re: problem with parsing parameter [message #445226 is a reply to message #445221] Fri, 26 February 2010 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
psix666 wrote on Fri, 26 February 2010 15:20

as u see 3 and 5 column should be equal as soon as 4 should be equal to 6..


Not sure what you mean by this but your where clause
and (
      dd_db != arh_db or
      dd_cr != arh_cr
    )
States that any rows where columns 3 and 5 are different should be returned along with any rows where columns 4 and 6 are different.

psix666 wrote on Fri, 26 February 2010 15:20

but as i said we have only 2 accounts with errors. and they shown in first post.

now i am in very interesting situation.. i know that query should return 2 rows and logic is right but it didnt...


As I've been trying to point out all along all the rows in your output match the logic I've outlined above
05-01-2010	|	480104800000030	|	2,926.7	|	1,285	|	15,920.7	|	1,285
05-01-2010	|	480104800000013	|	38,959.95	|	6,874	|	138,959.95	|	6,874
05-01-2010	|	480104800000018	|	1,366.45	|	2,008.22	|	101,366.45	|	2,008.22
05-01-2010	|	480104800000031	|	181.4	|	7,785.78	|	34,193.4	|	7,785.78
06-01-2010	|	105204800000004	|	80,355.91	|	260,355.91	|	260,355.91	|	260,355.91
07-01-2010	|	381201100102733	|	87.8	|	0	|	101.89	|	0
05-01-2010	|	381201100102858	|	61.92	|	0	|	122.14	|	0
05-01-2010	|	868104800000001	|	44,353.53	|	46,438.9	|	46,438.9	|	46,438.9
06-01-2010	|	105204800000002	|	62,569.6	|	1,583,022.42	|	1,162,569.6	|	1,583,022.42
07-01-2010	|	868104800000001	|	8,218.38	|	18,218.28	|	18,218.28	|	18,218.28

So as far as I can see you should definitely be getting more than 2 rows and I really don't understand why you think otherwise.
Re: problem with parsing parameter [message #445258 is a reply to message #445226] Fri, 26 February 2010 12:58 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Dear cookiemonster,

i say that there is no errors in those rows because i checked them 3 times before asking help of our other programmer. then we tried to find problems together.

we have spend more than 3 hours for different checks. then we have found that there is no any error in those accounts. they are correct but result of this query is incorrect. as i said before in my posts i have checked this accounts one by one. with different dates. all of them excep 2 are correct. then we decided to restructure our query. so. we have found that when we replace this:
and (
      (&date_oper = 0 and (date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy'))) or
      (&date_oper = 1 and date_oper = to_date(&dt1,'ddmmyyyy')) or
      (&date_oper = 2 and date_oper = to_date(&dt2,'ddmmyyyy'))
    )


with this:
and date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy')


the result is correct.

So my answer is - why when i use first predicate it return me incorrect resulst.. by the way - i have used this kind of parametrized queries many times and they always works great. but this is first time i have faced with that problem..


Dear all. i understand that you could not say anything before see that in real time. i will ask my chief to take some info ro the query and if he allows me i would post ddl's and inserts. to check all data.

Sorry for wasting your time guys.

As i said i am going crazy with that query. May be there is something wrong in first predicate but why when i use date_oper with 0 value and put 2 equal dates it gave me one result, and when i use only between clause it give me another one. fully different..

Best Regards,
Murad.
Re: problem with parsing parameter [message #445339 is a reply to message #445258] Sun, 28 February 2010 23:19 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Dear all.

I have created temp tables with data i have used for selection ('05012010' and '07012010'). It is not too much so i didnt create any index on new tables. Then i recreated my view for this new test tables. And then after running both queries i have found that they all works fine. Could it be indexing problem in mother-tables?

How often should i rebuild indexes for tables or sould i?

Best regards,
Murad.

[Updated on: Sun, 28 February 2010 23:23]

Report message to a moderator

Re: problem with parsing parameter [message #445340 is a reply to message #445339] Sun, 28 February 2010 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>How often should i rebuild indexes for tables or sould i?
Ready, Fire, Aim!
Re: problem with parsing parameter [message #445411 is a reply to message #445339] Mon, 01 March 2010 10:41 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
psix666 wrote on Mon, 01 March 2010 05:19
Dear all.

I have created temp tables with data i have used for selection ('05012010' and '07012010'). It is not too much so i didnt create any index on new tables. Then i recreated my view for this new test tables. And then after running both queries i have found that they all works fine. Could it be indexing problem in mother-tables?

How often should i rebuild indexes for tables or sould i?

Best regards,
Murad.


It's highly unlikely the problem is anything to do with indexes. For indexes to affect the result of the query, as opposed to the time taken, they would have to be corrupt. It's very difficult (but not impossible) to corrupt oracle indexes and if they were corrupt I would expect:
a) the query to return less rows than it should rather than more.
b) bugs to show up elsewhere in the system - unless your query is the only one to use those indexes.

I personally suspect the real problem is the view doesn't do what you think it does.
The first thing you should do is run both your queries in sqlplus and see if you get the same results.

I have to stress again that the data you've shown us as output from the query matches the where clause of your query. Oracle does not make up data, so those rows must be in your base tables and match the view query as well. Which is why I think the problem is the view.

If I create a table holding the data you've shown as output and then run both of your queries against it I get all rows back in both cases:
SQL> CREATE TABLE view_arh_saldo_ls_fil_and_ho
  2  (
  3  date_oper DATE, 
  4  filialnumber NUMBER, 
  5  ba NUMBER, 
  6  dd_db NUMBER, 
  7  dd_cr NUMBER, 
  8  arh_db NUMBER,
  9  arh_cr NUMBER);

Table created.

SQL> 
SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('05-01-2010', 'DD-MM-YYYY')  , 0,  480104800000030  ,  2926.7  ,  1285  ,  15920.7  ,  1285);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('05-01-2010', 'DD-MM-YYYY')  , 0,  480104800000013  ,  38959.95  ,  6874  ,  138959.95  ,  6874);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('05-01-2010', 'DD-MM-YYYY')  , 0,  480104800000018  ,  1366.45  ,  2008.22  ,  101366.45  ,  2008.22);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('05-01-2010', 'DD-MM-YYYY')  , 0,  480104800000031  ,  181.4  ,  7785.78  ,  34193.4  ,  7785.78);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('06-01-2010', 'DD-MM-YYYY')  , 0,  105204800000004  ,  80355.91  ,  260355.91  ,  260355.91  ,  260355.91);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('07-01-2010', 'DD-MM-YYYY')  , 0,  381201100102733  ,  87.8  ,  0  ,  101.89  ,  0);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('05-01-2010', 'DD-MM-YYYY')  , 0,  381201100102858  ,  61.92  ,  0  ,  122.14  ,  0);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('07-01-2010', 'DD-MM-YYYY')  , 0,  250904800000007  ,  3853.82  ,  4371.22  ,  4098.82  ,  4371.22);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('05-01-2010', 'DD-MM-YYYY')  , 0,  868104800000001  ,  44353.53  ,  46438.9  ,  46438.9  ,  46438.9);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('06-01-2010', 'DD-MM-YYYY')  , 0,  105204800000002  ,  62569.6  ,  1583022.42  ,  1162569.6  ,  1583022.42);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('07-01-2010', 'DD-MM-YYYY')  , 0,  450904800000139  ,  200  ,  -45  ,  200  ,  200);

1 row created.

SQL> INSERT INTO view_arh_saldo_ls_fil_and_ho (date_oper, filialnumber, ba , dd_db , dd_cr , arh_db, arh_cr)
  2  VALUES (
  3  to_date('07-01-2010', 'DD-MM-YYYY')  , 0,  868104800000001  ,  8218.38  ,  18218.28  ,  18218.28  ,  18218.28);

1 row created.

SQL> commit;

Commit complete.

SQL> select date_oper, ba, dd_db, dd_cr, arh_db,arh_cr
  2      from  view_arh_saldo_ls_fil_and_ho a
  3      where (&filialnumber = -1 or a.filialnumber = &filialnumber)
  4      and (&licsch = '*' or ba like &licsch||'%')
  5      and (
  6        (&date_oper = 0 and (date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy'))) or
  7        (&date_oper = 1 and date_oper = to_date(&dt1,'ddmmyyyy')) or
  8        (&date_oper = 2 and date_oper = to_date(&dt2,'ddmmyyyy'))
  9      )
 10      and (
 11        dd_db != arh_db or
 12        dd_cr != arh_cr
 13      )
 14  /
Enter value for filialnumber: 0
Enter value for filialnumber: 0
old   3:     where (&filialnumber = -1 or a.filialnumber = &filialnumber)
new   3:     where (0 = -1 or a.filialnumber = 0)
Enter value for licsch: '*'
Enter value for licsch: '*'
old   4:     and (&licsch = '*' or ba like &licsch||'%')
new   4:     and ('*' = '*' or ba like '*'||'%')
Enter value for date_oper: 0
Enter value for dt1: '05012010'
Enter value for dt2: '07012010'
old   6:       (&date_oper = 0 and (date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy'))) or
new   6:       (0 = 0 and (date_oper between to_date('05012010','ddmmyyyy') and to_date('07012010','ddmmyyyy'))) or
Enter value for date_oper: 0
Enter value for dt1: '05012010'
old   7:       (&date_oper = 1 and date_oper = to_date(&dt1,'ddmmyyyy')) or
new   7:       (0 = 1 and date_oper = to_date('05012010','ddmmyyyy')) or
Enter value for date_oper: 0
Enter value for dt2: '07012010'
old   8:       (&date_oper = 2 and date_oper = to_date(&dt2,'ddmmyyyy'))
new   8:       (0 = 2 and date_oper = to_date('07012010','ddmmyyyy'))

DATE_OPER               BA      DD_DB      DD_CR     ARH_DB     ARH_CR
--------------- ---------- ---------- ---------- ---------- ----------
20100105 000000 4.8010E+14     2926.7       1285    15920.7       1285
20100105 000000 4.8010E+14   38959.95       6874  138959.95       6874
20100105 000000 4.8010E+14    1366.45    2008.22  101366.45    2008.22
20100105 000000 4.8010E+14      181.4    7785.78    34193.4    7785.78
20100106 000000 1.0520E+14   80355.91  260355.91  260355.91  260355.91
20100107 000000 3.8120E+14       87.8          0     101.89          0
20100105 000000 3.8120E+14      61.92          0     122.14          0
20100107 000000 2.5090E+14    3853.82    4371.22    4098.82    4371.22
20100105 000000 8.6810E+14   44353.53    46438.9    46438.9    46438.9
20100106 000000 1.0520E+14    62569.6 1583022.42  1162569.6 1583022.42
20100107 000000 4.5090E+14        200        -45        200        200

DATE_OPER               BA      DD_DB      DD_CR     ARH_DB     ARH_CR
--------------- ---------- ---------- ---------- ---------- ----------
20100107 000000 8.6810E+14    8218.38   18218.28   18218.28   18218.28

12 rows selected.

SQL> select date_oper, ba, dd_db, dd_cr, arh_db,arh_cr
  2      from  view_arh_saldo_ls_fil_and_ho a
  3      where (&filialnumber = -1 or a.filialnumber = &filialnumber)
  4      and (&licsch = '*' or ba like &licsch||'%')
  5      and (
  6        date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy')
  7      )
  8      and (
  9        dd_db != arh_db or
 10        dd_cr != arh_cr
 11      )
 12  /
Enter value for filialnumber: 0
Enter value for filialnumber: 0
old   3:     where (&filialnumber = -1 or a.filialnumber = &filialnumber)
new   3:     where (0 = -1 or a.filialnumber = 0)
Enter value for licsch: '*'
Enter value for licsch: '*'
old   4:     and (&licsch = '*' or ba like &licsch||'%')
new   4:     and ('*' = '*' or ba like '*'||'%')
Enter value for dt1: '05012010'
Enter value for dt2: '07012010'
old   6:       date_oper between to_date(&dt1,'ddmmyyyy') and to_date(&dt2,'ddmmyyyy')
new   6:       date_oper between to_date('05012010','ddmmyyyy') and to_date('07012010','ddmmyyyy')

DATE_OPER               BA      DD_DB      DD_CR     ARH_DB     ARH_CR
--------------- ---------- ---------- ---------- ---------- ----------
20100105 000000 4.8010E+14     2926.7       1285    15920.7       1285
20100105 000000 4.8010E+14   38959.95       6874  138959.95       6874
20100105 000000 4.8010E+14    1366.45    2008.22  101366.45    2008.22
20100105 000000 4.8010E+14      181.4    7785.78    34193.4    7785.78
20100106 000000 1.0520E+14   80355.91  260355.91  260355.91  260355.91
20100107 000000 3.8120E+14       87.8          0     101.89          0
20100105 000000 3.8120E+14      61.92          0     122.14          0
20100107 000000 2.5090E+14    3853.82    4371.22    4098.82    4371.22
20100105 000000 8.6810E+14   44353.53    46438.9    46438.9    46438.9
20100106 000000 1.0520E+14    62569.6 1583022.42  1162569.6 1583022.42
20100107 000000 4.5090E+14        200        -45        200        200

DATE_OPER               BA      DD_DB      DD_CR     ARH_DB     ARH_CR
--------------- ---------- ---------- ---------- ---------- ----------
20100107 000000 8.6810E+14    8218.38   18218.28   18218.28   18218.28

12 rows selected.

SQL> 


I've had to assume that filialnumber is 0 for all rows.
Re: problem with parsing parameter [message #445453 is a reply to message #445411] Mon, 01 March 2010 23:53 Go to previous message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Because of structure of our core banking system it is very easy to corrupt indexes. I have already faced with this problem 2 times before.
Quote:
I personally suspect the real problem is the view doesn't do what you think it does.
The first thing you should do is run both your queries in sqlplus and see if you get the same results.

I have to stress again that the data you've shown us as output from the query matches the where clause of your query. Oracle does not make up data, so those rows must be in your base tables and match the view query as well. Which is why I think the problem is the view.

I've already said that all other checks show only 2 rows not 12 as they were in the "incorrect" query. Then i decided to copy that data to other tables and recreate new view with new tables. Now both view and tables works fine and fires 2 records. Now i would show u the view and the main sql which i've used to create that view from.

View:
create or replace view view_arh_saldo_ls_fil_and_ho as
    select date_oper, filialnumber, ba, sum(saldo_vhd_nacval) "VHD", sum(nvl(db,0)) "DD_DB", sum(nvl(cr,0)) "DD_CR", sum(a.oboroti_debet_nacval) "ARH_DB", sum(a.oboroti_kredit_nacval) "ARH_CR", sum(a.saldo_ish_nacval) "ISH"
    from (
        select date_oper, filialnumber, licsch "BA", a.saldo_vhd_nacval, db, cr, a.oboroti_debet_nacval, a.oboroti_kredit_nacval, a.saldo_ish_nacval
        from (
            select
              a.filialnumber,
              a.kodvaluti,
              a.date_oper,
              a.field_for_sort,
              a.licsch,
              a.priznak_licsch,
              a.kurs_valuti_licsch,
              a.kursovaya_raznica_yes_no,
              a.otvet_ispoln,
              a.saldo_open_nacval,
              a.saldo_vhd_nacval,
              a.oboroti_debet_nacval,
              a.oboroti_kredit_nacval,
              a.saldo_ish_nacval,
              a.date_posloper_nacval,
              a.date_predoper_nacval,
              a.saldo_open_inval,
              a.saldo_vhd_inval,
              a.oboroti_debet_inval,
              a.oboroti_kredit_inval,
              a.saldo_ish_inval,
              a.date_posloper_inval,
              a.date_predoper_inval,
              a.date_open_licsch,
              db.db,
              cr.cr
             
            from test_saldo_fil a
              left join (
                select date_oper, filialnumber, debet, sum(summa_v_nacval) "DB"
                from arh_dd_fil
                group by date_oper, filialnumber, debet
              ) db on db.filialnumber = a.filialnumber and db.debet = A.licsch and db.datE_oper = a.date_oper
              left join (
                select date_oper, filialnumber, kredit, sum(summa_v_nacval) "CR"
                from arh_dd_fil
                group by date_oper, filialnumber, kredit
              ) cr on cr.filialnumber = a.filialnumber and cr.kredit = a.licsch and cr.date_oper = a.date_oper

            union all

            select
              0 "FILIALNUMBER",
              k.kodvaluti,
              a.date_oper,
              a.field_for_sort,
              a.licsch,
              a.priznak_licsch,
              a.kurs_valuti_licsch,
              a.kursovaya_raznica_yes_no,
              a.otvet_ispoln,
              a.saldo_open_nacval,
              a.saldo_vhd_nacval,
              a.oboroti_debet_nacval,
              a.oboroti_kredit_nacval,
              a.saldo_ish_nacval,
              a.date_posloper_nacval,
              a.date_predoper_nacval,
              a.saldo_open_inval,
              a.saldo_vhd_inval,
              a.oboroti_debet_inval,
              a.oboroti_kredit_inval,
              a.saldo_ish_inval,
              a.date_posloper_inval,
              a.date_predoper_inval,
              a.date_open_licsch,
              db.db,
              cr.cr

            from test_kurval k,test_saldo a
              left join (
                select date_oper, debet, sum(summa_v_nacval) "DB"
                from arh_dd
                group by date_oper, debet
              ) db on db.debet = a.licsch and db.date_oper = a.date_oper
              left join (
                select date_oper, kredit, sum(summa_v_nacval) "CR"
                from arh_dd
                group by date_oper, kredit
              ) cr on cr.kredit = a.licsch and cr.date_oper = a.date_oper
            where substr(a.licsch,5,3) = k.soknamevalut
        ) a
    )a
    group by date_oper, filialnumber, ba

;


Parametrized select:
select *
from (
    select date_oper, ba, sum(saldo_vhd_nacval) "VHD", sum(nvl(db,0)) "DD_DB", sum(nvl(cr,0)) "DD_CR", sum(a.oboroti_debet_nacval) "ARH_DB", sum(a.oboroti_kredit_nacval) "ARH_CR", sum(a.saldo_ish_nacval) "ISH"
    from (
        select date_oper, (case when &ba_len = 0 then licsch else substr(licsch,1,&ba_len) end) "BA", a.saldo_vhd_nacval, db, cr, a.oboroti_debet_nacval, a.oboroti_kredit_nacval, a.saldo_ish_nacval
        from (
            select
              a.filialnumber,
              a.kodvaluti,
              a.date_oper,
              a.field_for_sort,
              a.licsch,
              a.priznak_licsch,
              a.kurs_valuti_licsch,
              a.kursovaya_raznica_yes_no,
              a.otvet_ispoln,
              a.saldo_open_nacval,
              a.saldo_vhd_nacval,
              a.oboroti_debet_nacval,
              a.oboroti_kredit_nacval,
              a.saldo_ish_nacval,
              a.date_posloper_nacval,
              a.date_predoper_nacval,
              a.saldo_open_inval,
              a.saldo_vhd_inval,
              a.oboroti_debet_inval,
              a.oboroti_kredit_inval,
              a.saldo_ish_inval,
              a.date_posloper_inval,
              a.date_predoper_inval,
              a.date_open_licsch,
              db.db,
              cr.cr
             
            from arh_saldo_ls_fil a
              left join (
                select date_oper, filialnumber, debet, sum(summa_v_nacval) "DB"
                from arh_dd_fil
                where (&filialnumber = -1 or filialnumber = &filialnumber)
                and (&licsch = '*' or debet like &licsch||'%')
                and (
                  (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
                  (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
                  (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
                )
                group by date_oper, filialnumber, debet
              ) db on db.filialnumber = a.filialnumber and db.debet = A.licsch and db.datE_oper = a.date_oper
              left join (
                select date_oper, filialnumber, kredit, sum(summa_v_nacval) "CR"
                from arh_dd_fil
                where (&filialnumber = -1 or filialnumber = &filialnumber)
                and (&licsch = '*' or kredit like &licsch||'%')
                and (
                  (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
                  (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
                  (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
                )
                group by date_oper, filialnumber, kredit
              ) cr on cr.filialnumber = a.filialnumber and cr.kredit = a.licsch and cr.date_oper = a.date_oper
            where (&filialnumber = -1 or filialnumber = &filialnumber)
            and (&licsch = '*' or licsch like &licsch||'%')
            and (
              (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
              (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
              (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
            )

            union all

            select
              0 "FILIALNUMBER",
              k.kodvaluti,
              a.date_oper,
              a.field_for_sort,
              a.licsch,
              a.priznak_licsch,
              a.kurs_valuti_licsch,
              a.kursovaya_raznica_yes_no,
              a.otvet_ispoln,
              a.saldo_open_nacval,
              a.saldo_vhd_nacval,
              a.oboroti_debet_nacval,
              a.oboroti_kredit_nacval,
              a.saldo_ish_nacval,
              a.date_posloper_nacval,
              a.date_predoper_nacval,
              a.saldo_open_inval,
              a.saldo_vhd_inval,
              a.oboroti_debet_inval,
              a.oboroti_kredit_inval,
              a.saldo_ish_inval,
              a.date_posloper_inval,
              a.date_predoper_inval,
              a.date_open_licsch,
              db.db,
              cr.cr

            from odb.kurval k, odb.arh_saldo_ls a
              left join (
                select date_oper, debet, sum(summa_v_nacval) "DB"
                from arh_dd
                where (&filialnumber in (-1,0))
                and (&licsch = '*' or debet like &licsch||'%')
                and (
                  (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
                  (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
                  (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
                )
                group by date_oper, debet
              ) db on db.debet = a.licsch and db.datE_oper = a.date_oper
              left join (
                select date_oper, kredit, sum(summa_v_nacval) "CR"
                from arh_dd
                where (&filialnumber in (-1,0))
                and (&licsch = '*' or kredit like &licsch||'%')
                and (
                  (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
                  (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
                  (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
                )
                group by date_oper, kredit
              ) cr on cr.kredit = a.licsch and cr.date_oper = a.date_oper
            where (&filialnumber in (-1,0))
            and substr(a.licsch,5,3) = k.soknamevalut
            and (&licsch = '*' or licsch like &licsch||'%')
            and (
              (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
              (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
              (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
            )
        ) a
        where (&filialnumber = -1 or a.filialnumber = &filialnumber)
        and (&licsch = '*' or licsch like &licsch||'%')
        and (
          (&dt = 0 and date_oper between to_datE(&dt1,'ddmmyyyy') and to_datE(&dt2,'ddmmyyyy')) or
          (&dt = 1 and date_oper = to_datE(&dt1,'ddmmyyyy')) or
          (&dt = 2 and date_oper = to_datE(&dt2,'ddmmyyyy'))
        )
    )a
    group by date_oper, ba
)
where (&with_check=0 or (dd_db != arh_db or dd_cr != arh_cr))


Now as i said i have checked them both on newly created table with data from old tables and they both works fine.
Previous Topic: Help in making query
Next Topic: I'd like to use the same time on one Database two types of fonts English and Russian (Cyrillic)
Goto Forum:
  


Current Time: Sat Oct 01 00:23:00 CDT 2016

Total time taken to generate the page: 0.13071 seconds