[MSSQL] NULL waarden negeren op column niveau per row

[Kamikazi]

Om ervoor te zorgen dat de software van de labelprinter de adresstickers netjes kan printen, is het de bedoeling dat deze software zijn adresgegevens uit de Microsoft SQL database kan halen.

Er is daarvoor een view gemaakt en wel de volgende:

code:

SELECT     
        AL2.Name AS Deel1,
        CASE AL2.[Name 2] WHEN '' THEN NULL ELSE AL2.[Name 2] END AS Deel2, 
        CASE AL2.Address WHEN '' THEN NULL ELSE AL2.Address END AS Deel3, 
        CASE AL2.[Address 2] WHEN '' THEN NULL ELSE AL2.[Address 2] END AS Deel4, 
        CASE AL2.[Adres 3] WHEN '' THEN NULL ELSE AL2.[Adres 3] END AS Deel5, 
        CASE AL2.[Adres 4] WHEN '' THEN NULL ELSE AL2.[Adres 4] END AS Deel6, 
        CASE AL3.Name WHEN 'United Kingdom' THEN AL2.City ELSE AL2.[Post Code] + ' ' + AL2.City END AS Deel7, 
        CASE AL4.Name WHEN '' THEN NULL ELSE AL4.Name END AS Deel8, 
        CASE AL3.Name WHEN 'United Kingdom' THEN AL2.[Post Code] END AS Deel9, 
        AL3.Name AS Deel10
FROM         dbo.[NavDB$Sales Invoice Line] AS AL1 INNER JOIN
                      dbo.[NavDB$Contact] AS AL2 LEFT OUTER JOIN
                      dbo.[NavDB$Country_Region] AS AL3 ON AL2.[Country_Region Code] = AL3.Code LEFT OUTER JOIN
                      dbo.[NavDB$Territory] AS AL4 ON AL2.[Territory Code] = AL4.Code ON AL1.[Sell-to Customer No_] = AL2.No_ INNER JOIN
                      dbo.[NavDB$Sales Invoice Header] AS AL5 ON AL1.[Document No_] = AL5.No_
GROUP BY AL1.[Sell-to Customer No_], AL1.[Document No_], AL2.Name, AL2.[Name 2], AL2.Address, AL2.[Address 2], AL2.City, AL2.[Adres 3], AL2.[Adres 4], AL2.Huisnummer, 
                      AL2.[Huisnummer toevoeging], AL3.Name, AL4.Name, AL5.[Payment Method Code], AL5.timestamp, AL2.[Post Code]
HAVING      (AL3.Name IS NOT NULL)
ORDER BY AL5.timestamp DESC

Ter illustratie, na het uitvoeren de view worden de volgende resultaten verkregen:

code:

Deel1    Deel2   Deel3              Deel4     Deel5    Deel6    Deel7   Deel8   Deel9   Deel10
Naam X  NULL    189 Albany Road NULL    NULL    NULL    CHATHAM NULL    ME4 5DW United Kingdom
Naam Y  NULL    Marx-Strasse 30 NULL    NULL    NULL    04649 DOHNA NULL    NULL    Deutschland
Naam B  NULL    14 Solent Drive Barton On Sea   NULL    NULL    NEW MILTON  Hampshire   BH29 7XX    United Kingdom
Naam Z  NULL    Extraweg 72        Steinbach    NULL    NULL    02351 Moritzburg    NULL    NULL    Deutschland

Het nadeel van de labelsoftware is dat het pakket niet de regels met een waarde NULL negeert (en dus alle adresgegevens netjes onder elkaar zet), maar dat hij lege regels print (dus als resultaat krijg je de waarde van Deel1, dan een lege regel, dan Deel3 etc etc)

Normaliter zou ik de resultaten van een query in een tijdelijke tabel inserten en dan via een ALTER TABLE de null waarden laten vervallen, alleen is er hier sprake van dat elke keer een andere column leeg is, dat verschilt per rij.

Ik vraag me dus af of het mogelijk is om per row te kijken of de column waarde leeg is en indien deze leeg is, de waarde van column erna over te nemen (mits die niet leeg is).

In het voorbeeld zou dus op rij 1 Deel3 op de plek van Deel2 komen, op de plek van Deel3 zou Deel7 komen en op de plek van Deel4 zou Deel9 komen en afsluitend op de plek van Deel5 zou Deel10 komen.
In rij 2 zou Deel3 op de plek van Deel2 komen, op plek Deel3 zou Deel7 komen en op de plek van Deel4 zou Deel10 komen.
En dat dan zo voor elke rij.

Iemand een tip?

[whoami]

COALESCE ?

Hmm, geen COALESCE dus; ik dacht eerst dat er telkens juist één waarde ingevuld was, maar dat is niet het geval.
ISNULL dan maar ?

Maareh, hoe stuur je die gegevens naar je printer ? SQL Is er om gegevens op te halen / te manipuleren , maar niet om te formatteren .
Eigenlijk zou dit dus niet de taak van je SQL query mogen zijn.

[ Voor 127% gewijzigd door whoami op 29-05-2008 15:31 ]

[sig69]

Hoop coalesce vraagjes de laatste tijd...
Edit: ah jammer, geen coalesce

[ Voor 22% gewijzigd door sig69 op 29-05-2008 15:30 ]

