Hilfe bei UNION-Fehler in Funktion

[Hilmar Sackmann]

Hallo,

ich habe folgendes Problem mit einer selbst geschriebenen Funktion in MS SQL-Server 2000, die von einem Visualisierungssystem aus aufgerufen werden soll:

1. Funktion Get_Stat_Verbrauch liefert den Verbrauch pro Auftrag in einem vom Benutzer einzugebenden Zeitraum. Das ganze funktioniert recht gut...

CREATE FUNCTION [dbo].Get_Stat_Verbrauch 
(
@StartDatum VARCHAR(10),@Startzeit varchar(8), @EndDatum VARCHAR(10), @Endzeit varchar(8)
)
RETURNS TABLE  AS RETURN
SELECT top 100 PERCENT 
  L.[order-no],
  L.sector,
  [DBO].[Get_Auftragsstart](T.[order-no]) AS [loading-time],
  L.[last writing time] AS [unloading-time],
  CASE WHEN L.basin=0 THEN '-' ELSE 'X' END AS basin,
  CASE WHEN L.frame=0 THEN '-' ELSE 'X' END AS frame,
  L.[base-coat color-no],
  L.[base-coat prog-no],
  L.[base-coat temp],
  L.[base-coat used color],  
  L.[base-coat used accelerator],  
  L.[base-coat used thinner],    
  L.[final-coat color-no],
  L.[final-coat prog-no],
  L.[final-coat temp],
  L.[final-coat used color],  
  L.[final-coat used accelerator],  
  L.[final-coat used thinner],      
  L.[temp oven]
  FROM LWDatenArchiv AS L
  JOIN Get_LWDatenArchiv_TimeExtract(@StartDatum, @Startzeit, @EndDatum, @Endzeit) AS T
  ON (L.[order-no]= T.[order-no]) AND (l.Id = T.Id)
  WHERE (L.sector=1) -- => Unloading
  ORDER BY L.[last writing time]

2. Funktion Get_ColumnTitlesFromTable liefert für den Export in eine Text-Datei die zugehörigen Spaltentitel. Der eigentliche Select liefert leider alle zugehörigen Spalten nur zeilenweise und nicht wie praktischer in einer Zeile als separate Spalten. Deswegen die Krücke darunter...

CREATE  FUNCTION [dbo].Get_ColumnTitlesFromTable (@TableName VARCHAR(100)
 )  
RETURNS TABLE  AS RETURN
   -- Liefert alle Spaltentitel der angegeben. Tabelle/Funktion
/* Ergebnis kommt leider in mehreren Zeilen 
   statt in einer Zeile mit mehreren Spalten
	SELECT  column_name 
	FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
	WHERE table_name = @TableName
	ORDER BY ordinal_position
  deswegen noch manueller Umweg
*/
	SELECT 
	'order-no'  AS [order-no], 
	'sector' AS [sector],
	'loading-time' AS [loading-time],
	'unloading-time' AS [unloading-time],
	'basin' AS [basin],
	'frame' AS [frame],
	'base-coat color-no' AS [base-coat color-no],
	'base-coat prog-no' AS [base-coat prog-no],
	'base-coat temp' AS [base-coat temp],
	'base-coat used color' AS [base-coat used color],
	'base-coat used accelerator' AS [base-coat used accelerator],
	'base-coat used thinner' AS [base-coat used thinner],
	'final-coat color-no' AS [final-coat color-no],
	'final-coat prog-no' AS [final-coat prog-no],
	'final-coat temp' AS [final-coat temp],
	'final-coat used color' AS [final-coat used color],
	'final-coat used accelerator' AS [final-coat used accelerator],
	'final-coat used thinner' AS [final-coat used thinner],
	'temp oven' AS [temp oven]

3. Funktion Expfile_Stat_Verbrauch liefert als erstes die Spaltentitel und anschließend die eigentlichen Dateninhalte. Diese Funktion wird mit folgendem Statement (vom Visualisierungssystem aus) aufgerufen:

bcp "SELECT * FROM mydb..[Expfile_Stat_Verbrauch] ('01/01/08','00:00:00', '02/01/08', '23:59:00')" QUERYOUT "C:\Temp\Exp.TXT" -c -q -T /t;

