Solved

Sql query help regarding repeating rows

Posted on 2006-11-11
6
1,168 Views
Last Modified: 2008-01-09
Hi,

I need help in making the sql query for the following situation, for ex:

I have a table like following

******************
SEQUENCES
******************
SEQ_ID, SUBSEQ_ID, EXTRACOLS.....
1           0                 xy.......
2           0                 xys.....
2           1                 zubyy.....
2           2                 ztu....
3           0                 uoiu....
4           0                 iuzt.....
4           1                 ztre.....
.....
.....

I need to formulate the sql query in such a way that the subseq_id for each seq_id should be considered till 5. It means, the seq_id and other extra cols are repeated till subseq_id is 5. It is not necessary that the subseq_id is always available in the sequences table. If the information of subseq_id is available in the sequences table then the query should consider it otherwise it should make a duplicate row with the seq_id for non-existing subseq_ids.

If one has more questions regarding the above, please ask.

Please advice.
Thanks.
Aks.

0
Comment
Question by:aks143
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17921354
>> otherwise it should make a duplicate row with the seq_id for non-existing subseq_ids.

I didnt quite understand ur output requirement. Can you show a sample resultset that ur expecting?
0
 

Author Comment

by:aks143
ID: 17923293
The subseq_id should be changed and the complete row should be duplicated if subseq_id not available in the table. The subseq_id should be increased to max of 5.

Following is the result table should look like

******************
RESULT SEQUENCES
******************
SEQ_ID, SUBSEQ_ID, EXTRACOLS.....
1           0                 xy.......
1           1                 xy....... << REPEATED with subseq_id 1
1           2                 xy....... << REPEATED with subseq_id 2
1           3                 xy....... << REPEATED with subseq_id 3
1           4                 xy....... << REPEATED with subseq_id 4
1           5                 xy....... << REPEATED with subseq_id 5
2           0                 xys.....
2           1                 zubyy.....
2           2                 ztu....
2           3                 xys..... << REPEATED with subseq_id 3
2           4                 zubyy... << REPEATED with subseq_id 4
2           5                 ztu.... << REPEATED with subseq_id 5
3           0                 uoiu....
3           1                 xy....... << REPEATED with subseq_id 1
3           2                 xy....... << REPEATED with subseq_id 2
3           3                 xy....... << REPEATED with subseq_id 3
3           4                 xy....... << REPEATED with subseq_id 4
3           5                 xy....... << REPEATED with subseq_id 5
.....
.....

Let me know if u need further clarification.
Thanks.
Aks
0
 
LVL 3

Expert Comment

by:cdemir
ID: 17930450
hi,

are you looking for GROUP BY and HAVING clauses? i think, your question is not so clear.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:aks143
ID: 17930582
>>i think, your question is not so clear.

The this is, i want to repeat the rows for non-existing subseq_ids for each seq_id. As shown in the example above. Please clarify, what is not understandable. Thanks.

regards,
Aks


0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17931287
to some extent i can understand your question but not very clear.

1) Just give me the data in the table first as you have shown while asking this question ( take only 3 or 4 records ).
2) Explain what needs to be done in little more details ( do you need in a single SQL or pl/sql proc/function is fine ).
3) Now just give the output of the results which you need ( please provide the output only for those records which you have given in step 1).

This way we can come to conclusion that you need this for sure.

Otherwise i am getting confused because '3           0                 uoiu....' this is given while asking the question but you say that you need output for this as
'3           0                 uoiu....
3           1                 xy....... << REPEATED with subseq_id 1
3           2                 xy....... << REPEATED with subseq_id 2
3           3                 xy....... << REPEATED with subseq_id 3
3           4                 xy....... << REPEATED with subseq_id 4
3           5                 xy....... << REPEATED with subseq_id 5'  --> where did this xy come for seq_id 3 because it should be 'uoiu' as you have given first right.

As  of now i can understand only that for every seq id, you need 6 records from subseq_id from 0 to 5.

Thanks
0
 
LVL 4

Accepted Solution

by:
radja7 earned 200 total points
ID: 17936139
try this:

SELECT sid AS seq_id, rn AS subseq_id
,(
  SELECT g.extracols
  FROM my_table g
  WHERE g.seq_id=sid
  AND g.subseq_id=DECODE(sign(msub-rn),-1,mod((rn+1),(msub+1)),rn)
) e
FROM
(
  SELECT * FROM
  (SELECT rownum-1 rn FROM dual CONNECT BY level<=6) d
  ,(SELECT seq_id sid, MAX(m.subseq_id) msub FROM my_table m GROUP BY seq_id) m
)
,(
  SELECT t.*
  FROM my_table t
) t
WHERE subseq_id(+)=rn
and seq_id(+)=sid
order by sid,rn
;
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question