Solved

# Retriving Year Number Of Data Parameter

Posted on 2013-05-31
416 Views
i have a report , On table transaction consist of :
Trans_Date,Acc_No,Amount
the query is :
``````select * from transaction where trans_date >= :p1 and trans_date <= :p2
and acc_no = :p3
``````

the open balance of each account saved in another table : Acc_Bal
year,acc_no,Op_Bal
now , to have filed open_bal , it should retreive the value from acc_bal
``````select op_bal from acc_bal
where acc_no=:p3
and year = (select to_char(:p1,'yyyy') from dual)
``````
it not accept it
also i used :
``````and year = (select extract(year from :p1) from dual)
``````
it also not accept it
0
Question by:NiceMan331
• 6
• 5
• 3

LVL 48

Expert Comment

ID: 39210380
try:

to_date( :p1 , 'YYYY' )
0

LVL 73

Expert Comment

ID: 39210395
what is :p1   ?  if it's a date
what is year?  is it a number?

assuming date and number then these should work (basically same thing you started with)

select op_bal from acc_bal
where acc_no=:p3
and year =  to_number(to_char(:p1,'yyyy'))

or

select op_bal from acc_bal
where acc_no=:p3
and year =  extract(year from :p1)

no need to use the subquery from dual

by "not accept"  - what happens?

0

LVL 48

Expert Comment

ID: 39210404
sorry

select trunc(to_date( :p1 , 'yyyy-mm-dd'),'yyyy')
from dual
0

LVL 73

Expert Comment

ID: 39210411
>>>> trunc(to_date( :p1 , 'yyyy-mm-dd'),'yyyy')

that assumes :p1 is a string in the form of yyyy-mm-dd,  if that's true that's a heck of a guess, I don't see anything above that indicates either the data type or the format of the content if it is a string.
0

Author Comment

ID: 39210418
yes sdstuber you are right
p1 is a date , and not necessary to be starting of the year
the error i got is regarding : from
it said bad bind from cluuase
0

LVL 48

Expert Comment

ID: 39210437
your code? I don't see much difference in code :)
0

LVL 73

Expert Comment

ID: 39210443
As I noted above, I didn't really change anything significantly from what you started with.,
I was simply clarifying that what you had should have worked, but cleaned it up a little.

how/where are you using your queries?  Are they inside a pl/sql procedure or function?
If so, you don't need to use colon in front of the variables, simply reference the name directly.
0

Author Comment

ID: 39210447
yes , i use them within a function as program unit of a report
0

LVL 73

Expert Comment

ID: 39210459
Is this what you're getting?

SQL> select count(*) from user_tables where last_analyzed > :p1;
SP2-0552: Bind variable "P1" not declared.
SQL>

if so, you need to declare the variable first,  however you can't declare a date bind variable in the sql*plus context

SQL> variable p1 date
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

you'll instead have to declare a string and populate it,  next example shows how to do that using the format from PortletPaul's guess above

SQL> variable p1 varchar2(10)
SQL> exec :p1 := '2013-05-31';

PL/SQL procedure successfully completed.

now though, because the string requires conversion to a date to be useful, if you don't you'll get errors or unreliable results

SQL> select count(*) from user_tables where last_analyzed > :p1;
select count(*) from user_tables where last_analyzed > :p1
*
ERROR at line 1:
ORA-01861: literal does not match format string

so we do an explicit conversion using PortletPaul's suggested format

SQL> select count(*) from user_tables where last_analyzed > to_date(:p1,'yyyy-mm-dd');

COUNT(*)
----------
0
0

LVL 73

Expert Comment

ID: 39210472
oops, didn't see your last response
so, you're doing something like this...

SQL> create or replace function test_func(p1 in date) return integer
2  is
3      v_cnt integer;
4  begin
5      select count(*) into v_cnt from user_tables where last_analyzed > :p1;
6  end;
7  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION TEST_FUNC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/71     PLS-00049: bad bind variable 'P1'

if so,  simply remove the colon

SQL> create or replace function test_func(p1 in date) return integer
2  is
3      v_cnt integer;
4  begin
5      select count(*) into v_cnt from user_tables where last_analyzed > p1;
6  end;
7  /

Function created.
0

Author Comment

ID: 39210561
actually the error were not related to :p1 , but related to keyword FROM where it used as subquery
anyhow , i'm out now of my desk , i can't cee the real error till tomorrow
so , if you please give me a chance for tomorror ,
i like your last function , but could you please asjust it to retrive year number of date
like this
``````create or replace function test_func(p1 in date) return integer
2  is
3      v_cnt integer;
4  begin
select  to_number(to_char(:p1,'yyyy'))  into v_cnt from dual ;
5
6  end;
``````

is will work ?
0

LVL 73

Accepted Solution

sdstuber earned 500 total points
ID: 39210660
you're still putting the colon in front of the variable,  don't do that for pl/sql variables

also, for direct assignments, don't query from dual, just assign the expression to the variable

CREATE OR REPLACE FUNCTION test_func(p1 IN DATE)
RETURN INTEGER
IS
v_year   INTEGER;
BEGIN
v_year  := TO_NUMBER(TO_CHAR(p1, 'yyyy'));
RETURN v_year;
END;

CREATE OR REPLACE FUNCTION test_func(p1 IN DATE)
RETURN INTEGER
IS
v_year   INTEGER;
BEGIN
v_year  := EXTRACT(YEAR FROM p1);
RETURN v_year;
END;

Once I leave work today I won't be near a computer for most of the weekend.  I hope these help.
If not, I'll check back in Sunday evening or next week
0

Author Comment

ID: 39210673
great
i think this is the best solution
i'll try it tomorrow
0

Author Comment

ID: 39212703
the function works
thanx
0

## Featured Post

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be â€¦
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logicalâ€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.