Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Can I use the function if() or iif() in a SQL sentence?

3 posts in FoxPro Last posting was on 2007-01-11 20:39:01.0Z
Hugo Glez Posted on 2007-01-11 02:05:47.0Z
From: "Hugo Glez" <gomh7172@prodigy.net.mx>
Newsgroups: Advantage.FoxPro
Subject: Can I use the function if() or iif() in a SQL sentence?
Date: Wed, 10 Jan 2007 20:05:47 -0600
Lines: 33
Organization: 3GM
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 189.165.19.172
Message-ID: <45a59a78@solutions.advantagedatabase.com>
X-Trace: 10 Jan 2007 19:01:28 -0700, 189.165.19.172
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!189.165.19.172
Xref: solutions.advantagedatabase.com Advantage.FoxPro:218
Article PK: 1109572

I need to execute thee next SELECT,

select distinct mu.id, mu.analisis, mu.area, mu.status, mu.no_result,
mu.registro

from muestras mu, analisis an

where mu.analisis = an.an_codigo

and iif(mu.muestra_nu=1,mu.user_capt<>'interf',1=1)

order by mu.id, mu.analisis



But I have the next error:

AdsQuery1: Error 7200: AQE Error: State = 42000; NativeError = 2115;
[Extended Systems][Advantage SQL Engine]Expected lexical element not found:
=,
<>, <, <=, >, or >= There was a problem parsing the WHERE clause in your
SELECT statement. -- Location of error in the SQL statement is: 32

Can I use the function if() or iif() in a SQL sentence?

Thank you


Hugo González Macias
aruiz@redlab.com.mx
gomh7172@hotmail.com


Joachim Duerr (ADS) Posted on 2007-01-11 09:30:33.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Can I use the function if() or iif() in a SQL sentence?
Newsgroups: Advantage.FoxPro
References: <45a59a78@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 130.214.79.11
Message-ID: <45a603b9@solutions.advantagedatabase.com>
Date: 11 Jan 2007 02:30:33 -0700
X-Trace: 11 Jan 2007 02:30:33 -0700, 130.214.79.11
Lines: 36
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!130.214.79.11
Xref: solutions.advantagedatabase.com Advantage.FoxPro:219
Article PK: 1109573


Hugo Glez wrote in <45a59a78@solutions.advantagedatabase.com>:

> select distinct mu.id, mu.analisis, mu.area, mu.status, mu.no_result,
> mu.registro
>
> from muestras mu, analisis an
>
> where mu.analisis = an.an_codigo
>
> and iif(mu.muestra_nu=1,mu.user_capt<>'interf',1=1)
>
> order by mu.id, mu.analisis

IIF is available in SQL, but I guess it's being used in a different way
than you want:

<snip>
IIF(boolean_expr, true_expr, false_expr)

If boolean_expr is True, the evaluation of true_expr is returned. If
boolean_expr is False, the evaluation of false_expr is returned. See
IIF for more information.
</snip>

e.g.
SELECT IIF(married=true,'married','not married') from mytable

from your statement do you mean something like following?

... and IIF( mu.muestra_nu=1, mu.user_capt<>'interf' , TRUE )=TRUE

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de


Hugo Glez Posted on 2007-01-11 20:39:01.0Z
From: "Hugo Glez" <gomh7172@prodigy.net.mx>
Newsgroups: Advantage.FoxPro
References: <45a59a78@solutions.advantagedatabase.com> <45a603b9@solutions.advantagedatabase.com>
Subject: Re: Can I use the function if() or iif() in a SQL sentence?
Date: Thu, 11 Jan 2007 14:39:01 -0600
Lines: 64
Organization: 3GM
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 189.165.17.124
Message-ID: <45a69f62@solutions.advantagedatabase.com>
X-Trace: 11 Jan 2007 13:34:42 -0700, 189.165.17.124
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!189.165.17.124
Xref: solutions.advantagedatabase.com Advantage.FoxPro:220
Article PK: 1109574

I tryed this:
select distinct mu.id, mu.analisis, mu.area, mu.status, mu.no_result
from muestras mu, analisis an
where mu.analisis = an.an_codigo
and iif(mu.muestra_nu=1,mu.user_capt<>'interf',TRUE)=TRUE
order by mu.id, mu.analisis

But don't work.
Stephan said to me that, I should modify this:

select distinct mu.id, mu.analisis, mu.area, mu.status, mu.no_result
from muestras mu, analisis an
where mu.analisis = an.an_codigo
and (mu.muestra_nu <> 1 or mu.user_capt <> 'interf')
order by mu.id, mu.analisis

And now it's working very well

Thank you, Joachim Duerr

Hugo



"Joachim Duerr (ADS)" <jojo.duerr@gmx.de> escribió en el mensaje
news:45a603b9@solutions.advantagedatabase.com...

> Hugo Glez wrote in <45a59a78@solutions.advantagedatabase.com>:
>
>> select distinct mu.id, mu.analisis, mu.area, mu.status, mu.no_result,
>> mu.registro
>>
>> from muestras mu, analisis an
>>
>> where mu.analisis = an.an_codigo
>>
>> and iif(mu.muestra_nu=1,mu.user_capt<>'interf',1=1)
>>
>> order by mu.id, mu.analisis
>
> IIF is available in SQL, but I guess it's being used in a different way
> than you want:
>
> <snip>
> IIF(boolean_expr, true_expr, false_expr)
>
> If boolean_expr is True, the evaluation of true_expr is returned. If
> boolean_expr is False, the evaluation of false_expr is returned. See
> IIF for more information.
> </snip>
>
> e.g.
> SELECT IIF(married=true,'married','not married') from mytable
>
> from your statement do you mean something like following?
>
> ... and IIF( mu.muestra_nu=1, mu.user_capt<>'interf' , TRUE )=TRUE
>
> --
> Joachim Duerr
> Senior Product Support Analyst (Advantage Database Server)
> iAnywhere Solutions / Extended Systems
> advantage[AT]extendsys.de