Kopfzerbrechen bereiten mir die markierten Zeilen: Sobald ich diese hineinnehme, kann die Funktion nicht mehr kompiliert werden. Und es gibt folgende Fehlermeldung:

Server: Nachr.-Nr. 446, Schweregrad 16, Status 9, Prozedur Expfile_Stat_Verbrauch, Zeile 7
Cannot resolve collation conflict for UNION operation.

CREATE   FUNCTION Expfile_Stat_Verbrauch (
)
RETURNS TABLE  AS RETURN 
SELECT TOP 100 PERCENT 'Columntitles:' AS DT, 
[order-no], 
[sector],
[loading-time],
[unloading-time],
[basin],
[frame],
--[base-coat color-no],
[base-coat prog-no],
[base-coat temp],
[base-coat used color],
[base-coat used accelerator],
[base-coat used thinner],
--[final-coat color-no],
[final-coat prog-no],
[final-coat temp],
[final-coat used color],
[final-coat used accelerator],
[final-coat used thinner],
[temp oven]
FROM [WWALMDB].[dbo].[Test]()
UNION
SELECT TOP 100 PERCENT 'Data:' AS DT, 
CONVERT(VARCHAR,[order-no]) AS [order-no], 
CONVERT(VARCHAR,[sector]) AS [sector],
CONVERT(VARCHAR,[loading-time]) AS [loading-time],
CONVERT(VARCHAR,[unloading-time]) AS [unloading-time],
[basin],
[frame],
--[base-coat color-no], ßhier gibt’s Probleme
CONVERT(VARCHAR,[base-coat prog-no]) AS [base-coat prog-no],
CONVERT(VARCHAR,[base-coat temp]) AS [base-coat temp],
CONVERT(VARCHAR,[base-coat used color]) AS [base-coat used color],
CONVERT(VARCHAR,[base-coat used accelerator]) AS [base-coat used accelerator],
CONVERT(VARCHAR,[base-coat used thinner]) AS [base-coat used thinner],
--[final-coat color-no], ßhier gibt’s Probleme
CONVERT(VARCHAR,[final-coat prog-no]) AS [final-coat prog-no],
CONVERT(VARCHAR,[final-coat temp]) AS [final-coat temp],
CONVERT(VARCHAR,[final-coat used color]) AS [final-coat used color],
CONVERT(VARCHAR,[final-coat used accelerator]) AS [final-coat used accelerator],
CONVERT(VARCHAR,[final-coat used thinner]) AS [final-coat used thinner],
CONVERT(VARCHAR,[temp oven]) AS [temp oven]
FROM [WWALMDB].[dbo].[Get_Stat_Verbrauch]('1/1/0', '00:00:00', '2/21/8','23:59:59')
ORDER BY DT ASC

4. Die zu Grunde liegende Tabelle hat folgenden Aufbau:

CREATE TABLE [LWDatenArchiv] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[carrier-no] [int] NOT NULL ,
	[sector] [int] NULL ,
	[last readingstation] [int] NULL ,
	[last writing time] [datetime] NULL ,
	[basin] [bit] NULL ,
	[frame] [bit] NULL ,
	[destination lift 1] [bit] NULL ,
	[destination lift 2] [bit] NULL ,
	[through lift 1] [bit] NULL ,
	[base-coat work] [bit] NULL ,
	[base-coat 2nd run] [bit] NULL ,
	[base-coat cycles] [int] NULL ,
	[base-coat color-no] [varchar] (6) COLLATE Latin1_General_CI_AS NULL ,
	[base-coat prog-no] [int] NULL ,
	[base-coat speed] [int] NULL ,
	[final-coat work] [bit] NULL ,
	[final-coat 2nd run] [bit] NULL ,
	[final-coat cycles] [int] NULL ,
	[final-coat color-no] [varchar] (6) COLLATE Latin1_General_CI_AS NULL ,
	[final-coat prog-no] [int] NULL ,
	[final-coat speed] [int] NULL ,
	[order-no] [numeric](10, 0) NULL ,
	[base-coat temp] [real] NULL ,
	[base-coat used color] [real] NULL ,
	[base-coat used accelerator] [real] NULL ,
	[base-coat used thinner] [real] NULL ,
	[final-coat temp] [real] NULL ,
	[final-coat used color] [real] NULL ,
	[final-coat used accelerator] [real] NULL ,
	[final-coat used thinner] [real] NULL ,
	[temp oven] [real] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]

