f

#### conditional selection of 'AND' or 'Select' in Oracle

I am working on Oracle reports . One of the parameters that a User
inputs is
'report_type'. Now, is there a way to do the following within a SQL
query with no use of procedures or functions.

select blaa from foo where

something = something
// if report_type='some_data' then
and somethingelse = something else
//else do nothing

Also, IS there a way of doing something like the following

// if report_type='some_data' then

select blaa from foo where
something = something
// if report_type='some_data' then
and somethingelse = something else
//else do nothing

else
select blass1 from foo where
something = something
// if report_type='some_data' then
and somethingelse = something else
//else do nothing

 0
rohit_reborn
4/16/2004 11:45:13 PM
comp.databases.oracle.misc 8436 articles. 1 followers.

7 Replies
489 Views

Similar Articles

[PageSpeed] 12

On 16 Apr 2004 16:45:13 -0700, rohit_reborn@yahoo.com (Rohit Dhawan)
wrote:

>Now, is there a way to do the following within a SQL
>query with no use of procedures or functions.

No

--
Sybrand Bakker, Senior Oracle DBA

 0
Sybrand
4/17/2004 5:52:31 AM
"Rohit Dhawan" <rohit_reborn@yahoo.com> wrote in message
> I am working on Oracle reports . One of the parameters that a User
> inputs is
> 'report_type'. Now, is there a way to do the following within a SQL
> query with no use of procedures or functions.
>
> select blaa from foo where
>
> something = something
> // if report_type='some_data' then
> and somethingelse = something else
> //else do nothing
>
>
> Also, IS there a way of doing something like the following
>
> // if report_type='some_data' then
>
>      select blaa from foo where
>       something = something
>     // if report_type='some_data' then
>        and somethingelse = something else
>       //else do nothing
>
> else
>       select blass1 from foo where
>       something = something
>     // if report_type='some_data' then
>         and somethingelse = something else
>     //else do nothing

Could you be more vague about what you want?

Try to be more specific .. or else the answers would be something something.

I'm guessing that decode might be of help in what you want:

select col2
from foo
where  col1 = 'X'
and col3 = decode('&report_type', 'CRITERIA1','Y',col3)
/

select decode('&report_type','CRITERIA1', colx, coly)
from foo
where col1 = 'X'
and col3 = decode('&report_type', 'CRITERIA1','Y',col3)
/

something like that ... unless you want something else :))))

Anurag


 0
Anurag
4/17/2004 6:17:51 PM
I have one query similar to the following:-

select coursenum,sectionnum,instructor from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'

but is it possible to add another 'and' condition to the above query
provided a certain user parameter = 'add'

for e.g

select coursenum,sectionnum,instructor from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'
and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else
the above (first)query gets executed.

I do not want to use procedure for the above as I think everyting can
be done using and,or,not

 0
rohit_reborn
4/18/2004 12:46:55 AM
Rohit Dhawan wrote:
> I have one query similar to the following:-
>
> select coursenum,sectionnum,instructor from
> SECTION s where (sectionnum = '001')
> and coursenum LIKE 'MATH%'
>
>
> but is it possible to add another 'and' condition to the above query
> provided a certain user parameter = 'add'
>
> for e.g
>
> select coursenum,sectionnum,instructor from
> SECTION s where (sectionnum = '001')
> and coursenum LIKE 'MATH%'
> and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else
> the above (first)query gets executed.
>
> I do not want to use procedure for the above as I think everyting can
> be done using and,or,not

HOW?
SQL language does NOT provide any type of conditional statement
such as IF, THEN, ELSE


 0
