Solved

select using substituation variable

Posted on 2002-07-01
4
814 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query of Oracle 10g database. 8 79
SQL Retrieve Values 4 57
PL/SQL Display based on value 4 20
Use of Exception to end a Loop 3 19
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

867 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

16 Experts available now in Live!

Get 1:1 Help Now