How to workaround NVL with TEXT column
Pradeep asked how to apply NVL (or CASE) to a TEXT column in Informix, since his cross-database SQL works on Oracle/DB2/SQL Server but fails on Informix and Sybase. Art Kagel argued NVL is a crutch and NULLs should be handled in the application with indicator variables (ESQL/C example given), and Jonathan Leffler agreed: detect the NULL blob in the app and substitute 'No Value', or better, use VARCHAR/LVARCHAR instead of TEXT. Fernando Nunes showed a SQL workaround using CLOB — casting the column to CLOB inside a CASE, with the default text loaded via FILETOCLOB. No confirmation from the poster that any option was adopted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a table which has TEXT column and I need to use NVL on that column in my SELECT query . Since Informix doesn't support TEXT data in NVL or CASE statement , I'm not sure how to workaround it : select (text_column,'No Value') from Mytab Any help is appreciated. Thanks, Pradeep
Pradeep:
You've hit one of my pet peeves. I've been holding this rant back for
years now, so don't take it personally. This is for everyone who has ever
groused about NVL or asked about eliminating NULLs in their SQL.
Is this query being processed in a host language application of some kind
(ESQL/C, ODBC in C, Perl-DBM, etc.)? If so, the proper way to handle nulls
is not to use the NVL() function to map them away as if your database
doesn't store any. The proper way to handle NULLs in your data to use
indicator variables in your code to detect the NULL in code space. This is
the official ANSI/ISO method for handling NULLs in a host language. NVL is
a crutch originating because most of Oracle's front-end tools could not
handle NULLs properly. It is supported by Informix to make life simpler
for coders used to those brain-dead tools. Heck even Oracle's limited
ESQL/C compiler supports indicators. ODBC supports indicators as does
IBM's DRDA protocol and library.
Even report writers and BI tools like Hyperion (now an Oracle product
itself), Yellow Fin, Cognos, etc. all support NULLs and can translate them
for you after fetching the data. Even if you are just using UNLOAD in
dbaccess and post processing the delimited unload file using AWK, Perl, or
Ruby you can know that a completely empty field of length zero is a NULL
which you could translate in code space. If you are programming SPL
procedures to post process query results for consumption by grunt coders
who don't need to know where their data originates you SPL can handle the
NULLs as well (though I vehemently disagree with the whole concept of
coders who don't understand where their data comes from).
Bottom line, you do not need NVL to support TEXT (or any other data type)
I've coded data based applications for Informix, Sybase, Oracle and other
RDBMS's for 30 years, every one of them handling NULLs properly and not one
included a single call to NVL() in any query
So in ESQL you would:
string text_host[100000];
short text_host_ind;
...
EXEC SQL PREPARE get_data_stmt FROM "SELECT text_col FROM mytable WHERE
keycol = ?";
...
EXEC SQL DESCRIBE get_data_stmt INTO :sqlda_structure;
...
EXEC SQL DECLARE get_data FOR get_data_stmt;
...
EXEC SQL OPEN get_data;
...
EXEC SQL FETCH get_data INTO :text_host indicator text_host_ind;
if (text_host_ind == -1) {
snprintf( text_host, sizeof text_host, "%s", "No Value" );
}
...
Yes, I left lots out like error handling and how to setup to fetch a dumb
blob into ESQL data, and yes in real code text_host would be dynamically
allocated to the correct size not a static length array. But if you have
used ESQL/C and have fecthed blobs before you already know that part.
Flame off..
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Feb 1, 2013 at 5:25 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote:
> I have a table which has TEXT column and I need to use NVL on that column
> in
> my SELECT query . Since Informix doesn't support TEXT data in NVL or CASE
> statement , I'm not sure how to workaround it :
>
> select (text_column,'No Value') from Mytab
>
> Any help is appreciated.
>
> Thanks,
> Pradeep
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d040839d10da7dd04d4a85568
Hi Art, Thanks so much for deatiled explanation and code snippets. Unfortunately this query is being run as a plain SQL in our application ( outside of ESQL/C) and its working fine on Oracle,DB2,MSS but fails on Informix and Sybase. Any suggestion ..? Thanks in advance. Pradeep
What's the application written in? Or what 3rd party app is it? Maybe someone in the community has already worked around this one? This touched another pet peeve of mine. Sorry Pradeep to make you partial target of two flames in one morning. For all: Don't post the solution to your problem that you tried and couldn't get to work! Post the original problem and perhaps also tell us what you tried that didn't work to save time. With the whole problem in front of us, we're more likely to find the answer for you. Flame off. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 1, 2013 at 7:17 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > Hi Art, Thanks so much for deatiled explanation and code snippets. > > Unfortunately this query is being run as a plain SQL in our application > ( outside of ESQL/C) and its working fine on Oracle,DB2,MSS but fails on > Informix and Sybase. > > Any suggestion ..? > > Thanks in advance. > > Pradeep > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d04426cccd45a3804d4a90e30
This would require much more careful investigation... However, if your
application can handle a CLOB instead of a TEXT for that particular query,
you can try do work based on this:
panther@pacman.onlinedomus.net:informix-> cat dummy.txt
1|This TEXT is NOT null...|
2||
panther@pacman.onlinedomus.net:informix-> cat dummy1.txt
This TEXT is null...
panther@pacman.onlinedomus.net:informix-> cat blob_null.sql
drop table if exists fnunes;
drop table if exists fnunes1;
create table fnunes
(
col1 integer,
col2 text
);
create table fnunes1
(
col1 CLOB
);
LOAD FROM 'dummy.txt' INSERT INTO fnunes;
INSERT INTO fnunes1 VALUES ( FILETOCLOB('dummy1.txt','client'));
SELECT
col1,
CASE
WHEN col2 IS NULL THEN
(SELECT col1 FROM fnunes1)
ELSE
col2::CLOB
END
-- NVL(col1,"This TEXT is null...")
FROM
fnunes;
panther@pacman.onlinedomus.net:informix-> dbaccess stores blob_null.sql
Database selected.
Table dropped.
Table dropped.
Table created.
Table created.
2 row(s) loaded.
1 row(s) inserted.
col1 1
(expression)
This TEXT is NOT null...
col1 2
(expression)
This TEXT is null...
2 row(s) retrieved.
Database closed.
1 row(s) retrieved.
Database closed.
On Fri, Feb 1, 2013 at 12:17 PM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote:
> Hi Art, Thanks so much for deatiled explanation and code snippets.
>
> Unfortunately this query is being run as a plain SQL in our application
> ( outside of ESQL/C) and its working fine on Oracle,DB2,MSS but fails on
> Informix and Sybase.
>
> Any suggestion ..?
>
> Thanks in advance.
>
> Pradeep
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--f46d043d6771728a6b04d4aaca4b
On Fri, Feb 1, 2013 at 2:25 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > I have a table which has TEXT column and I need to use NVL on that column > in > my SELECT query . Since Informix doesn't support TEXT data in NVL or CASE > statement , I'm not sure how to workaround it : > > select (text_column,'No Value') from Mytab > In the application, check for a NULL blob back, and substitute your 'No Value' string. Don't use a TEXT column; use a VARCHAR or LVARCHAR. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --047d7b622434a7cb0504d4ab23c8
That's what I said! ;-) Nice to have backup. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 1, 2013 at 10:10 AM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > On Fri, Feb 1, 2013 at 2:25 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> > wrote: > > > I have a table which has TEXT column and I need to use NVL on that column > > in > > my SELECT query . Since Informix doesn't support TEXT data in NVL or CASE > > statement , I'm not sure how to workaround it : > > > > select (text_column,'No Value') from Mytab > > > > In the application, check for a NULL blob back, and substitute your 'No > Value' string. > > Don't use a TEXT column; use a VARCHAR or LVARCHAR. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --047d7b622434a7cb0504d4ab23c8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec554df40d7daf304d4ab2d07
And for your other comment... What you've just described is an X-Y Problem ( http://mywiki.wooledge.org/XyProblem). The question asks about X, which seems a little odd to those who might help, and upon further investigation, the real problem is Y, but the questioner thought that they could almost solve X and if they could solve X then they could work to a solution for Y. And 'just SQL' is a misnomer; there is no such thing as 'just SQL'. SQL is always executed by a program, and it matters what language the program is written in. DB-Access is written in ESQL/C, for example (but is otherwise about as close to 'just SQL' as you can get). On Fri, Feb 1, 2013 at 7:13 AM, Art Kagel <art.kagel@gmail.com> wrote: > That's what I said! ;-) Nice to have backup. > I hadn't seen your response at the time I wrote mine. :( > On Fri, Feb 1, 2013 at 10:10 AM, Jonathan Leffler wrote: > > > On Fri, Feb 1, 2013 at 2:25 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> > > wrote: > > > > > I have a table which has TEXT column and I need to use NVL on that > column in > > > my SELECT query . Since Informix doesn't support TEXT data in NVL or > CASE > > > statement , I'm not sure how to workaround it : > > > > > > select (text_column,'No Value') from Mytab > > > > In the application, check for a NULL blob back, and substitute your 'No > > Value' string. > > > > Don't use a TEXT column; use a VARCHAR or LVARCHAR. > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --047d7b3a7fea0e8a8304d4abe5f7