Home » SQL & PL/SQL » SQL & PL/SQL » Re-write SQL in Single Query? (merged)
Re-write SQL in Single Query? (merged) [message #301542] Thu, 21 February 2008 00:38 Go to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi All,

select empid, substr(ename, 1, 5), sal, date
from emp
union all
select empid, ename, sal, date
from emp

Is there any other way to re-write the above query in single query, but only four columns, because this is inner query.
I dont want like below query...
select empid, substr(ename, 1, 5), ename, sal, date from emp;

Thanks in advance

bye
Karthik
Re: Re-write SQL? [message #301545 is a reply to message #301542] Thu, 21 February 2008 00:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
@karthik can you explain what are you trying to do.i don't see any inner query here.


regards,
Re: Re-write SQL? [message #301553 is a reply to message #301545] Thu, 21 February 2008 00:48 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Let me repeat the question, some more added...

Hi All,

select empid, substr(ename, 1, 5), sal, date
from emp
where substr(empid, 1, 1) between 1 and 9
union all
select empid, ename, sal, date
from emp
where substr(empid, 1, 1) not between 1 and 9

Is there any other way to re-write the above query in single query?, but only with four columns (diff. where condn.)

I don't want like below query...
select empid, substr(ename, 1, 5), ename, sal, date .....

Thanks in advance

bye
Karthik
Re: Re-write SQL? [message #301559 is a reply to message #301553] Thu, 21 February 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be better to post an example, it is still not clear.
You said: "but only with four columns" but there is at most 4 columns in your queries, so what does mean your "only"?

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Re-write SQL? [message #301567 is a reply to message #301542] Thu, 21 February 2008 01:04 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Ok michel, I read the guide, now my question is...

select empid, substr(ename, 1, 5), sal, date
from emp
where substr(empid, 1, 1) between 1 and 9
union all
select empid, ename, sal, date
from emp
where substr(empid, 1, 1) not between 1 and 9

How to write the above query in single query?
Re: Re-write SQL? [message #301570 is a reply to message #301553] Thu, 21 February 2008 01:07 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Take a look at CASE

At the bottom you'll find a simple example

HTH
Marc
Re: Re-write SQL? [message #301696 is a reply to message #301567] Thu, 21 February 2008 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ap_karthi
select empid, substr(ename, 1, 5), sal, date 
from emp
where substr(empid, 1, 1) between 1 and 9


It is a little bit unclear what you are dealing with here.

How does the 'emp' table look like? Precisely, what is the 'empid' column's data type?

If it is a number (that's what the right side of the equity sign tells us: "between 1 and 9" -> both 1 and 9 are NUMBERS), then - if you are using the SUBSTR against it, it would be better to convert it to a character. So, you'd have
substr(TO_CHAR(empid), 1, 1)
instead of what you have now.

On the other hand, if 'empid' is a character (that's what "substr(empid, 1, 1)" suggests), why don't you enclose 1 and 9 into single quotes, as
where substr(empid, 1, 1) between '1' and '9'


Here's what I think: 'empid' is a positive number. Checking whether the first digit is "between 1 and 9" is just opposite to checking whether the first digit is equal to 0 (zero). But, if 'empid' really IS a number, then the whole story doesn't make any sense because the leading zero will not exist at all, so you'll always get SUBSTR(ename, 1, 5) as a result.

But, if 'empid' is a character, does it contain alphanumerics or just numbers? If it is a number only, you could kick out UNION you use and have something like this:
select empid, 
  decode(substr(to_char(empid), 1, 1), '0', ename, 
                                            substr(ename, 1, 5)) name,
  sal, "date"
from emp;
If there are characters in there, it seems that you'd better check use of the CASE as MarcS suggested.

By the way, I hope that you didn't name a column 'date' as it is a reserved word. If you did, oh my, you must have used double quotes with the column name and this table is a complete mess.

Perhaps you should start over, from the scratch.
in Single Query? [message #301833 is a reply to message #301542] Thu, 21 February 2008 23:19 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi All,

Select a, b, c
from x
where d < '01-Feb-2008'
union all
Select to_number(e), b, c
from x
where d between '01-Feb-2008' and '15-Feb-2008';

I want to write the above query in single query, without using CASE. Is there any other method? Thanks in advance.

bye
karthik
Re: in Single Query? [message #301834 is a reply to message #301833] Thu, 21 February 2008 23:31 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Are we allowed to ask why you don't want to use a perfectly good union statement?
Re: in Single Query? [message #301843 is a reply to message #301833] Fri, 22 February 2008 00:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
.. and how does this differ from your previous post?
Re: in Single Query? [message #301845 is a reply to message #301843] Fri, 22 February 2008 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Merged.

And why don't you now want CASE?
You can use DECODE instead.

Regards
Michel
Re: in Single Query? [message #301877 is a reply to message #301845] Fri, 22 February 2008 02:26 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
There are always other methods to perform a certain task.

I don't see any need to go look for another one if the one I got suits my requirements: the result is correct and the performance is good.

If a query with an UNION does the job fine, then it'll be a query with UNION.

Bottom line: I'm not going to spent much time looking for other solution(s) without knowing why the one provided isn't any good.

Re: in Single Query? [message #301950 is a reply to message #301833] Fri, 22 February 2008 07:45 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
ap_karthi wrote on Fri, 22 February 2008 00:19
Hi All,

Select a, b, c
from x
where d < '01-Feb-2008'
union all
Select to_number(e), b, c
from x
where d between '01-Feb-2008' and '15-Feb-2008';




This is not how to use DATEs in Oracle. Please read the concepts manual on DATEs and Strings.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3826
Previous Topic: error in executing the package
Next Topic: create table with non default date format
Goto Forum:
  


Current Time: Thu Dec 08 00:38:47 CST 2016

Total time taken to generate the page: 0.08797 seconds