Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Inverse WM_CONCAT()

644238Mar 12 2009 — edited Mar 12 2009
Having the following data:

{color:#0000ff}338033 2 531030 142062
338033 2 531010 142062
338033 2 530010 142062{color}

... I use WM_CONCAT() to get:

{color:#0000ff}338033 2 531030,531010,530010 142062{color}

Now what I am looking for is the way around -> Converting the 1 agregated record to the 3 initial records.

I thought I'll ask before starting to program some PL/SQL loops...

Thx,
Peter

Comments

Frank Kulash
Answer

Hi, @jian-cdo
wonder if it's possible I can store the case when part for each study as a static parameter, then later feed it into the sql query when the study_id is passed.
Sure. That's how dynamic SQL is usually generated: some parts of the query are static (never changing), and other parts depend on something like the data in some table. If you have trouble, post a concrete example. (You can simplify the example: instead of posting a CASE expression with 200 WHEN clauses, post one with only 3 WHEN clauses, but just point out that you really have 200.
Maybe a CASE expression isn't the best tool for the job. Perhaps you can get the same results (perhaps more efficiently) by joining to a table, or with a user-defined function.

Marked as Answer by Jian-cdo · Oct 16 2021
Jian-cdo

Thanks for the prompt reply. So far I will stick with the case expression since it's a legacy query. So here is the example of the query (regardless of other tons of table join and sub query):
case when study_id=38 and maxvisit_order<4 then 1
case when study_id=38 and maxvisit_order>4 and maxvisit_order<10 then 2
......
case when study_id=27 and maxvisit_order<6 then 1
case when study_id=27 and maxvisit_order>6 and maxvisit_order<15 then 2
.....
So like the above two parts (for 2 studies), how I can save them somewhere as a static parameter. Thanks.

mathguy

That kind of case expression is exactly what Mr. Kulash had in mind when he said something like "storing data in a table and using a join may be a better approach". A separate table with columns for study_id, maxvisit_order_low, maxvisit_order_high and the values you must assign - and a join on study_id and inequalities for maxvisit_order - will be much easier to maintain than the monster you have now. When needs change, you would only need to change the data (the additional table), you wouldn't have to hunt through the query itself to change its code. The queries will also run much faster.
You say "I will stick with the case expression." WHY? I don't know about other posters here; but when I see a rejection like that (rejection of excellent advice provided here to help you), without a good reason being given by the poster, I choose to move on to the next thread. Good luck!

1 person found this helpful
Jian-cdo

Thanks for further explanation, now I understand what it means by using join. Yes, now I'd like to get rid of Case.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 9 2009
Added on Mar 12 2009
7 comments
1,876 views