Solved

Retriving Year Number Of Data Parameter

Posted on 2013-05-31
14
416 Views
Last Modified: 2013-06-01
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

Open in new window


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)

Open in new window

it not accept it
also i used :
and year = (select extract(year from :p1) from dual)

Open in new window

it also not accept it
0
Comment
Question by:NiceMan331
  • 6
  • 5
  • 3
14 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39210380
try:

to_date( :p1 , 'YYYY' )
0
 
LVL 73

Expert Comment

by:sdstuber
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?

error? no results? wrong result? key board catches f ire?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39210404
sorry

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

Expert Comment

by:sdstuber
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

by:NiceMan331
ID: 39210418
yes sdstuber you are right
p1 is a date , and not necessary to be starting of the year
i'll try your code tomorrow
the error i got is regarding : from
it said bad bind from cluuase
0
 
LVL 48

Expert Comment

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

Expert Comment

by:sdstuber
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Expert Comment

by:sdstuber
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

by:sdstuber
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

by:NiceMan331
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;

Open in new window



is will work ?
0
 
LVL 73

Accepted Solution

by:
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

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

Author Comment

by:NiceMan331
ID: 39212703
the function works
thanx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now