Solved

select using substituation variable

Posted on 2002-07-01
4
837 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:leoahmad
[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
  • 2
4 Comments
 

Accepted Solution

by:
Avotar earned 25 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:leoahmad
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

Technology Partners: 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!

Question has a verified solution.

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

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…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

632 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