Solved

select using substituation variable

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 12c patching 1 80
SQL query question 8 73
dbms_crypto.decrypt   errors out 6 31
ORA-02288: invalid OPEN mode 2 20
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

785 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