No data in one column of a view (NULL value instead

Question

No data in one column of a view (NULL value instead

polytropic2310

Ten Centuries

Points: 1004
More actions
November 23, 2016 at 10:07 am

#332327

Hello,
I have a table [DataXYZ].[dbo].[DataXYZ_GeoLoc_Input] with, as you can see, data for both "x" and "y" columns for year 2015:
I have a view [EntrepotXYZ].[dbo].[lv_DataXYZ_GeoLoc_Step1] created like this:
SELECT intras_annee, LEFT(siret, 9) AS intras_siren, siret + ident_int AS intras_cle_id, CAST(depcom_red + ISNULL(LTRIM(RTRIM(iris)) + REPLICATE('0', 4 - LEN(LTRIM(RTRIM(iris)))), REPLICATE('0', 4)) AS varchar(9))
AS intras_iris, CASE WHEN LEFT(depcom_red, 2) = '97' THEN LEFT(depcom_red, 3) ELSE LEFT(depcom_red, 2) END AS intras_dep, ident_org, siret, rs, cat_org, typeconst_red, nbpiece_red, construct_red,
conv_red, numconv_red, CASE WHEN datconv_red IS NULL THEN NULL WHEN LTRIM(rtrim(datconv_red)) = '' THEN NULL ELSE RIGHT(datconv_red, 4) + '-' + SUBSTRING(datconv_red, 4, 2)
+ '-' + LEFT(datconv_red, 2) END AS datconv_red, rsexpro_red, siretexpro_red, finan_red, finanautre_red, cus_red, identges_red, ident_int, ident_rep, sru_expir_red, sru_alinea_red, reg, lib_reg, dep, lib_dep,
epci, libepci, depcom_red, lib_depcom, codepostal_red, numvoie_red, indrep_red, typvoie_red, nomvoie_red, numappt_red, numboite_red, esc_red, couloir_red, etage_red, complident_red, entree_red, bat_red,
immeu_red, complgeo_red, lieudit_red, zus_red, loyerprinc_red, loyeracc_red, contrib_red, CAST('' AS xml ).value('sql:column("loymoy") cast as xs:decimal ? ', 'decimal(7,3)') AS loymoy, loyermaxapl_red,
loyermaxcus_red, locat_red, mode_red, bail_red, remlocdate_red, contreslog_red, duree_vacance, codsegpatrim_red, libsegpatrim_red, droit_red, patrimoine_red, origine_red, sortiepatrim_red, newlogt_red,
oldlogt_red, dpedate_red, dpeenergie_red, dpeserre_red, surfhab_red, modesurf_red, surfmode_red, qualacq_red, miscommercial_red, prixvente_red, prodfin_red, mes_sanscumul,
CAST('' AS xml ).value('sql:column("x") cast as xs:decimal ?', 'decimal(24,12)') AS x, CAST('' AS xml ).value('sql:column("y") cast as xs:decimal ?', 'decimal(24,12)') AS y,
CAST('' AS xml ).value('sql:column("x_l2e") cast as xs:decimal ?', 'decimal(24,12)') AS x_l2e, CAST('' AS xml ).value('sql:column("y_l2e") cast as xs:decimal ?', 'decimal(24,12)') AS y_l2e, zus, zfu, iris, nqp, qp, ril,
comazus, comazfu, comanqp, comaqp
FROM DataXYZ.dbo.DataXYZ_GeoLoc_Input
When I do a SELECT on this view for year 2015, "x" column has data but "y" column has "NULL" value:
In fact, for year 2015, "y" column has a "NULL" value for each row:
"x" an "y" value are handled the same way.
Quite strange... :blink:
Any idea of what could be the problem ?
Thanks in advance.
Regards,
Steph.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

I don't have an answer right now but I HAD to fix that awful Word Wrapped SQL you'd pasted, so that someone else doesn't have to:

SELECT intras_annee,

LEFT(siret, 9) AS intras_siren,

siret + ident_int AS intras_cle_id,

CAST(depcom_red + ISNULL(LTRIM(RTRIM(iris)) + REPLICATE('0', 4 - LEN(LTRIM(RTRIM(iris)))), REPLICATE('0', 4)) AS varchar(9)) AS intras_iris,

CASE WHEN LEFT(depcom_red, 2) = '97'

THEN LEFT(depcom_red, 3) ELSE LEFT(depcom_red, 2) END AS intras_dep,

ident_org,

siret,

rs,

cat_org,

typeconst_red,

nbpiece_red,

construct_red,

conv_red,

numconv_red,

CASE WHEN datconv_red IS NULL THEN NULL

WHEN LTRIM(rtrim(datconv_red)) = '' THEN NULL

ELSE RIGHT(datconv_red, 4) + '-' + SUBSTRING(datconv_red, 4, 2) + '-' + LEFT(datconv_red, 2) END AS datconv_red,

rsexpro_red,

siretexpro_red,

finan_red,

finanautre_red,

cus_red,

identges_red,

ident_int,

ident_rep,

sru_expir_red,

sru_alinea_red,

reg,

lib_reg,

dep,

lib_dep,

epci,

libepci,

depcom_red,

lib_depcom,

codepostal_red,

numvoie_red,

indrep_red,

typvoie_red,

nomvoie_red,

numappt_red,

numboite_red,

esc_red,

couloir_red,

etage_red,

complident_red,

entree_red,

bat_red,

immeu_red,

complgeo_red,

lieudit_red,

zus_red,

loyerprinc_red,

loyeracc_red,

contrib_red,

CAST('' AS xml ).value('sql:column("loymoy") cast as xs:decimal ? ', 'decimal(7,3)') AS loymoy,

loyermaxapl_red,

loyermaxcus_red,

locat_red,

mode_red,

bail_red,

remlocdate_red,

contreslog_red,

duree_vacance,

codsegpatrim_red,

libsegpatrim_red,

droit_red,

patrimoine_red,

origine_red,

sortiepatrim_red,

newlogt_red,

oldlogt_red,

dpedate_red,

dpeenergie_red,

dpeserre_red,

surfhab_red,

modesurf_red,

surfmode_red,

qualacq_red,

miscommercial_red,

prixvente_red,

prodfin_red,

mes_sanscumul,

CAST('' AS xml ).value('sql:column("x") cast as xs:decimal ?', 'decimal(24,12)') AS x,

CAST('' AS xml ).value('sql:column("y") cast as xs:decimal ?', 'decimal(24,12)') AS y,

CAST('' AS xml ).value('sql:column("x_l2e") cast as xs:decimal ?', 'decimal(24,12)') AS x_l2e,

CAST('' AS xml ).value('sql:column("y_l2e") cast as xs:decimal ?', 'decimal(24,12)') AS y_l2e,

zus,

zfu,

iris,

nqp,

qp,

ril,

comazus,

comazfu,

comanqp,

comaqp

FROM DataXYZ.dbo.DataXYZ_GeoLoc_Input

Edit: A tab was misaligned... >_<

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 2

Thanks ! 🙂

Sorry, it's a "copy/paste" from the horrible editor of "SQL Server 2014 Management Studio".

Did not find any way to make it respect indentation. 🙁

Steph.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You could certainly format the code yourself or use a tool like poorsql.com

On the other hand, we need DDL and sample data in a consumable format. Read the links in my signature to know how to get them.

Also, I can't see your images, so those don't help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76944 More actions · Answer 4

I have to ask WHY are you doing your conversions through XML?

The problem is that column Y is in scientific notation, and the xml does not recognize scientific notation as being valid for the decimal data type.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76944 More actions · Answer 5

As a quick test, I decided to compare doing a direct T-SQL CAST versus using XML to do the CAST. Also, the XML is essentially doing a double cast: once within the XML and once to get the value from the XML, so I added a case without the internal cast. Here are the results.

Using SQL CAST()

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 27 ms.

Using XML cast as and value

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 119 ms.

Using XML value; no cast as

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 104 ms.

As you can see, the direct T-SQL cast is about 5 times more efficient than going through the double XML cast and about 3.8 times more efficient than going through a single XML cast.

And here is the code for the test.

PRINT 'Using SQL CAST()';

WITH E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)

)

, cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E a, E b, E c, E d

)

SELECT @i = CAST(n AS DECIMAL(24, 12))

FROM cteTally

;

PRINT 'Using XML cast as and value';

WITH E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)

)

, cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E a, E b, E c, E d

)

SELECT @i = CAST('' AS XML).value('sql:column("n") cast as xs:decimal ?', 'decimal(24, 12)')

FROM cteTally

;

PRINT 'Using XML value; no cast as';

WITH E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)

)

, cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E a, E b, E c, E d

)

SELECT @i = CAST('' AS XML).value('sql:column("n")', 'decimal(24, 12)')

FROM cteTally

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 6

In fact, I'm not the author of the script of this view and I actually don't know why XML was used for conversion.

There is perhaps a good reason that I ignore (I'm new with ETL tasks).

But I agree with you, it's quite strange.

I'll test tomorrow a classical CAST and try to find a guy who worked with the author of this view.

So something like that should work ?

CAST(y AS DECIMAL(24, 12)) as y

Even if "y" is in scientific notation ?

When XML conversions should be generally used ?

Thanks for your help. 🙂

Steph.

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 7

Nobody can see images I insert in my post or just Luis ???

Thom A SSC Guru Points: 99126 More actions · Answer 8

polytropic2310 (11/23/2016)
Nobody can see images I insert in my post or just Luis ???

We can see them, but pictures don't help us with what the data looks like. If I sent you a picture of a car, it would tell you nothing about how the engine works, you'd need schematics. The same is true here, we need the DDL and sample data.

P.s. I'm surprised the SSMS 2014 gave a view back like that. SSMS puts each field on a new line, left commans (ewwww), and wraps with square brackets. Your DDL did none of that. You'll find that almost everyone here probably makes heavy use of SSMS, as it's a very good tool. it's not "horrible" at all.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 9

Hi!

drew.allen (11/23/2016)
I have to ask WHY are you doing your conversions through XML?
Drew

Finally, I had the answer from a guy that knows the history of the databases and associated scripts.

XML conversions were necessary with SQL Server 2008.

With SQL Server 2014, I replaced them with standard CAST instructions and it works well. 🙂

Thom A (11/24/2016)
If I sent you a picture of a car, it would tell you nothing about how the engine works

Pictures were just to show the dark smoke exhausting from the pipe. 😉

But you're right, necessary buf not enough to solve the problem.

Thanks to all.

Steph.