AnaCDent
4/18/2004 1:13:21 AM
"AnaCDent" <anacedent@hotmail.com> wrote in message
news:SCkgc.55085$U83.54238@fed1read03... | Rohit Dhawan wrote: | > I have one query similar to the following:- | > | > select coursenum,sectionnum,instructor from | > SECTION s where (sectionnum = '001') | > and coursenum LIKE 'MATH%' | > | > | > but is it possible to add another 'and' condition to the above query | > provided a certain user parameter = 'add' | > | > for e.g | > | > select coursenum,sectionnum,instructor from | > SECTION s where (sectionnum = '001') | > and coursenum LIKE 'MATH%' | > and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else | > the above (first)query gets executed. | > | > I do not want to use procedure for the above as I think everyting can | > be done using and,or,not | | HOW? | SQL language does NOT provide any type of conditional statement | such as IF, THEN, ELSE | actually, decode or case can be used for optional predicate criteria -- but you have to be very careful about the impact on performance, as trying to put too much if/then/else logic in a where clause can cause the optimizer to choose a path that is less that optimal (no, i don't have a specific example at this point, but just make sure you always do and explain plan or a trace to determine the effect as you build up additional criteria) i'll give an example in a minute, but first, for this situation, no such cleverness is needed the first query and the second query can be combined (presumably some user interface is providing the values for the bind variables): select coursenum,sectionnum,instructor from SECTION s where sectionnum = :snum and coursenum LIKE :cnu || '%' and instructor LIKE :ins || '%' if the ins bind variable's contents are null, all instructors are matched, so only the sectionnum and coursenum values affect the results if the ins bind variable contains a value, then instructors are also filtered out if the logic requires checking the contents of a variable to determine what search to perform, you could use a CASE or DECODE in the where clause -- but again, be careful of the affect on performance as a simple example: select * from scott.emp where deptno = :dnum and ( ename like :ename || '%' or sal = case when :ename is null then :sval else sal end ) / but, again, in this particular case, what's issuing the SQL? if you're issuing it from anything other than SQL*Plus, can't the UI tool check the variables and issue the most appropriate SQL statement? ;-{ mcs   0 Mark 4/18/2004 12:52:00 PM You are wrong, it does: DECODE or CASE WHEN ... THEN ... ELSE .. END Jan AnaCDent <anacedent@hotmail.com> wrote in message news:<SCkgc.55085$U83.54238@fed1read03>...
> Rohit Dhawan wrote:
> > I have one query similar to the following:-
> >
> > select coursenum,sectionnum,instructor from
> > SECTION s where (sectionnum = '001')
> > and coursenum LIKE 'MATH%'
> >
> >
> > but is it possible to add another 'and' condition to the above query
> > provided a certain user parameter = 'add'
> >
> > for e.g
> >
> > select coursenum,sectionnum,instructor from
> > SECTION s where (sectionnum = '001')
> > and coursenum LIKE 'MATH%'
> > and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else
> > the above (first)query gets executed.
> >
> > I do not want to use procedure for the above as I think everyting can
> > be done using and,or,not
>
> HOW?
> SQL language does NOT provide any type of conditional statement
> such as IF, THEN, ELSE

 0
janik
4/19/2004 10:15:12 AM
On 17 Apr 2004 17:46:55 -0700, rohit_reborn@yahoo.com (Rohit Dhawan) wrote:

>I have one query similar to the following:-
>
>select coursenum,sectionnum,instructor from
>SECTION s where (sectionnum = '001')
>and coursenum LIKE 'MATH%'
>
>
>but is it possible to add another 'and' condition to the above query
>provided a certain user parameter = 'add'
>
>for e.g
>
>select coursenum,sectionnum,instructor from
>SECTION s where (sectionnum = '001')
>and coursenum LIKE 'MATH%'
>and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else
>the above (first)query gets executed.
>
>I do not want to use procedure for the above as I think everyting can
>be done using and,or,not

With Reports 6i, you can create a Formula Column, that formula column will return the additional
condition.

Ex: This is the report's query:

select coursenum,sectionnum,instructor from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'

and if you have a parameter called "and" with something on it, say,  "Y", you code a formula called

begin
if :AND  = 'Y' then
Return ' and instructor LIKE '''L%'''' ; --> you should count the quot. marks
else
return null ;
end if ;
end ;

Ok, now you modify the report's query:

select coursenum,sectionnum,instructor from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'

If Add_And return null, nothing happens. If Add_And returns something, it will be appended to the
query before reports execute it.

I'm using this technique on almost report I write... Works if you want a custom order_by too. I
don't think you can do it with only or/and/not. This way is much easier...

Good Luck.

--
Tiago Rocha
Recife - Brasil
www.diariodastrilhas.cjb.net

 0
Tiago
4/19/2004 5:27:56 PM

Similar Artilces:

conditional Selection of 'AND' and 'Select' statements
I am working on Oracle reports . One of the parameters that a User inputs is 'report_type'. Now, is there a way to do the following within a SQL query with no use of procedures or functions. select blaa from foo where something = something // if report_type='some_data' then and somethingelse = something else //else do nothing Also, IS there a way of doing something like the following // if report_type='some_data' then select blaa from foo where something = something // if report_type='some_data' then and somethin...

'^=' and '~='?
Hello, What is the difference between '^=' and '~='? Thanks, Duckhye ...

'is not' or '!='
A newbie question to you; what is the difference between statements like: if x is not None: and if x != None: Without any context, which one should be preferred? IMHO, the latter is more readable. On 2014-08-18 21:35, ElChino wrote: > A newbie question to you; what is the difference between statements > like: > if x is not None: > and > if x != None: > > Without any context, which one should be preferred? > IMHO, the latter is more readable. > "x == y" tells you whether x and y refer to objects that are equal. "x is y&qu...

'''''''''''''The Running Update/Append Queries Using VBA code Ordeal''''''''''''''
Hello fellow programmers, I am trying to run an append/update query from code, a command button on a form initiates the queries. the format i am using is; _____________________________________________________ SELECT "criteria" FROM "criteria" WHERE "criteria" UPDATE/APPEND "field selections" RecordSource "qryExample" = above text strings" _______________________________________________________________________ When i am running a SELECT query in this manner it works fine with no problems, and accepts the values of specified linked for...

'Select top 10 * ...' returns data but 'Select * ..' does not
Hi I have a query that is performing very strangely. I f I put a top statement in it returns rows, so Select top 10 * from ....... returns 10 rows but without it then no data is returned Select * from .......... returns 0 rows. The problem lay in a join to another database on a datatype of sqlvariant. BOL shows how the sql_variant comparison rules work. I needed to bypass these for data in rows after my top 10 list provided. I had to use openquery and cast the datatype first to get a join. ...

'''''''''''''The Running Update/Append Queries Using VBA code Ordeal'''''''''''''' #2

if str_mo not in ('','.') and str_da not in ('','.') and str_yy not in ('','.') Any shorter ?
Hi, there. =20 I'm just curious if it ever dawned on anybody how to abbreviate this line : if str_mo not in ('','.') and str_da not in ('','.') and str_yy not in ('','.')=20 =20 Igor Kurbeko Clinical Programmer Analyst 678 336 4328 ikurbeko@atherogenics.com =20 no brain no pain =20 how about: if not (str_mo in ('','.') or str_da in ('','.') or str_yy in ('','.')) OR if not (missing(str_mo) or missing(str_da) or missing(str_yy)) Eric On 22 Oct 03 21:13:37 GMT, ikurbeko@ATHER...

A function with 'and' , 'not' , 'null' , 'car' and 'cdr'
What's this ? (defun enigma (x) (and (not (null x)) (or (null (car x)) (enigma (cdr x))))) "I suppose I should learn Lisp, but it seems so foreign." - Paul Graham, Nov 1983 On Wed, Oct 07 2015, CAI GENGYANG wrote: > What's this ? > > > (defun enigma (x) > (and (not (null x)) > (or (null (car x)) > (enigma (cdr x))))) Bad taste? It returns T if the list X contains nil as an element. It would be clearer to write (some #'null x). Helmut CAI GENGYANG ...

Re: if str_mo not in ('','.') and str_da not in ('','.') and str_yy not in ('','.') Any shorter ?
OR you could use ARRAY data new; set old; array igor $(*) str_mo str_da str_yr; do over igor; if igor ~in (' ','.') then do; end; run; Prasad Ravi Igor Kurbeko <ikurbeko@ATHEROGENIC To: SAS-L@LISTSERV.UGA.EDU S.COM> cc: Sent by: "SAS(r) Subject: if str_mo not in ('','.') and str_da not in ('','.') and str_yy ... error: expected '=', ',', ';', 'asm' or '__attrib Hi I'm trying to compile an ADC Driver & come acrosss the following error. I've no experience writing drivers before, and hence have no clue how to fix it. Hope someone out there has encountered the problem & suggesst a fix for the same. The Error is I get is : qadc.c: At top level: qadc.c:97: error: expected '=', ',', ';', 'asm' or '__attribute__' before 'qadc_read' make: *** [qadc.o] Error 1 [root@localhost qadc]# ########################################################################### ADC Driver Code ##... error: expected '=', ',', ';', 'asm' or '__attrib Hi I'm trying to compile an ADC Driver & come acrosss the following error. I've no experience writing drivers before, and hence have no clue how to fix it. Hope someone out there has encountered the problem & suggesst a fix for the same. The Error is I get is : qadc.c: At top level: qadc.c:97: error: expected '=', ',', ';', 'asm' or '__attribute__' before 'qadc_read' make: *** [qadc.o] Error 1 [root@localhost qadc]# ########################################################################### ADC Driver Code ####################... logical to 'on' / 'off' Hi, is there a function implemented doing this conversion? my Problem is, that I want to use the following code: set(handles.edit_curr_trq_sl,'Enable',get(hObject,'Value')) where get(hObject,'Value') gives the state of a checkbox thank you! function [str]=tf2oo(logic) switch logic case 0 str='off'; case 1 str='on'; end%switch end%function tf2oo() while i do not know a built in function, I use my own:) meisterbartsch wrote: > > > function [str]=tf2oo(logic) > switch logic > case 0 > str='off'; &g... Replacing ',' with '.' Hello, I have a huge amount of numbers in a .txt file. The numbers are in the form 2,43252e+1. I need to replace the , with . How should I do this? I'd prefer some import method that does this during the import procedure. -Janne Hi, I guess you import the data as text and convert it then to numbers. Try 'strrep' before you convert the text to numbers. Tobias Jake the Snake schrieb: > Hello, > > I have a huge amount of numbers in a .txt file. The numbers are in the form 2,43252e+1. I need to replace the , with . How should I do this? I'd prefer some import method... Re: '^=' and '~='? Duckhye, According to the doc ( http://xrl.us/befwjx ) they, and one other set of characters, and the mnemonic 'NE' all represent 'NOT EQUAL'. Art ------- On Wed, 11 Feb 2009 16:52:40 -0600, Duck-Hye Yang <dyang@CHAPINHALL.ORG> wrote: >Hello, >What is the difference between '^=' and '~='? > >Thanks, >Duckhye ... Does '!=' equivelent to 'is not' I'm a bit confusing about whether "is not" equivelent to "!=" if a != b: ... if a is not b: ... What's the difference between "is not" and "!=" or they are the same thing? pirata wrote: > I'm a bit confusing about whether "is not" equivelent to "!=" > > if a != b: > ... > > if a is not b: > ... > > > What's the difference between "is not" and "!=" or they are the same thing? No, they are not the same thing. == and != test to see if the *value* of two ... '[OFF]' as in 'offensive'??? Hi, given that 'off-topicness' is indicated as '[OT]' and taking a look at those postings that started the threads indicated as '[OFF]' (which may both be seen as being somewhat offensive) may lead to the conclusion that '[OFF]' stands for offensiveness. I don't think that this is the intended meaning so what actually *does* '[OFF]' mean? I never came across that abbreviation before (although I have been around on the USENET for quite some time) but maybe it is worth knowing? Josef 'Jupp' Schugt NOTE: mails >100 KiB ... 'a'..'z' Is it possible to achieve something like this? switch (mystring.charAt(0)) { case 'a'..'z': // do something break; } "cruster" <cruster@gmail.com> wrote in message news:1151319731.988814.326200@m73g2000cwd.googlegroups.com... > Is it possible to achieve something like this? > > switch (mystring.charAt(0)) { > case 'a'..'z': > // do something > break; > } > There are times when an if statement may be more appropriate ;) Sorry - java is not VB :) -- LTP :) cruster schreef: > Is it possible to achieve somethi... Override 'and' and 'or' Is it possible to override 'and' and/or 'or'? I cannot find a special method for it... __and__ and __rand__ and __or__ and __ror__ are for binary manipulation... any proposals? Have marvelous sunday, Marco Dekker <m.aschwanden@gmail.com> wrote: > Is it possible to override 'and' and/or 'or'? I cannot find a special > method for it... __and__ and __rand__ and __or__ and __ror__ are for > binary manipulation... any proposals? If you want to customize the truth value testing you have to implement __nonzero__ " __nonzero__( self) Call... Meaning of ':' and '{}' Hi, Please, can anyone tell me what is meaning of predicates ':' and '{}' ? ---------------- yours Advait Advait wrote: > Hi, > Please, can anyone tell me what is meaning of predicates ':' and '{}' > ? It depends on the context. : is used for module qualification of predicates. Like lists:member(X,L) The curly brackets are used in Definite Clauses Grammars: find the section on DCGs in the manual or google for it and you will find out easily. There are uses of {} as a functor in datastructures also. Cheers Bart Demoen Hello everyone,... Difference between 'is' and '==' Hey guys, this maybe a stupid question, but I can't seem to find the result anywhere online. When is the right time to use 'is' and when should we use '=='? Thanks alot~ mwql: >Hey guys, this maybe a stupid question, but I can't seem to find the >result anywhere online. When is the right time to use 'is' and when >should we use '=='? http://docs.python.org/ref/comparisons.html -- Ren� Pijlman mwql wrote: > Hey guys, this maybe a stupid question, but I can't seem to find the > result anywhere online. When is the right time to ... replacement for '{' and '}' ? I am still playing around with what kind of syntax I would like to mark up my documents. Are there equivalent long substitutes for { and } when they are not used to describe arguments to functions? Something like \begin{group} and \end{group}. In other words, if I could force myself to write, say, \begin{group} \it ... \end{group} instead of {\it ... }, then I believe I could identify from the markup context what is an argument that belongs to a just invoked macro and what is text. {Of course, in this context, \textit{...} would be better.} No more ambiguity whether a in \myfunction{a} i... replacing '/' with '\\' I have a MKS ksh shell script running on a Windows box. In some scripts I have to convert a UNIX filename into a DOS filename, that is replace the '/' with '\\'. For example, replace /opt/siips/archive/text.dat with \\opt\\siips\\archive\\text.dat. I've tried using sed like$FILE='echo \$FILE|sed "s/\//\\\/g"' which got me \opt\siips\archive\text.dat. It's close but I still need the second \. Does anyone have any suggestion? Or tell me where I've gone wrong? Thanks -- Steffen On 9 Dec 2004 20:14:21 -0800, sajohn52@yahoo.com <sajohn52@yahoo....

'!' vs. '.'
Is there an advantage to using the '!' notation to represent form/ control relationships? (eg. Me!text1 vs Me.text1) I am currently using the '.' notation exclusively (for code completion in the VB Editor), but much of the high-quality code that I've seen (in Duane Hookom's Query-by-Form db, for example) uses the other. Here's one opinion for you: http://doc.advisor.com/doc/05352 robert.waters wrote: >Is there an advantage to using the '!' notation to represent form/ >control relationships? (eg. Me!text1 vs Me.text1) > >I am currently using...

Web resources about - conditional selection of 'AND' or 'Select' in Oracle - comp.databases.oracle.misc

Resources last updated: 2/21/2016 12:53:45 PM