OraFAQ Forum: SQL & PL/SQL » Re-write SQL in Single Query? (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Re-write SQL in Single Query? (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re-write SQL in Single Query? (merged) [message #301542]

Thu, 21 February 2008 00:38

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

Report message to a moderator

Re: Re-write SQL? [message #301545 is a reply to message #301542]

Thu, 21 February 2008 00:42

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,

Report message to a moderator

Re: Re-write SQL? [message #301553 is a reply to message #301545]

Thu, 21 February 2008 00:48

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

Report message to a moderator

Re: Re-write SQL? [message #301559 is a reply to message #301553]

Thu, 21 February 2008 00:54

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Re-write SQL? [message #301567 is a reply to message #301542]

Thu, 21 February 2008 01:04

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?

Report message to a moderator

Re: Re-write SQL? [message #301570 is a reply to message #301553]

Thu, 21 February 2008 01:07

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

Report message to a moderator

Re: Re-write SQL? [message #301696 is a reply to message #301567]

Thu, 21 February 2008 07:04

Littlefoot
Messages: 21826
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.

Report message to a moderator

in Single Query? [message #301833 is a reply to message #301542]

Thu, 21 February 2008 23:19

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

Report message to a moderator

Re: in Single Query? [message #301834 is a reply to message #301833]

Thu, 21 February 2008 23:31

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Are we allowed to ask why you don't want to use a perfectly good union statement?

Report message to a moderator

Re: in Single Query? [message #301843 is a reply to message #301833]

Fri, 22 February 2008 00:06

Frank
Messages: 7901
Registered: March 2000

Senior Member

.. and how does this differ from your previous post?

Report message to a moderator

Re: in Single Query? [message #301845 is a reply to message #301843]

Fri, 22 February 2008 00:10

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Merged.

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

Regards
Michel

Report message to a moderator

Re: in Single Query? [message #301877 is a reply to message #301845]

Fri, 22 February 2008 02:26

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.

Report message to a moderator

Re: in Single Query? [message #301950 is a reply to message #301833]

Fri, 22 February 2008 07:45

joy_division
Messages: 4963
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

Report message to a moderator

Previous Topic:	error in executing the package
Next Topic:	create table with non default date format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 09 09:57:32 CST 2025