[Kamikazi]

whoami schreef op donderdag 29 mei 2008 @ 15:29:
Maareh, hoe stuur je die gegevens naar je printer ? SQL Is er om gegevens op te halen / te manipuleren , maar niet om te formatteren .
Eigenlijk zou dit dus niet de taak van je SQL query mogen zijn.

Het software pakket van de printer (het gaat hier om een Zebra thermische printer) maakt de koppeling naar de database, haalt de gegevens op en verstuurt vervolgens het printcommando naar de printer.

[RobIII]

Die tabelnamen

NavDB$
AL2.No_
NavDB$Sales Invoice Header
Document No_
Sell-to Customer No_


Het makkelijkst is misschien eerst alles casten naar een enkele varchar waabij je de fields separate met een delimiter (pipe ofzo), daarna (while instr(dubbele pipes)>0) replacen met enkele pipe en dat dan weer opslitsen in losse fields ofzo

[edit]
Hmmm, ik bedenk net dat het met een UDF wel eens grappig zou kunnen werken...

[ Voor 13% gewijzigd door RobIII op 29-05-2008 15:37 ]

[sig69]

Ik weet ook nog een ranzige:

code:

select coalesce(Deel1, Deel2, Deel3, Deel4, Deel5, ...),
coalesce( Deel2, Deel3, Deel4, Deel5, ...),
coalesce(  Deel3, Deel4, Deel5, ...),
coalesce( Deel4, Deel5, ...)...
etc...

Dan krijg je wel null-values op het einde, weet niet wat je printer daar mee doet

[Kamikazi]

RobIII schreef op donderdag 29 mei 2008 @ 15:35:
Die tabelnamen

NavDB$
AL2.No_
NavDB$Sales Invoice Header
Document No_
Sell-to Customer No_

Komen straight uit Microsoft Dynamics NAV

RobIII schreef op donderdag 29 mei 2008 @ 15:35:

Het makkelijkst is misschien eerst alles casten naar een enkele varchar waabij je de fields separate met een delimiter (pipe ofzo), daarna (while instr(dubbele pipes)>0) replacen met enkele pipe en dat dan weer opslitsen in losse fields ofzo

[edit]
Hmmm, ik bedenk net dat het met een UDF wel eens grappig zou kunnen werken...

sig69 schreef op donderdag 29 mei 2008 @ 15:38:
[/code]
Dan krijg je wel null-values op het einde, weet niet wat je printer daar mee doet

Daar doet hij niets mee

Ik ga even in het UDF verhaal duiken.

[_js_]

sig69 schreef op donderdag 29 mei 2008 @ 15:38:
Ik weet ook nog een ranzige:

code:

select coalesce(Deel1, Deel2, Deel3, Deel4, Deel5, ...),
coalesce( Deel2, Deel3, Deel4, Deel5, ...),
coalesce(  Deel3, Deel4, Deel5, ...),
coalesce( Deel4, Deel5, ...)...
etc...

Dan krijg je wel null-values op het einde, weet niet wat je printer daar mee doet

Als Deel1 null is, dan krijg je twee keer Deel2 terug.

[d00d]

Dit is een leuk probleem dat kan worden opgelost met de pivot en unpivot statements in SQL Server 2005. In SQL Server 2008 wordt dit nog makkelijker door gebruik te maken van sparse columns maar ik ga even uit van de 2005 versie.

Je kunt jouw dataset als reeds ge-pivot zien die we eerst gaan unpivotten en vervolgens weer gaan pivotten (sorry voor het lelijke Nederlands hier ).

Het mooie, in dit geval, van het unpivot statement is dat NULL waarden automatisch worden verwijderd. Je kunt dit checken door regel 3, 4 en 5 uit te voeren als los statement.

Ik ga er in mijn code even vanuit dat de view 'adres' heet, dit zul je dus moeten aanpassen in jouw code.

SQL:

select [1] deel1,[2] deel2,[3] deel3,[4] deel4,[5] deel5,[6] deel6,[7] deel7,[8] deel8,[9] deel9,[10] deel10
from (
    select RowID, Deel, ROW_NUMBER() OVER (partition by RowID ORDER BY RowID) AS DeelNr
    from ( select ROW_NUMBER() OVER (ORDER BY deel1) AS RowID, deel1, deel2, deel3, deel4, deel5, deel6, deel7, deel8, deel9, deel10 from adres ) p
    unpivot (Deel FOR D IN (deel1, deel2, deel3, deel4, deel5, deel6, deel7, deel8, deel9, deel10 )) q
) r
pivot (max(Deel) for DeelNr IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) s;

sql hogeschool

[sig69]

_js_ schreef op donderdag 29 mei 2008 @ 17:16:
[...]

Als Deel1 null is, dan krijg je twee keer Deel2 terug.

Je hebt helemaal gelijk...

[Kamikazi]

d00d schreef op vrijdag 30 mei 2008 @ 00:40:
Dit is een leuk probleem dat kan worden opgelost met de pivot en unpivot statements in SQL Server 2005. In SQL Server 2008 wordt dit nog makkelijker door gebruik te maken van sparse columns maar ik ga even uit van de 2005 versie.

Met deze suggestie en een beetje Google heb ik dit probleem kunnen oplossen. Helemaal geweldig, bedankt!