dbinfo("sqlca.sqlerrd2")

Posted in 2003

Topics: Installation, Setup & Upgrades, Stored Procedures & SPL

Support Informix — Mon, 06 Oct 2003 09:03:59 — source: IIUG Forums & Mailing Lists

Hi all, the function dbinfo("sqlca.sqlerrd2") returns always 1 when the previous statement selects only min(). How how can I check in SPL if the min() function returns 1 real row and not null. The dbinfo("sqlca.sqlerrd2") returns always 1 when min() was the only selected column in previous statement. select min(ideidt) into g_ideidt from ide, vrm where ide.idendx = i_idendx and ide.idevdx = i_idevdx and ide.idedte is null and ide.vrmrrn = vrm.vrmrrn ; if dbinfo("sqlca.sqlerrd2") > 0 then ... if g_ideidt = "" ... didn't work IDS7.31 - AIX4.3 (sorry if HTML, our exchange server transforms text to html, and our admin didn't want install MS patches) Yves Dieltiens FOD BZ ------_=_NextPart_001_01C38BE8.9B2F55B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2653.12"> <TITLE>dbinfo("sqlca.sqlerrd2")</TITLE> </HEAD> <BODY> Hi all, the function dbinfo("sqlca.sqlerrd2") = returns always 1 when the previous statement selects only = min(). How how can I check in SPL if the min() function = returns 1 real row and not null. The dbinfo("sqlca.sqlerrd2") returns = always 1 when min() was the only selected column in previous statement. select  min(ideidt) into  g_ideidt from    ide, vrm where ide.idendx       = =3D i_idendx and ide.idevdx       = =3D i_idevdx             and ide.idedte is null             and = ide.vrmrrn       =3D vrm.vrmrrn ; if dbinfo("sqlca.sqlerrd2") > 0 then = ... if g_ideidt =3D "" ... didn't work IDS7.31 - AIX4.3 (sorry if HTML, our exchange server transforms text = to html, and our admin didn't want install MS patches) Yves Dieltiens FOD BZ </BODY> </HTML> ------_=_NextPart_001_01C38BE8.9B2F55B0--

↪ replying to Support Informix

Mark D. Stock — Mon, 06 Oct 2003 10:44:07 — source: IIUG Forums & Mailing Lists

Support Inf.... wrote: > Hi all, > > the function dbinfo("sqlca.sqlerrd2") returns always 1 > when the previous statement selects only min(). Yes, it will. The MIN function will always return a single row. > How how can I check in SPL if the min() function returns 1 real row > and not null. The only time the MIN function returns a NULL value is if ALL values are NULL. Otherwise it will ignore NULL values. > The dbinfo("sqlca.sqlerrd2") returns always 1 when min() was the > only selected column in previous statement. Yes, that's the number of rows returned. > select min(ideidt) > into g_ideidt > from ide, vrm > > where ide.idendx = i_idendx > and ide.idevdx = i_idevdx > and ide.idedte is null > and ide.vrmrrn = vrm.vrmrrn ; > if dbinfo("sqlca.sqlerrd2") > 0 then ... This will always be TRUE. > if g_ideidt = "" ... didn't work No, it probably didn't. You need to check for NULL. Try this: IF g_ideidt IS NULL THEN -- All values were NULL ... ELSE -- We have the smallest value ... END IF : You could avoid this check by ensuring that ideidt does not allow NULLs. > IDS7.31 - AIX4.3 Thanks for providing that info. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

↪ replying to Mark D. Stock

Danny Wright — Mon, 06 Oct 2003 16:43:10 — source: IIUG Forums & Mailing Lists

> Support Inf.... wrote: > > > Hi all, > > > > the function dbinfo("sqlca.sqlerrd2") returns always 1 > > when the previous statement selects only min(). > > Yes, it will. The MIN function will always return a single row. With the one caveat that is doesn't encounter an error - of course, that goes without saying. I wouldn't mention it, but I've been running into a lot of code lately where error-checking of SQL statements seems to be something that isn't considered important.

↪ replying to Mark D. Stock

Mark D. Stock — Mon, 06 Oct 2003 17:29:51 — source: IIUG Forums & Mailing Lists

Danny Wright wrote: >>Support Inf.... wrote: >> >> >>>Hi all, >>> >>>the function dbinfo("sqlca.sqlerrd2") returns always 1 >>>when the previous statement selects only min(). >> >>Yes, it will. The MIN function will always return a single row. > > > With the one caveat that is doesn't encounter an error - of course, that > goes without saying. It should do. > I wouldn't mention it, but I've been running into a lot of code lately where > error-checking of SQL statements seems to be something that isn't considered > important. Good point. I've also been diving into code lately that doesn't bother trapping errors at all. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

↪ replying to Support Informix

preetinder dhaliwal — Mon, 06 Oct 2003 18:59:19 — source: IIUG Forums & Mailing Lists

You can use nvl(min(nvl(colname,defvalue)),defval2) , this way you can put ur defvalue to a value which is never used in ur application and then check for this and can find out from defval2 if it did not have any rows. Rgds Preetinder Support Inf.... wrote: >Hi all, > >the function dbinfo("sqlca.sqlerrd2") returns always 1 >when the previous statement selects only min(). >How how can I check in SPL if the min() function returns 1 real row >and not null. >The dbinfo("sqlca.sqlerrd2") returns always 1 when min() was the >only selected column in previous statement. > > >select min(ideidt) >into g_ideidt >from ide, vrm > >where ide.idendx = i_idendx >and ide.idevdx = i_idevdx > and ide.idedte is null > and ide.vrmrrn = vrm.vrmrrn ; >if dbinfo("sqlca.sqlerrd2") > 0 then ... > >if g_ideidt = "" ... didn't work > > >IDS7.31 - AIX4.3 > > >(sorry if HTML, our exchange server transforms text to html, and our admin >didn't want install MS patches) > > >Yves Dieltiens >FOD BZ > >------_=_NextPart_001_01C38BE8.9B2F55B0 >Content-Type: text/html; > charset="iso-8859-1" >Content-Transfer-Encoding: quoted-printable > ><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> ><HTML> ><HEAD> ><META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = >charset=3Diso-8859-1"> ><META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = >5.5.2653.12"> ><TITLE>dbinfo("sqlca.sqlerrd2")</TITLE> ></HEAD> ><BODY> > >Hi all, > > >the function dbinfo("sqlca.sqlerrd2") = >returns always 1 > when the previous statement selects only = >min(). > How how can I check in SPL if the min() function = >returns 1 real row > and not null. > The dbinfo("sqlca.sqlerrd2") returns = >always 1 when min() was the > only selected column in previous statement. > > > >select  min(ideidt) > into  g_ideidt > from    ide, vrm > > >where ide.idendx       = >=3D i_idendx > and ide.idevdx       = >=3D i_idevdx >         SIZE=3D2>    and ide.idedte is null >         SIZE=3D2>    and = >ide.vrmrrn       =3D vrm.vrmrrn ; > if dbinfo("sqlca.sqlerrd2") > 0 then = >.. > > >if g_ideidt =3D "" ... didn't work > > > >IDS7.31 - AIX4.3 > > > >(sorry if HTML, our exchange server transforms text = >to html, and our admin didn't want install MS patches) > > > >Yves Dieltiens > FOD BZ > > ></BODY> ></HTML> >------_=_NextPart_001_01C38BE8.9B2F55B0-- > > > > > >

Related threads

Glossary

SPL (Stored Procedure Language)