Solved

select using substituation variable

Posted on 2002-07-01
4
809 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Leoahmad...

didn;t my solution work for you?

Avotar
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

10 Experts available now in Live!

Get 1:1 Help Now