Home » SQL & PL/SQL » SQL & PL/SQL » query (oracle 10g)
query [message #401772] |
Wed, 06 May 2009 07:13  |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
I have table
id num
-------------
11 1
22 2
33 3
44 4
66 6
77 7
88 8
Query is
SELECT ID,
CASE
WHEN ID BETWEEN 0 AND 10 THEN 1
WHEN ID BETWEEN 10 AND 20 THEN 2
WHEN ID BETWEEN 21 AND 30 THEN 3
WHEN ID BETWEEN 31 AND 40 THEN 4
WHEN ID BETWEEN 41 AND 50 THEN 5
WHEN ID BETWEEN 51 AND 60 THEN 6
WHEN ID BETWEEN 61 AND 70 THEN 7
WHEN ID BETWEEN 71 AND 80 THEN 8
WHEN ID BETWEEN 81 AND 90 THEN 9
WHEN ID > 91 THEN 10
ELSE -1
END Cnt
FROM ad
id,cnt
-----
11,2
22,3
33,4
44,5
66,7
77,8
88,9
Here i want to show if any condition does not match that time it will show as 55,0
i.e. for 55 id i want to show 0
Please advice.
|
|
|
|
Re: query [message #401775 is a reply to message #401772] |
Wed, 06 May 2009 07:19   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
INSERT INTO AD ( ID, NUM ) VALUES (
11, 1);
INSERT INTO AD ( ID, NUM ) VALUES (
22, 2);
INSERT INTO AD ( ID, NUM ) VALUES (
33, 3);
INSERT INTO AD ( ID, NUM ) VALUES (
44, 4);
INSERT INTO AD ( ID, NUM ) VALUES (
66, 6);
INSERT INTO AD ( ID, NUM ) VALUES (
77, 7);
INSERT INTO AD ( ID, NUM ) VALUES (
88, 8);
i want result as
id,cnt
-----
11,2
22,3
33,4
44,5
55,0
66,7
77,8
88,9
[Updated on: Wed, 06 May 2009 07:20] Report message to a moderator
|
|
|
Re: query [message #401776 is a reply to message #401772] |
Wed, 06 May 2009 07:19   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Where does come this 55?
Why not talking about 1,2,3,4,5,6,7,8,9,10,999,102456,98763?
Regards
Michel
|
|
|
|
|
Re: query [message #401785 is a reply to message #401782] |
Wed, 06 May 2009 07:28   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
You still have not answered all of the questions asked. Where does the value of 55 come from?
And in future, please try to create a more useful and less useless Thread subject title.
[Updated on: Wed, 06 May 2009 07:28] Report message to a moderator
|
|
|
Re: query [message #401786 is a reply to message #401772] |
Wed, 06 May 2009 07:33   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
suppose condition WHEN ID BETWEEN 51 AND 60 THEN 6
fails then it will not display 6 in the output.
So assume that for this entry i want to disply
id,cnt
-----
11,2
22,3
33,4
44,5
'NONE',6
66,7
77,8
88,9
Please forget about the 55.
Thanks,
[Updated on: Wed, 06 May 2009 07:34] Report message to a moderator
|
|
|
|
Re: query [message #401800 is a reply to message #401791] |
Wed, 06 May 2009 08:15   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It is easy to fill in the blanks in the list of IDs, once we know what the rules to generate a complete list of IDs are.
Another way of putting this is: You can only have an ID missing from the list if you know what the complete list should be.
Tell us how you want the complete list to be calculated.
It looks like (from the data posted) you are looking for ID=11*Num, but I'm pretty sure that's not what you want.
|
|
|
Re: query [message #401807 is a reply to message #401772] |
Wed, 06 May 2009 08:35   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
CREATE TABLE ad(ID NUMBER,num NUMBER)
INSERT INTO AD ( ID, NUM ) VALUES ( 11, 1);
INSERT INTO AD ( ID, NUM ) VALUES ( 22, 2);
INSERT INTO AD ( ID, NUM ) VALUES ( 33, 3);
INSERT INTO AD ( ID, NUM ) VALUES ( 44, 4);
INSERT INTO AD ( ID, NUM ) VALUES ( 66, 6);
INSERT INTO AD ( ID, NUM ) VALUES ( 77, 7);
INSERT INTO AD ( ID, NUM ) VALUES ( 88, 8);
SELECT ID,
CASE
WHEN ID BETWEEN 0 AND 10 THEN 1
WHEN ID BETWEEN 10 AND 20 THEN 2
WHEN ID BETWEEN 21 AND 30 THEN 3
WHEN ID BETWEEN 31 AND 40 THEN 4
WHEN ID BETWEEN 41 AND 50 THEN 5
WHEN ID BETWEEN 51 AND 60 THEN 6
WHEN ID BETWEEN 61 AND 70 THEN 7
WHEN ID BETWEEN 71 AND 80 THEN 8
WHEN ID BETWEEN 81 AND 90 THEN 9
WHEN ID > 91 THEN 10
ELSE -1
END Cnt
FROM ad
id,cnt
--------------------------------------------------------------------------------
11,2
22,3
33,4
44,5
66,7
77,8
88,9
As we can see that we cannot see cnt = 6 in the result.
I want to show output as
id,cnt
--------------------------------------------------------------------------------
11,2
22,3
33,4
44,5
'No',0 -----instead of 6 because query doesnt find the required data.
66,7
77,8
88,9
SO i tried below query.....
WITH tmp
AS
(
SELECT (10*LEVEL)+LEVEL rno FROM dual CONNECT BY LEVEL <= 8
)
SELECT NVL(TO_CHAR(ID), 'NO'),
CASE
WHEN ID BETWEEN 0 AND 10 THEN 1
WHEN ID BETWEEN 10 AND 20 THEN 2
WHEN ID BETWEEN 21 AND 30 THEN 3
WHEN ID BETWEEN 31 AND 40 THEN 4
WHEN ID BETWEEN 41 AND 50 THEN 5
WHEN ID BETWEEN 51 AND 60 THEN 6
WHEN ID BETWEEN 61 AND 70 THEN 7
WHEN ID BETWEEN 71 AND 80 THEN 8
WHEN ID BETWEEN 81 AND 90 THEN 9
WHEN ID NOT BETWEEN 0 AND 1000 THEN 0
WHEN ID > 91 THEN 10
WHEN ID IS NULL THEN 0
ELSE -1
END Cnt
FROM tmp LEFT JOIN ad
ON ad.ID = tmp.rno
ORDER BY rno
It show output as required
But my data is not necessorilly is sequence with 11,22,33......etc
it coluld be between 0-11,12-21,22-31....etc
How could we get result in this case?
Thanks,
|
|
|
Re: query [message #401811 is a reply to message #401807] |
Wed, 06 May 2009 08:47   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You would need to have the ranges to be used in the CASE statement stored in a table somewhere.
It's those ranges that seem to determine what set of values you want back, not the data you've got stored in the table.
Once you've got those ranges somewhere that SQL can get at them, you'd have to check that each range was matched by at least one value from the data.
I can't believe that you've got a problem to which this is the best solution.
Try explaining the actual problem you're trying to solve, rather than the problem you've got with your solution.
|
|
|
Re: query [message #401812 is a reply to message #401807] |
Wed, 06 May 2009 08:51   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Either your description is wrong (again), or your expected result is
id cnt
'No' 0
11 2
22 3
33 4
44 5
'No' 0
66 7
77 8
88 9
'No' 0
|
|
|
Re: query [message #401972 is a reply to message #401772] |
Thu, 07 May 2009 03:10   |
danish_fsd@yahoo.com
Messages: 38 Registered: February 2008 Location: Pakistan
|
Member |
|
|
Try following. Hope it will help you.
SELECT TO_CHAR(ID), cnt, cnt num
FROM
(SELECT ID,
CASE
WHEN ID BETWEEN 0 AND 10 THEN 1
WHEN ID BETWEEN 10 AND 20 THEN 2
WHEN ID BETWEEN 21 AND 30 THEN 3
WHEN ID BETWEEN 31 AND 40 THEN 4
WHEN ID BETWEEN 41 AND 50 THEN 5
WHEN ID BETWEEN 51 AND 60 THEN 6
WHEN ID BETWEEN 61 AND 70 THEN 7
WHEN ID BETWEEN 71 AND 80 THEN 8
WHEN ID BETWEEN 81 AND 90 THEN 9
WHEN ID > 91 THEN 10
ELSE -1
END Cnt
FROM ad
)
UNION
SELECT 'NONE',0,num
FROM
(
SELECT level num
FROM dual
CONNECT BY level<=10
MINUS
SELECT cnt
FROM
(SELECT ID,
CASE
WHEN ID BETWEEN 0 AND 10 THEN 1
WHEN ID BETWEEN 10 AND 20 THEN 2
WHEN ID BETWEEN 21 AND 30 THEN 3
WHEN ID BETWEEN 31 AND 40 THEN 4
WHEN ID BETWEEN 41 AND 50 THEN 5
WHEN ID BETWEEN 51 AND 60 THEN 6
WHEN ID BETWEEN 61 AND 70 THEN 7
WHEN ID BETWEEN 71 AND 80 THEN 8
WHEN ID BETWEEN 81 AND 90 THEN 9
WHEN ID > 91 THEN 10
ELSE -1
END Cnt
FROM ad)
)
ORDER BY 3;
Regards.
Danish
|
|
|
|
Re: query [message #401983 is a reply to message #401977] |
Thu, 07 May 2009 03:47   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Be fair Michel - @Danish's code does produce the required result.
|
|
|
Re: query [message #401996 is a reply to message #401983] |
Thu, 07 May 2009 04:09   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
JRowbottom wrote on Thu, 07 May 2009 10:47 | Be fair Michel - @Danish's code does produce the required result.
|
I don't know if it returns the required result as OP didn't answer to clarify what should the result.
Danish query returns a row for num 10 but not for num -1, is this the required result? Who knows? num 10 is not in OP output.
Regards
Michel
[Updated on: Thu, 07 May 2009 04:10] Report message to a moderator
|
|
|
Re: query [message #402006 is a reply to message #401977] |
Thu, 07 May 2009 04:47   |
danish_fsd@yahoo.com
Messages: 38 Registered: February 2008 Location: Pakistan
|
Member |
|
|
Quote: | When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
|
My solution may not be the exact one. But it can help to achieve the desired output.
@JRowbottom: Thanks for encouragement.
Regards.
Danish
[Updated on: Thu, 07 May 2009 06:26] by Moderator Report message to a moderator
|
|
|
Re: query [message #402027 is a reply to message #402006] |
Thu, 07 May 2009 06:28  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
In what it is complex?
In addition, the main point is that there is NO clear requirements.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Thu Feb 13 15:25:05 CST 2025
|