freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
- From: KERTEL - Maxime MARAIS <mmarais AT kertel.com>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] Unexpected results with null fields
- Date: Tue, 06 Jun 2006 14:18:25 +0200
ZIGLIO, Frediano, VF-IT a écrit :
Hello,
I am running a query looking like the following one (the one I use also select other fields):
SELECT id, isnull(
right('0'+convert(varchar,day(max(T.gdh))),2)
+right('0'+convert(varchar,month(max(T.gdh))),2)
+convert(varchar,year(max(T.gdh)))
+right('0'+convert(varchar,datepart(hour,max(T.gdh))),2)
+right('0'+convert(varchar,datepart(minute,max(T.gdh))),2)
,'010119700000')
FROM MyTable T
GROUP BY id
with MyTable(id int not null, gdh datetime null).
I expect to get either the biggest date (T.gdh) for each id in MyTable ddmmyyyyhhmm formated or '010119700000' when max(T.gdh) if null
When I run this query from MS Query Analyser, I get the expected results: dates looking like ddmmyyyyhhmm or '010119700000'.
When I run this query from a Perl script using ODBC + a DSN with MSSQL Driver on Windows I also get the expected results.
But, when I run this query from a Perl script using DBD::Sybase + FreeTDS on Linux I get dates ddmmyyyyhhmm formated or '0000' when max(T.gdh) is null. It appears the '0000' comes from the right('0'+....) functions: Replaced the first right('0'...) with right('A'...) and i get some 'A000' instead of '0000'.
Any idea about this unexpected behaviour?
Hi!
yes, it depends on settings. ODBC like query analyser concatenate
NULLs using empty string while dblib (and ctlib) by default yield NULL
this means that
SELECT 'x' + NULL
return 'x' under ODBC and NULL under ctlib.
The setting is SET ANSI_NULLS (search it under query analyser help)
freddy77
I Freddy,
My database is set in such a way that SELECT 'x' + NULL returns NULL under either ODBC or query analyser without setting any option.
SET ANSI_NULLS only modifies the way SELECT * FROM myField = NULL is computed.
With SET ANSI_NULLS ON, this request never returns data (SELECT * FROM myField is NULL must be used instead)
With SET ANSI_NULLS OFF, this request returns rows where myField is NULL.
The right SET option is CONCAT_NULL_YIELDS_NULL. But this option is defined at the database level and not at the session level. As a result, the same results would be expected with either FreeTDS or ODBC considering the way my database is set.
If SET CONCAT_NULL_YIELDS_NULL ON if executed prior to run select queries, the problem remains: I still get '0000' instead of '010119700000' when max(T.gdh) is null.
-
Maxime MARAIS
Kertel.
-
[freetds] Unexpected results with null fields,
KERTEL - Maxime MARAIS, 06/06/2006
- <Possible follow-up(s)>
-
Re: [freetds] Unexpected results with null fields,
ZIGLIO, Frediano, VF-IT, 06/06/2006
-
Re: [freetds] Unexpected results with null fields,
KERTEL - Maxime MARAIS, 06/06/2006
- Re: [freetds] Unexpected results with null fields, KERTEL - Maxime MARAIS, 06/06/2006
-
Re: [freetds] Unexpected results with null fields,
KERTEL - Maxime MARAIS, 06/06/2006
Archive powered by MHonArc 2.6.24.