Bin für jeden Hinweis dankbar....

[hwoess]

Um mich da jetzt im Detail durchzulesen fehlt mir gerade die Zeit, aber vielleicht hift dir ja mein Hinweis weiter: irgendwo gibt es Felder, die unterschiedliche COLLATION haben. Und wenn man die jetzt über ein UNION in eine gemeinsame Spalte stellen will, müsste sich der SQL-Server ja für eine von beiden COLLATION entscheiden, was er aber nicht tut, sondern stattdessen eine Fehlermeldung generiert.
Du kannst jetzt entweder einmal alle betroffenen Tabellen/Felder durchgehen und schauen, ob die unterschiedliche COLLATION's haben, dazu natürlich auch schauen, ob auch die Default-COLLATION des SQL-Server dazu passt. Oder bei jedem Zeichenstring-Feld die COLLATION seperat dazu angeben:

select feld1 COLLATE Latin1_General_CI_AS from tabelleX
UNION
select feld2 COLLATE Latin1_General_CI_AS from tabelleY

Dasselbe kann auch bei einem Vergleich zweier unterschiedlicher Felder passieren, dann geht's so:
select * from tabelle where feld1 COLLATE Latin1_General_CI_AS > feld2 COLLATE Latin1_General_CI_AS


bye,
Helmut

[O. Helper]

Hallo Hilmar,

ganz schön viel Code....

Der Meldung nach stimmt die COLLATION der varchar-Feld aus der Tabelle TEST und der Rückgabe von GET_STAT_VERBRAUCH nicht überein, hier solltest Du mal kontrollieren, was da eingestellt ist.

Ansonsten kannst Du es auch noch im SELECT CASTen, versuch mal folgendes:

....[frame],
[base-coat color-no] COLLATE Latin1_General_CI_AS,
[base-coat prog-no], ....

Olaf

[Hilmar Sackmann]

WOW, das "COLLATE Latin1_General_CI_AS" hat's gebracht!!!!
Danke Euch beiden Olaf und Helmut!!!


Nur dass, ich's auch begreife:
Eine Spalte vom Typ "[varchar] (6) COLLATE Latin1_General_CI_AS" in der 'TEXT' steht ist für UNION nicht das gleiche wie der 'TEXT' aus meinem ersten SELECT???


Hilmar

[O. Helper]

Hallo Hilmar,

ob es das gleiche ist, definierst Du eben über die Collation, wobei die in dem Fall folgende Bedeutung hat:
CI = Case Insensitive
AS = Akzent sensitive
also ist Groß-/Kleinschreibung gleichwertig und somit "TEXT", "Text" und "text" das gleiche.

Bei Latin1_General_CS_AS hingegen ist es
CS = Case Sensitve
und da sind "TEXT", "Text" und "text" 3 unterschiedliche Werte.
Wenn Du unterschiedliche COLLATIONs hast, weiß der SQL Server nicht, wie sortieren oder wie zu vergleichen (z.B. bei JOINs).

Olaf

[Hilmar Sackmann]

Danke ... jetzt dämmerts...

Hat hier einer sonst noch eine Lösung für mein Spalten-statt-Zeilen-Problem aus dem Punkt 2. ???

Hilmar

[hwoess]

Kannst ja mal das im QueryAnalizer (oder ähnlichem) ausprobieren.

declare @txt varchar(4000)

	SELECT  @txt = IsNull(@txt + ', ','') + '''' + column_name + ''' as ' + column_name
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE table_name = @tabellenname
	ORDER BY ordinal_position

exec('select ' + @txt)

In eine UDF lässt sich das nicht verpacken, da man da ja schon beim CREATE die Spalten angeben muss, die aber bei wechselnder Tabelle wahrscheinlich ganz unterschiedlich sind. Aber vielleicht liefert es dir eine Idee und du bringst das irgendwie anders unter.

bye,
Helmut

[Hilmar Sackmann]

Vielen Dank Helmut!!!

Das sieht richtig gut aus!!! Ich muss zwar noch ein wenig basteln.... Aber ein Gedanke ist da... Dank Dir!