Home » SQL & PL/SQL » SQL & PL/SQL » query (oracle 10g)
query [message #401772] Wed, 06 May 2009 07:13 Go to next message
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 #401773 is a reply to message #401772] Wed, 06 May 2009 07:15 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
393 Posts and you still can't post a valid test case?
Re: query [message #401775 is a reply to message #401772] Wed, 06 May 2009 07:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 #401779 is a reply to message #401775] Wed, 06 May 2009 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora_2007 wrote on Wed, 06 May 2009 14:19
...i want result as...

This is NOT a valid test case:
SQL> INSERT INTO AD ( ID, NUM ) VALUES ( 
  2  11, 1); 
INSERT INTO AD ( ID, NUM ) VALUES (
            *
ERROR at line 1:
ORA-00942: table or view does not exist

Regards
Michel

Re: query [message #401782 is a reply to message #401772] Wed, 06 May 2009 07:23 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Smile
CREATE TABLE ad(ID NUMBER,num NUMBER)

[Updated on: Wed, 06 May 2009 07:23]

Report message to a moderator

Re: query [message #401785 is a reply to message #401782] Wed, 06 May 2009 07:28 Go to previous messageGo to next message
pablolee
Messages: 2835
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 Go to previous messageGo to next message
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 #401791 is a reply to message #401786] Wed, 06 May 2009 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why there are not 1, 10 and -1 in your result?

Regards
Michel
Re: query [message #401800 is a reply to message #401791] Wed, 06 May 2009 08:15 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #401977 is a reply to message #401972] Thu, 07 May 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
danish_fsd@yahoo.com wrote on Thu, 07 May 2009 08:16
...
Sorry for put the solution. I will care it in future.

Regards.
Danish

It seems you don't take care so much.
Please wait for OP to precise his requirements, it is disrespect to disregard others' posts.

Having said that, I didn't read your query but I doubt any union is necessary (of course I can't know as we haven't the requirements).

Regards
Michel
Re: query [message #401983 is a reply to message #401977] Thu, 07 May 2009 03:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In what it is complex?
In addition, the main point is that there is NO clear requirements.

Regards
Michel
Previous Topic: Day Number to Full Day Name Conversion
Next Topic: how to pass multiple values to another procedure
Goto Forum:
  


Current Time: Thu Dec 08 16:19:00 CST 2016

Total time taken to generate the page: 0.10635 seconds