Forum Stats

  • 3,782,459 Users
  • 2,254,650 Discussions
  • 7,880,082 Comments

Discussions

Categories

if possible to use static strings as variable for long query

Jian-cdo
Jian-cdo Member Posts: 459 Blue Ribbon
in SQL & PL/SQL

Hello,

I have an interactive report with a super long query, in which it has dynamic statement based on different studies, then for each study it has a very long case when clause (at least 200 characters for each study). I don't know how many characters I can have for a sql query since we have over a dozen studies to handle, 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. Although later it will still compose a big query to send to the database, but if it will be easier to maintain the query. Thanks.

· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,396 Red Diamond
    Accepted 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.

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,396 Red Diamond
    Accepted 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.

    · Share on TwitterShare on Facebook
  • Jian-cdo
    Jian-cdo Member Posts: 459 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16812297#Comment_16812297

    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.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,223 Blue Diamond

    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!

    Jian-cdo
    · Share on TwitterShare on Facebook
  • Jian-cdo
    Jian-cdo Member Posts: 459 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16812302#Comment_16812302

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

    · Share on TwitterShare on Facebook