?
Solved

select using substituation variable

Posted on 2002-07-01
4
Medium Priority
?
849 Views
Last Modified: 2007-12-19
I want to write a query that returns result using a substituation variable
Suppose in the well known emp table i want the result of those employees who are hired in December not concerning with their day or year.
I write the query

select ename, hiredate,sal from emp
where hiredate like '%&date%'

on sql prompt if I write "DEC"  for substituation variable then it gives accurate result

ENAME      HIREDATE         SAL
---------- --------- ----------
SMITH      17-DEC-80        800
JAMES      03-DEC-81        950
FORD       03-DEC-81       3000

but i want to spelled out month as "DECEMBER" in substituation variable then it gives wrong result. For similar results I write the query

select ename, hiredate,sal from emp
where hiredate like TO_DATE('%&date%','%MONTH%');

The results are:

Enter value for date: DECEMBER
old   2: where hiredate like TO_DATE('%&date%','%MONTH%')
new   2: where hiredate like TO_DATE('%DECEMBER%','%MONTH%')

no rows selected

Is there any Oracle master to solve my problem????????
0
Comment
Question by:Muhammad Ahmad Imran
  • 2
4 Comments
 

Accepted Solution

by:
Avotar earned 100 total points
ID: 7121555
what i would do is:

select ename
,      hiredate
,      sal
from emp
where to_char(hiredate, '%MONTH%') like UPPER('%&date%');

by the way.. this works with either the 3-letter abbr. as with the full month...:

  1  select sysdate
  2  from dual
  3* where to_char(sysdate, '%MONTH%') like upper('%&date%')
13:54:55 SQL> /
Voer waarde voor date in: jul
01-07-02

1 rij is geselecteerd.

Verstreken: 00:00:00.10
13:54:57 SQL> /
Voer waarde voor date in: JULI
01-07-02

1 rij is geselecteerd.

0
 

Expert Comment

by:dbms_dumb
ID: 7124749
hmm..well what i can think of right now is that if u try altering the session with the DD-MONTH-YY format of the date and try giving it a shot with the same substution variable i think it will go for the session default value for the month.
0
 
LVL 14

Author Comment

by:Muhammad Ahmad Imran
ID: 7125648
no no dbms dump
altering the session is not the solution of this question
if you have any idea then make changes in the query so it can use anywhere where sql can be used like in "MS Access".
0
 

Expert Comment

by:Avotar
ID: 7125968
Leoahmad...

didn;t my solution work for you?

Avotar
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

599 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