Query

[gayathri Dev]

Hi All,

 

I would like to know if in the Query below can be modified to keep the string "text_sample" in one place?

 

 

SELECT
       case

           when t2.col21 = 'text_sample' and t2.col22 = 1 then 'No Action'
           else t1.col14
       end as Task
FROM
      tab1 t1,
      tab2 t2
where
      (t1.col11 = 'text_sample' or (t2.col21 = 'text_sample' and t2.col22 = 1))
      and t2.col23 = t1.col13;

 

Thanks,

G

[Rodrigo Mesquita]

put a variable insted of a fixed text

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Michael Moore]

how about

SELECT

       case

           when t2.col21 = ts and t2.col22 = 1 then 'No Action'

           else t1.col14

       end as Task

FROM 

      tab1 t1,

      tab2 t2,

      (select 'text_sample' ts from dual)t3

where 

      (t1.col11 = ts or (t2.col21 = ts and t2.col22 = 1))

      and t2.col23 = t1.col13;

--

[rich p]

If you're talking about queries encapsulated within a PL/SQL coding framework, variables are usually kept in one place in the declaration section of a pl/sql object such as a procedure or function. Further, if you need to share a variable value between multiple processes, queries and database operations, consider putting all these common objects within the same package. A package variable is usually declared only once, and can be referenced anywhere from amongst the other objects defined as a part of that package.

Though package variables and constants cannot be directly referenced in queries from SQLPlus sessions (non PL/SQL constructs), I have been able to bypass this problem in the past by writing a function or procedure within the same package which calls and outputs the variable I want to share. In that form, my package variable can also be used across to objects in other packages.

One last comment: if you are planning on using a particular sql statement multiple times where your variable is the only thing that changes, look into the topic of bind variables. PL/SQL automatically treats statement variables as bind variables, but in SQL queries themselves that contain variables, it is optimal to use the notation for bind variables in your code as Oracle database engines queried multiple times through queries without a bind variable reference potentially suffer from noticeable inefficiencies.

As always, Google (and the Internet at large) is your friend. Now that you know a few key words and concepts, go forth and search for what additional info you may need to meet with success.

Rich Pascual