?
Solved

Retriving Year Number Of Data Parameter

Posted on 2013-05-31
14
Medium Priority
?
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39210380
try:

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

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 49

Expert Comment

by:PortletPaul
ID: 39210404
sorry

select trunc(to_date( :p1 , 'yyyy-mm-dd'),'yyyy')
from dual
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

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 49

Expert Comment

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

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
 

Author Comment

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

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 74

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 74

Accepted Solution

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 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