Home » SQL & PL/SQL » SQL & PL/SQL » Help with query change (merged)
Help with query change (merged) [message #397975] Tue, 14 April 2009 13:09 Go to next message
reuben12
Messages: 2
Registered: April 2009
Junior Member
There has been change to requirements and i'm not sure how to change my query to handle the changes.

The table basically has entries for each end point of a connection and the query combines the from/to row. Now the requirement change is that the connection can have multiple end points with a single "from" end point. SO the result set may be multiple rows for a connection with the same "from endpoint" and the different "to endpoints".

The query is below ( Sorry about the bad formatting)

Basically the "seq<=2" is no longer true and there is no finite limit. If there are multiple endpoints, the key is that for each result "row" the "from endpoint" will be same but different "to endpoints"

I can do a "SIGN(seq-1),1 " to get the multiple rows but don't know how to remove the "max" without getting a "not a GROUP BY expression" error.

Please help. Thank you.

select tt.descr type, from_id, from_name, from_ra, from_field1,
from_ran, total, to_id, to_name,to_ra, to_field2, to_ran from (
select type,
max(decode(seq, 1, id, null)) from_id,
max(decode(seq, 1, name, null)) from_name,
max(decode(seq, 1, ra, null)) from_ra,
max(decode(seq, 1, field1, null)) from_field1,
max(decode(seq, 1, ran, null)) from_ran,
max(decode(seq, 1, total, null)) total,
max(decode(seq, 2, id, null)) to_id,
max(decode(seq, 2, name, null)) to_name,
max(decode(seq, 2, ra, null)) to_ra,
max(decode(seq, 2, field1, null)) to_field2,
max(decode(seq, 2, ran, null)) to_ran
from (
select tablew.group as group, tablew.id as id, s.name,
nvl(
(select t.field1 from tableB t where t.name = (
select name from tableA where id = another_id)
and t.field2 = tablew.ra and rownum <=1),'No Data')as field1,
tablew.range, tablew.total as total,
tablew.ra, tablew.type,
row_number() over (partition by tablew.group order by tablew.cla, tablew.id) seq
from tablew w,tableS s
where tablew.id like '"+var1+"%' and s.id = tablew.id)
where seq <=2 group by group, type) c, tableC tt
where c.type=tt.type ;

Re: Help with query change (merged) [message #397981 is a reply to message #397975] Tue, 14 April 2009 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
( Sorry about the bad formatting)

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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Help with query change (merged) [message #397986 is a reply to message #397975] Tue, 14 April 2009 13:55 Go to previous message
reuben12
Messages: 2
Registered: April 2009
Junior Member
The formatted query and the Oracle version.
I'll try to get a working version. Thanks for reading.

Oracle Version : 10.1.0.3.0


SELECT tt.descr TYPE,
       from_id,
       from_name,
       from_ra,
       from_field1,
       from_ran,
       total,
       to_id,
       to_name,
       to_ra,
       to_field2,
       to_ran
FROM   (SELECT   TYPE,
                 Max(Decode(seq,1,id,
                                NULL)) from_id,
                 Max(Decode(seq,1,NAME,
                                NULL)) from_name,
                 Max(Decode(seq,1,ra,
                                NULL)) from_ra,
                 Max(Decode(seq,1,field1,
                                NULL)) from_field1,
                 Max(Decode(seq,1,ran,
                                NULL)) from_ran,
                 Max(Decode(seq,1,total,
                                NULL)) total,
                 Max(Decode(seq,2,id,
                                NULL)) to_id,
                 Max(Decode(seq,2,NAME,
                                NULL)) to_name,
                 Max(Decode(seq,2,ra,
                                NULL)) to_ra,
                 Max(Decode(seq,2,field1,
                                NULL)) to_field2,
                 Max(Decode(seq,2,ran,
                                NULL)) to_ran
        FROM     (SELECT tablew.group                                                     AS group,
                         tablew.id                                                        AS id,
                         s.NAME,
                         Nvl((SELECT t.field1
                              FROM   tableb t
                              WHERE  t.NAME = (SELECT NAME
                                               FROM   tablea
                                               WHERE  id = another_id)
                                     AND t.field2 = tablew.ra
                                     AND ROWNUM <= 1),'No Data') AS field1,
                         tablew.RANGE,
                         tablew.total                                                     AS total,
                         tablew.ra,
                         tablew.TYPE,
                         Row_number()
                           OVER(PARTITION BY tablew.group ORDER BY tablew.cla, tablew.id) seq
                  FROM   tablew w,
                         tables s
                  WHERE  tablew.id LIKE '"+var1+"%'
                         AND s.id = tablew.id)
        WHERE    seq <= 2
        GROUP BY group,
                 TYPE) c,
       tablec tt
WHERE  c.TYPE = tt.TYPE; 


 
Previous Topic: Store and retrieve image in oracle database
Next Topic: Help with Data Types of Different versions of Oracle
Goto Forum:
  


Current Time: Sat Dec 10 11:02:34 CST 2016

Total time taken to generate the page: 0.13558 seconds