Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Retriving Year Number Of Data Parameter

Posted on 2013-05-31
14
Medium Priority
?
430 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

916 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