SQL Server (και άλλες databases)

Απ: Migration Ms Access function σε SQL 2008

neoklis — Mon, 20 Dec 2010 05:14:23 GMT

Φυσικά και θα το κάνω, απλά θέλω τον χρόνο μου για να δοκιμάσω όλα αυτά του spaceman..

Απ: Migration Ms Access function σε SQL 2008

KelMan — Sun, 19 Dec 2010 02:29:17 GMT

neoklis, πάτα και το "Σημείωση ως Απάντησης" στις απαντήσεις που σε βοήθησαν...

Απ: Migration Ms Access function σε SQL 2008

neoklis — Fri, 17 Dec 2010 23:10:28 GMT

spaceman:

Οχι απαραιτητα. Σε καποιους αυτου του ειδους η προσεγγισεις "ξενιζουν".

Κατάλαβα τι λες.., όχι ότι σε μένα δεν μου έπεσε βαριά η προσέγγιση σου... Αισθάνθηκα σαν να μου πετάνε crt οθόνη από τον 4ο και γώ πρέπει να την πιάσω. Ωστόσο δεν είμαι από τους ανθρώπους που τα παρατάνε...λίγη προσπάθεια και κάτι νομίζω κατάλαβα...

spaceman:

Στην δικη σου περιπτωση, αν εχεις περισσοτερους απο 80+κατι διαφορετικους συνδυασμους Operation_No & Work_Center & WorkGroupConfirm & WorkGroupPrevConfirm ανα SD_Key, τοτε ο πινακας ProductionMatrixSAP δεν επαρκει για να περιλαβει ολες τις εγγραφες (οι οποιες μετατρεπονται σε στηλες) και θα πρεπει να δημιουργησεις καινουριες στηλες στο πινακα κοκ.

οκ..

για τα υπόλοιπα θα πάρω τον χρόνο μου να τα διαβάσω..

Σε ευχαριστώ

Απ: Migration Ms Access function σε SQL 2008

spaceman — Fri, 17 Dec 2010 22:44:48 GMT

Δεν βλέπω κάτι το παράξενο... Δεν ξέρω όμως αν θα έπρεπε...

Οχι απαραιτητα. Σε καποιους αυτου του ειδους η προσεγγισεις "ξενιζουν". Ωστοσο εχω δει παρομοιες προσεγγισεις σε large scale dbs και υπο ελεγχομενες συνθηκες εκαναν την δουλεια τους μια χαρα.
Στην δικη σου περιπτωση, αν εχεις περισσοτερους απο 80+κατι διαφορετικους συνδυασμους Operation_No & Work_Center & WorkGroupConfirm & WorkGroupPrevConfirm ανα SD_Key, τοτε ο πινακας ProductionMatrixSAP δεν επαρκει για να περιλαβει ολες τις εγγραφες (οι οποιες μετατρεπονται σε στηλες) και θα πρεπει να δημιουργησεις καινουριες στηλες στο πινακα κοκ.

Violation of PRIMARY KEY constraint 'PK_ProductionMatrixSAP'. Cannot insert duplicate key in object 'dbo.ProductionMatrixSAP'.

Στο παραδειγμα με τον cursor που ανεφερες παραπανω, ειναι λογικο να εχεις primary key violation:
Στo "SELECT SAP_PO.SD_key, Max(SAP_Confirmations.Posting_Date) AS PostDate, SAP_Confirmations ......" τα δεδομενα επιστρεφονται grouped by (εκτος του SD_key) SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm
και οπως φαινεται και απο το δειγμα των δεδομενων σου

 SD_key			PostDate		         Operation_No
1200019825-001-21028018863	2006-03-15 00:00:00.000	0060	    
1200019825-001-21028018863	2006-04-07 00:00:00.000	0060	    
1200019825-001-21028018863	2006-04-07 00:00:00.000	0150	    
1200019825-001-21028018863	2006-04-07 00:00:00.000	0195	    
1200019825-001-21028018863	2006-04-25 00:00:00.000	0500

Δεν επιστρεφονται μοναδικες τιμες SD_Key (αλλα επιστρεφονται μοναδικοι συνδυασμοι SD_key & Operation_No & Work_Center & WorkGroupConfirm & WorkGroupPrevConfirm)
Οποτε, οταν κανεις iteration με τον cursor, η πρωτη fetched εγγραφη του SD_Key δημιουργειται κανονικα στον ProductionMatrixSAP , η δευτερη ομως οχι, εφοσον η τιμη του SD_Key ειναι ιδια με την προηγουμενη (pk violation).

To "INSERT INTO ProductionMatrixSAP (SD_key, MaxOP, OP01 .......SELECT SD_Key, MAX(row_sdk), /* first row column set*/ MAX(CASE row_sdk WHEN 1 THEN Operation_No END), ....) AS data GROUP BY SD_Key"
επιστρεφει μια κ μονο μια εγγραφη ανα SD_Key οποτε "θεωρητικα" δεν θα επρεπε να επιστρεφει PK violation σφαλμα.
Πιθανες περιπτωσεις:
1. Υπαρχει trigger for insert on ProductionMatrixSAP ? Ισως ο trigger να δημιουργει σε καποια στιγμη διπλο-εγγραφες κ να τις καθαριζει στην συνεχεια? (drop pk...insert records..trigger duplicates...trigger cleans up..recreate pk works)
2. ANSI_WARNINGS is set to OFF και τo μηκος καποιων τιμων SD_Key που επιστρεφει το "SELECT SD_Key, MAX(row_sdk),....." ειναι μεγαλυτερο απο το μηκος του πεδιου ProductionMatrixSAP.SD_Key. Σε αυτην την περιπτωση οι επιπλεον χαρακτηρες SD_Key "κοβονται σιωπηλα" και εμφανιζονται διπλο-εγγραφες.
3. Extreme case, something could be wrong with parallelism. Αυτη η περιπτωση δεν ειναι λογικη αλλα ποτε δεν ξερεις....
BOL: In a parallel query execution plan, the insert, update, and delete operators are executed serially. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database. Για να αποκλεισεις αυτην την "φανταστικη" περιπτωση, αλλαξε το statement σε "......) AS data
GROUP BY SD_Key
ORDER BY SD_Key
OPTION (MAXDOP 1)"

Σε καθε περιπτωση, πρεπει να βεβαιωθεις οτι δεν υπαρχουν διπλες εγγραφες στο SELECT statement:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
	DROP TABLE #temp
END


SELECT SD_Key, MAX(row_sdk) AS row_sdk, MAX(CASE row_sdk WHEN 1 THEN Operation_No END) AS OP01 
INTO #temp
FROM 
(
	SELECT	SAP_PO.SD_key, 
		SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, 
		Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm,
		MAX(SAP_Confirmations.Posting_Date) AS PostDate, SUM(SAP_Confirmations.Conf_qty_KG) AS KG, SUM(SAP_Confirmations.Conf_Pieces) AS ST, 
		ROW_NUMBER() OVER(PARTITION BY SAP_PO.SD_key ORDER BY SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center) AS row_sdk
	FROM SAP_Confirmations 
		INNER JOIN SAP_PO ON SAP_Confirmations.PO = SAP_PO.PO
		INNER JOIN Work_Groups ON SAP_Confirmations.MRP_Controller = Work_Groups.MRP_Controller AND SAP_Confirmations.Work_Center = Work_Groups.WorkCenter
	GROUP BY SAP_PO.SD_key, SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm 
) AS data
GROUP BY SD_Key
ORDER BY SD_Key


--check if there are duplicate keys
--if you get results here, there are duplicates
SELECT 'there are duplicates' AS msg, SD_Key, COUNT(*) AS counter
FROM #temp
GROUP BY SD_Key
HAVING(COUNT(*) > 1)


IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
	DROP TABLE #temp
END

--HTH--

Απ: Migration Ms Access function σε SQL 2008

neoklis — Fri, 17 Dec 2010 19:57:41 GMT

spaceman:
(the following looks weird, or not?):

Δεν βλέπω κάτι το παράξενο... Δεν ξέρω όμως αν θα έπρεπε...

Θέλω να ρωτήσω και κάτι άλλο το οποίο το έχω συναντήσει αρκετές φορές αλλά δεν έχω καταλάβει για ποιό λόγο γίνεται.. π.χ. Αν εκτελέσω το παραπάνω insert into στον πίνακα ProductionMatrixSAP (ο οποίος έχει γίνει truncate) και έχει κλειδί το sd_key, μου χτυπάει το εξής μήνυμα για διπλοεγγραφές.

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_ProductionMatrixSAP'. Cannot insert duplicate key in object 'dbo.ProductionMatrixSAP'.

To selection που του ζητάω να κάνει insert είναι group by sd_key. Σαν result δεν βλέπω διπλοεγγραφές, αλλά ο SQL τις καταλαβαίνει παρόλο που δεν του ζητάω να τις περάσει. Όπως είπα το έχω ξανα αντιμετωπίσει στο παρελθόν, και σαν λύση (μπακαλίστικη αλλά δεν έχω βρει κάτι άλλο), είναι να κάνω drop το index, εκτέλεση το insert into, create index.. Στην Access δεν είχα αντιμετωπίσει ποτέ τέτοιο πρόβλημα...

Απ: Migration Ms Access function σε SQL 2008

spaceman — Fri, 17 Dec 2010 07:58:09 GMT

Δεν μπορώ να καταλάβω όμως πως το αποτέλεσμα, θα το κάνω insert σε πίνακα...

(the following looks weird, or not?):

INSERT INTO ProductionMatrixSAP
			(SD_key, MaxOP, 
			OP01, WC01, WG01, PG01, LD01, KG01, ST01, --first row column set
			OP02, WC02, WG02, PG02, LD02, KG02, ST02, --second row column set
			OP03, WC03, WG03, PG03, LD03, KG03, ST03, --third row column set
			OP04, WC04, WG04, PG04, LD04, KG04, ST04, --fourth row column set
			OP05, WC05, WG05, PG05, LD05, KG05, ST05 --,												
			--......................................
			--......................................
			--......................................			
			--OPxx, WCxx, WGxx, PGxx, LDxx, KGxx, STxx	--xx row column set		
			)
SELECT SD_Key, MAX(row_sdk),
/* first row column set*/	MAX(CASE row_sdk WHEN 1 THEN Operation_No END), MAX(CASE row_sdk WHEN 1 THEN Work_Center END), MAX(CASE row_sdk WHEN 1 THEN WorkGroupConfirm END), MAX(CASE row_sdk WHEN 1 THEN WorkGroupPrevConfirm END), MAX(CASE row_sdk WHEN 1 THEN PostDate END), MAX(CASE row_sdk WHEN 1 THEN KG END), MAX(CASE row_sdk WHEN 1 THEN ST END), 
/* second row column set*/	MAX(CASE row_sdk WHEN 2 THEN Operation_No END), MAX(CASE row_sdk WHEN 2 THEN Work_Center END), MAX(CASE row_sdk WHEN 2 THEN WorkGroupConfirm END), MAX(CASE row_sdk WHEN 2 THEN WorkGroupPrevConfirm END), MAX(CASE row_sdk WHEN 2 THEN PostDate END), MAX(CASE row_sdk WHEN 2 THEN KG END), MAX(CASE row_sdk WHEN 2 THEN ST END), 
/* third row column set*/	MAX(CASE row_sdk WHEN 3 THEN Operation_No END), MAX(CASE row_sdk WHEN 3 THEN Work_Center END), MAX(CASE row_sdk WHEN 3 THEN WorkGroupConfirm END), MAX(CASE row_sdk WHEN 3 THEN WorkGroupPrevConfirm END), MAX(CASE row_sdk WHEN 3 THEN PostDate END), MAX(CASE row_sdk WHEN 3 THEN KG END), MAX(CASE row_sdk WHEN 3 THEN ST END), 
/* fourth row column set*/	MAX(CASE row_sdk WHEN 4 THEN Operation_No END), MAX(CASE row_sdk WHEN 4 THEN Work_Center END), MAX(CASE row_sdk WHEN 4 THEN WorkGroupConfirm END), MAX(CASE row_sdk WHEN 4 THEN WorkGroupPrevConfirm END), MAX(CASE row_sdk WHEN 4 THEN PostDate END), MAX(CASE row_sdk WHEN 4 THEN KG END), MAX(CASE row_sdk WHEN 4 THEN ST END), 
/* fifth row column set*/	MAX(CASE row_sdk WHEN 5 THEN Operation_No END), MAX(CASE row_sdk WHEN 5 THEN Work_Center END), MAX(CASE row_sdk WHEN 5 THEN WorkGroupConfirm END), MAX(CASE row_sdk WHEN 5 THEN WorkGroupPrevConfirm END), MAX(CASE row_sdk WHEN 5 THEN PostDate END), MAX(CASE row_sdk WHEN 5 THEN KG END), MAX(CASE row_sdk WHEN 5 THEN ST END) --,
/*xx row column set*/	--MAX(CASE row_sdk WHEN xx THEN Operation_No END), MAX(CASE row_sdk WHEN xx THEN Work_Center END), MAX(CASE row_sdk WHEN xx THEN WorkGroupConfirm END), MAX(CASE row_sdk WHEN xx THEN WorkGroupPrevConfirm END), MAX(CASE row_sdk WHEN xx THEN PostDate END), MAX(CASE row_sdk WHEN xx THEN KG END), MAX(CASE row_sdk WHEN xx THEN ST END) --,
FROM 
(
	SELECT	SAP_PO.SD_key, 
		SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, 
		Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm,
		MAX(SAP_Confirmations.Posting_Date) AS PostDate, SUM(SAP_Confirmations.Conf_qty_KG) AS KG, SUM(SAP_Confirmations.Conf_Pieces) AS ST, 
		ROW_NUMBER() OVER(PARTITION BY SAP_PO.SD_key ORDER BY SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center) AS row_sdk
	FROM SAP_Confirmations 
		INNER JOIN SAP_PO ON SAP_Confirmations.PO = SAP_PO.PO
		INNER JOIN Work_Groups ON SAP_Confirmations.MRP_Controller = Work_Groups.MRP_Controller AND SAP_Confirmations.Work_Center = Work_Groups.WorkCenter
	GROUP BY SAP_PO.SD_key, SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm 
) AS data
GROUP BY SD_Key
ORDER BY SD_Key

Το pattern ειναι προφανες, οποτε θα μπορουσες ή να χρησιμοποιησεις dynamic sql για να δημιουργησεις το insert...select statement ή να πληκτρολογησεις ολο το insert..select statement σε μια stored procedure.

--HTH--

Απ: Migration Ms Access function σε SQL 2008

neoklis — Thu, 16 Dec 2010 23:55:33 GMT

Σε ευχαριστώ για τις παρατηρήσεις σου για τα cursors.. το πρόβλημα που είχα διορθώθηκε....

Το παράδειγμα που μου δίνεις με pivot, είναι πάρα πολύ καλό... κάθομαι και το χαζεύω.... σαν ζαβος.. Δεν μπορώ να καταλάβω όμως πως το αποτέλεσμα, θα το κάνω insert σε πίνακα... Θα το googlάρω και όλο και κάτι θα βρω..

Σε ευχαριστώ

Απ: Migration Ms Access function σε SQL 2008

spaceman — Thu, 16 Dec 2010 23:17:37 GMT

neoklis:
Ενώ γράφω τον κώδικα μου και πατάω execute για να δω τα αποτελέσματα, το execute δεν εκτελείται. Δείχνει ότι κάνει κάτι, αλλά κοκο και αυγό τίποτα... Πατάω debug βλέπω ότι δεν υπάρχει πρόβλημα.. κάνω stop debug... execute και εκτελείται μια χαρά... μήπως γνωρίζει κάποιος..;

This is a good one.
Δοκιμασε το παρακατω

declare @t table (id smallint)
insert into @t(id) values (1), (2), (3)

declare @i smallint

declare cur1 cursor for select id from @t
open cur1

select @@FETCH_STATUS as fetchstatus

while @@FETCH_STATUS = 0
begin
	fetch next from cur1 into @i
	select @i AS i
end

close cur1
deallocate cur1

Την πρωτη φορα που το εκτελεις παιρνεις τα παρακατω αποτελεσματα:
fetchstatus = 0
i = 1
i = 2
i = 3
i = 3 (duplicate return)

Εκτελεσε το statement ξανα στην ιδια connection και αυτο που επιστρεφει ειναι:
fetchstatus = -1
BOL:
Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection. (not completely true though)

To σωστο θα ηταν να γραψεις τον κερσορα σου καπως ετσι:

open s_cursor

FETCH NEXT FROM s_cursor
into @sd_key,@PostDate,@OperNo,@WCenter,@WorkGrpConf,@WorkGrpPrvConf,@Kg,@St

WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO ProductionMatrixSAP
						  (SD_key, MaxOP, OP01, WC01, WG01, PG01, LD01, KG01, ST01)
	VALUES     (@sd_key,'1',@OperNo,@WCenter,@WorkGrpConf,@WorkGrpPrvConf,@PostDate,@Kg,@St)

	FETCH NEXT FROM s_cursor
	into @sd_key,@PostDate,@OperNo,@WCenter,@WorkGrpConf,@WorkGrpPrvConf,@Kg,@St

end


CLOSE s_cursor
DEALLOCATE s_cursor

--ΗΤΗ--

Απ: Migration Ms Access function σε SQL 2008

spaceman — Thu, 16 Dec 2010 22:58:16 GMT

neoklis:

Αυτό που θέλω να πάρω είναι

1
2

               SD_key	      MaxOP	OP01	WC01	WG01	PG01	LD01	KG01	ST01	OP02	WC02	WG02	PG02	LD02	KG02	ST02	OP03	WC03	WG03	PG03	LD03	KG03	ST03	OP04	WC04	WG04	PG04	LD04	KG04	ST04	OP05	WC05	WG05	PG05	LD05	KG05	ST05
1200019825-001-21028018863	5	0060	S_IGT1	IGT	IGT	15-03-06	27900	0	0060	S_IGT3	IGT	IGT	07-04-06	57150	0	0150	S_SPL3	SPL	SPL	07-04-06	90750	605	0195	S_FRN2	FRN	FRN	07-04-06	90150	601	0500	LWC	Y-LWC		25-04-06	85423	0

Δεν έχω καταφέρει απόλυτη στοίχηση των πεδίων αλλά ελπίζω να είναι κατανοητό...

Μηπως αυτο που θελεις ειναι cross-tab report ? (records --> columns), επειδη απο την μορφη του τελικου αποτελεσματος δεν ειναι εμφανες που/πως χρησιμοποιειται concatenation.

Στην περιπτωση cross-tab report, υπαρχουν διαφοροι τροποι να το επιτυχεις (cursors, dynamic sql, pivot, case statements) :

px.

DECLARE @t TABLE (	SD_KEY NVARCHAR(100) COLLATE database_default,
					PostDate SMALLDATETIME,
					Operation_No NVARCHAR(4) COLLATE database_default,
					Work_Center NVARCHAR(10) COLLATE database_default,
					WorkGroupConfirm NVARCHAR(5) COLLATE database_default,
					WorkGroupPrevConfirm NVARCHAR(5) COLLATE database_default,
					KG INT,
					ST INT
				)
INSERT INTO @t(SD_KEY, PostDate, Operation_No, Work_Center, WorkGroupConfirm, WorkGroupPrevConfirm, KG, ST)
VALUES
(N'1200019825-001-21028018863', '2006-03-15 00:00:00.000', N'0060', N'S_IGT1', N'IGT', N'IGT',27900, 0),
(N'1200019825-001-21028018863', '2006-04-07 00:00:00.000', N'0060', N'S_IGT3', N'IGT', N'IGT',57150, 0),
(N'1200019825-001-21028018863', '2006-04-07 00:00:00.000', N'0150', N'S_SPL3', N'SPL', N'SPL',90750, 605),
(N'1200019825-001-21028018863', '2006-04-07 00:00:00.000', N'0195', N'S_FRN2', N'FRN', N'FRN',90150, 601),
(N'1200019825-001-21028018863', '2006-04-25 00:00:00.000', N'0500', N'LWC', N'Y-LWC', NULL,85423, 0)


--option 1: multi-pivot
SELECT SD_KEY, 
	MAX([OP01]) AS OP01, MAX([LD01]) AS LD01, MAX([KG01]) AS KG01,
	MAX([OP02]) AS OP02, MAX([LD02]) AS LD02, MAX([KG02]) AS KG02,
	MAX([OP03]) AS OP03, MAX([LD03]) AS LD03, MAX([KG03]) AS KG03,
	MAX([OP04]) AS OP04, MAX([LD04]) AS LD04, MAX([KG04]) AS KG04,
	MAX([OP05]) AS OP05, MAX([LD05]) AS LD05, MAX([KG05]) AS KG05
FROM 
	(SELECT SD_KEY, 
			PostDate, 'LD' + RIGHT(N'00' + CAST(ROW_NUMBER() OVER(PARTITION BY SD_KEY ORDER BY PostDate) AS NVARCHAR(2)), 2) AS LDrownum,
			Operation_No, 'OP' + RIGHT(N'00' + CAST(ROW_NUMBER() OVER(PARTITION BY SD_KEY ORDER BY PostDate) AS NVARCHAR(2)), 2) AS OPrownum,
			KG, 'KG' + RIGHT(N'00' + CAST(ROW_NUMBER() OVER(PARTITION BY SD_KEY ORDER BY PostDate) AS NVARCHAR(2)), 2)  AS KGrownum
		FROM @t 
	) AS data
--PostDate pivot
PIVOT
(
	MAX(PostDate) FOR LDrownum IN ([LD01], [LD02], [LD03], [LD04], [LD05]) --[LD06], .....[LDxx]
) AS LDpvt
--Operaton_No pivot
PIVOT
(
	MAX(Operation_No) FOR OPrownum IN ([OP01], [OP02], [OP03], [OP04], [OP05]) --[OP06], .....[OPxx]
) AS LDpvt
--other pivots here
--......
--......
--KG pivot
PIVOT
(
	MAX(KG) FOR KGrownum IN ([KG01], [KG02], [KG03], [KG04], [KG05]) --[KG06], .....[KGxx]
) AS KGpvt
GROUP BY SD_KEY


--option 2: case statements
SELECT SD_KEY, 
	MAX(CASE rownum WHEN 1 THEN Operation_No END) AS OP01, MAX(CASE rownum WHEN 1 THEN PostDate END) AS PD01, MAX(CASE rownum WHEN 1 THEN KG END) AS KG01,
	MAX(CASE rownum WHEN 2 THEN Operation_No END) AS OP02, MAX(CASE rownum WHEN 2 THEN PostDate END) AS PD02, MAX(CASE rownum WHEN 2 THEN KG END) AS KG02,
	MAX(CASE rownum WHEN 3 THEN Operation_No END) AS OP03, MAX(CASE rownum WHEN 3 THEN PostDate END) AS PD03, MAX(CASE rownum WHEN 3 THEN KG END) AS KG03,
	MAX(CASE rownum WHEN 4 THEN Operation_No END) AS OP04, MAX(CASE rownum WHEN 4 THEN PostDate END) AS PD04, MAX(CASE rownum WHEN 4 THEN KG END) AS KG04,		
	MAX(CASE rownum WHEN 5 THEN Operation_No END) AS OP05, MAX(CASE rownum WHEN 5 THEN PostDate END) AS PD05, MAX(CASE rownum WHEN 5 THEN KG END) AS KG05		
FROM 
(
	SELECT SD_KEY, PostDate, Operation_No, KG, ROW_NUMBER() OVER(PARTITION BY SD_KEY ORDER BY PostDate) AS rownum
	FROM @t
) AS data
GROUP BY SD_KEY

Στην περιπτωση του cross-tab ο tsql κωδικας "gets extremely ugly" και ειναι πολλοι που θα σου προτειναν να κανεις το cross-tab report στον client.

--HTH--

Απ: Migration Ms Access function σε SQL 2008

neoklis — Thu, 16 Dec 2010 22:12:32 GMT

Ενώ γράφω τον κώδικα μου και πατάω execute για να δω τα αποτελέσματα, το execute δεν εκτελείται. Δείχνει ότι κάνει κάτι, αλλά κοκο και αυγό τίποτα... Πατάω debug βλέπω ότι δεν υπάρχει πρόβλημα.. κάνω stop debug... execute και εκτελείται μια χαρά... μήπως γνωρίζει κάποιος..;

Απ: Migration Ms Access function σε SQL 2008

neoklis — Thu, 16 Dec 2010 21:14:34 GMT

Αυτό που πιστεύω ότι θα χρειαστώ είιναι μια stored procedure που θα διαβάζω γραμμή γραμμή το selection. Έχω ξεκινήσει να γράφω τον κώδικα μου, χτυπάει σε κάποιες περιπτώσεις (διπλοκλειδιά)... λογικό είναι δεν έχω ενσωματώσει ελέγχους ούτε συνθήκες... κάπως έτσι πιστεύω θα συνεχίσω...

truncate table ProductionMatrixSAP

declare @Sd_Key nvarchar(50)
declare @PostDate datetime
declare @OperNo nvarchar(4)
declare @WCenter nvarchar(8)
declare @WorkGrpConf nvarchar(8)
declare @WorkGrpPrvConf nvarchar(8)
declare @Kg int
declare @St int

DECLARE s_cursor CURSOR 
READ_ONLY
FOR SELECT SAP_PO.SD_key, Max(SAP_Confirmations.Posting_Date) AS PostDate, SAP_Confirmations.Operation_No, 
SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm, 
Sum(SAP_Confirmations.Conf_qty_KG) AS KG, Sum(SAP_Confirmations.Conf_Pieces) AS ST 
FROM (SAP_Confirmations INNER JOIN SAP_PO ON SAP_Confirmations.PO = SAP_PO.PO) INNER JOIN Work_Groups 
ON (SAP_Confirmations.MRP_Controller = Work_Groups.MRP_Controller) AND 
(SAP_Confirmations.Work_Center = Work_Groups.WorkCenter) GROUP BY SAP_PO.SD_key, SAP_Confirmations.Operation_No, 
SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm 
ORDER BY SAP_PO.SD_key, SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center

open s_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH NEXT FROM s_cursor
	into @sd_key,@PostDate,@OperNo,@WCenter,@WorkGrpConf,@WorkGrpPrvConf,@Kg,@St

	INSERT INTO ProductionMatrixSAP
						  (SD_key, MaxOP, OP01, WC01, WG01, PG01, LD01, KG01, ST01)
	VALUES     (@sd_key,'1',@OperNo,@WCenter,@WorkGrpConf,@WorkGrpPrvConf,@PostDate,@Kg,@St)
end

CLOSE s_cursor
DEALLOCATE s_cursor

Απ: Migration Ms Access function σε SQL 2008

neoklis — Thu, 16 Dec 2010 18:52:56 GMT

spaceman:
Ποσες στηλες εχει ο πινακας ProductionMatrixSAP ?

Ο πίνακας έχει 85 στήλες.

spaceman:

Με μια ματια στον κωδικα που παραθετεις, ο πινακας ProductionMatrixSap ειναι της μορφης (correct me if i'm wrong) :
SD_key, MaxOp, OP01, WC01, WG01, PG01, LD01, KG01, ST01, OP02, WC02, WG02, PG02, LD02, KG02, ST02, OP03, WC03, WG03, PG03, LD03, KG03, ST03, OPxx, WCxx, WGxx, PGxx, LDxx, KGxx, STxx

Σωστά.

spaceman:
Καθε unique τιμη του SD_Key εχει και μια εγγραφη στον πινακα, κ στο MaxOp χρησιμοποιειται καπου στην συνεχεια, για τον προσδιορισμο των στηλων που πρεπει να "προστεθουν" για το concatenation ?
px. SD_Key   MaxOp
      KeyA         4
      KeyB         2

Σωστά.

spaceman:

KeyA, CONCATENATE(OP01, OP02, OP03, OP04) AS OP (looks a bit weird)
KeyA, CONCATENATE(OP01, OP02) AS OP

Αν παρουσιαζες τα results του "SELECT SAP_PO.SD_key, Max(SAP_Confirmations.Posting_Date) AS PostDate, SAP_Confirmations ......"
κ περιεγραφες στην συνεχεια το πως θελεις την τελικη μορφη των δεδομενων, ισως θα μπορουσε να δωθει μια ποιο ευστοχη απαντηση.

Έστω λοιπό ότι το αποτέλεσμα του selection είναι

               SD_key	                  PostDate	Operation_No	Work_Center	WorkGroupConfirm	WorkGroupPrevConfirm	KG	ST
1200019825-001-21028018863	2006-03-15 00:00:00.000	0060	               S_IGT1	         IGT	         IGT	               27900	0
1200019825-001-21028018863	2006-04-07 00:00:00.000	0060	               S_IGT3	         IGT	         IGT	               57150	0
1200019825-001-21028018863	2006-04-07 00:00:00.000	0150	               S_SPL3	         SPL	         SPL	               90750	605
1200019825-001-21028018863	2006-04-07 00:00:00.000	0195	               S_FRN2	         FRN	         FRN	               90150	601
1200019825-001-21028018863	2006-04-25 00:00:00.000	0500	               LWC	         Y-LWC	          NULL	               85423	0

Αυτό που θέλω να πάρω είναι

1
2

               SD_key	      MaxOP	OP01	WC01	WG01	PG01	LD01	KG01	ST01	OP02	WC02	WG02	PG02	LD02	KG02	ST02	OP03	WC03	WG03	PG03	LD03	KG03	ST03	OP04	WC04	WG04	PG04	LD04	KG04	ST04	OP05	WC05	WG05	PG05	LD05	KG05	ST05
1200019825-001-21028018863	5	0060	S_IGT1	IGT	IGT	15-03-06	27900	0	0060	S_IGT3	IGT	IGT	07-04-06	57150	0	0150	S_SPL3	SPL	SPL	07-04-06	90750	605	0195	S_FRN2	FRN	FRN	07-04-06	90150	601	0500	LWC	Y-LWC		25-04-06	85423	0

Δεν έχω καταφέρει απόλυτη στοίχηση των πεδίων αλλά ελπίζω να είναι κατανοητό...

Απ: Migration Ms Access function σε SQL 2008

spaceman — Thu, 16 Dec 2010 03:14:47 GMT

Ποσες στηλες εχει ο πινακας ProductionMatrixSAP ?
Με μια ματια στον κωδικα που παραθετεις, ο πινακας ProductionMatrixSap ειναι της μορφης (correct me if i'm wrong) :
SD_key, MaxOp, OP01, WC01, WG01, PG01, LD01, KG01, ST01, OP02, WC02, WG02, PG02, LD02, KG02, ST02, OP03, WC03, WG03, PG03, LD03, KG03, ST03, OPxx, WCxx, WGxx, PGxx, LDxx, KGxx, STxx

Καθε unique τιμη του SD_Key εχει και μια εγγραφη στον πινακα, κ στο MaxOp χρησιμοποιειται καπου στην συνεχεια, για τον προσδιορισμο των στηλων που πρεπει να "προστεθουν" για το concatenation ?
px. SD_Key   MaxOp
      KeyA         4
      KeyB         2

KeyA, CONCATENATE(OP01, OP02, OP03, OP04) AS OP (looks a bit weird)
KeyA, CONCATENATE(OP01, OP02) AS OP

Αν παρουσιαζες τα results του "SELECT SAP_PO.SD_key, Max(SAP_Confirmations.Posting_Date) AS PostDate, SAP_Confirmations ......"
κ περιεγραφες στην συνεχεια το πως θελεις την τελικη μορφη των δεδομενων, ισως θα μπορουσε να δωθει μια ποιο ευστοχη απαντηση.

--ΗΤΗ--

Migration Ms Access function σε SQL 2008

neoklis — Wed, 15 Dec 2010 22:20:24 GMT

Επειδή το παρόν thread είναι συνέχεια προηγούμενου αλλά αφορά διαφορετικό θέμα, δημιούγησα το παρόν και ποστάρω link του προηγούμενου

http://www.dotnetzone.gr/cs/forums/1/61671/ShowThread.aspx

Κάνω migration από Ms Access σε SQL 2008 και έχω την παρακάτω function. Αν την αφήσω όπως έχει και την τρέξω μέσα από την Access με linked tables στον SQL, o χρόνος εκτέλεσης είναι 2 με 3 ώρες την στιγμή που όταν ήταν linked με πίνακες στην Access, ήταν κοντά στα 15 λεπτά. Για λόγους που δεν γνωρίζω καθυστερεί υπερβολικά, οπότε πρέπει να την ξαναφτιάξω στον SQL. Έχω κολλήσει όμως και δεν μπορώ να αποφασίσω για το αν θα πρέπει να είναι Stored Procedure, UDf ή custom aggregate.

Public Function Generate_LineProductionSAP()

    Static S As Recordset, SAP_SQL, L As Recordset
    Static Ret As Variant, z As Long, TotalRecs
    Static cur_key, i, itxt, j, jtxt, GroupFound, x
    
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE ProductionMatrixSAP.* FROM ProductionMatrixSAP;"
    SAP_SQL = "SELECT SAP_PO.SD_key, Max(SAP_Confirmations.Posting_Date) AS PostDate, SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm, Sum(SAP_Confirmations.Conf_qty_KG) AS KG, Sum(SAP_Confirmations.Conf_Pieces) AS ST FROM (SAP_Confirmations INNER JOIN SAP_PO ON SAP_Confirmations.PO = SAP_PO.PO) INNER JOIN Work_Groups ON (SAP_Confirmations.MRP_Controller = Work_Groups.MRP_Controller) AND (SAP_Confirmations.Work_Center = Work_Groups.WorkCenter) GROUP BY SAP_PO.SD_key, SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center, Work_Groups.WorkGroupConfirm, Work_Groups.WorkGroupPrevConfirm ORDER BY SAP_PO.SD_key, SAP_Confirmations.Operation_No, SAP_Confirmations.Work_Center;"
    Set L = CurrentDb.OpenRecordset("ProductionMatrixSAP", dbOpenDynaset)
    
    x = SaveSQL("Generate ProductionMatrix with SAP Confirmations", "Generate_LineProductionSAP", "SAP_SQL", SAP_SQL, GetAddSQL())
    Set S = CurrentDb.OpenRecordset(SAP_SQL, dbReadOnly)
    
    If S.RecordCount > 0 Then
        i = 0
        itxt = Null
        cur_key = "#init#"
        
        S.MoveLast
        TotalRecs = S.RecordCount
        Ret = SysCmd(SYSCMD_INITMETER, "Generate SAP Matrix...", TotalRecs)
        z = 1
        
        S.MoveFirst
        Do Until S.EOF
        
            Ret = SysCmd(SYSCMD_UPDATEMETER, z)
            If Not cur_key = S("SD_key") Then
                L.AddNew
                L("SD_key") = S("SD_key")
                L("MaxOP") = 1
                L("OP01") = S("Operation_No")
                L("WC01") = S("Work_Center")
                L("WG01") = S("WorkGroupConfirm")
                L("PG01") = S("WorkGroupPrevConfirm")
                L("LD01") = S("PostDate")
                L("KG01") = S("KG")
                L("ST01") = S("ST")
                L("MaxCombo") = 1
                L("G01") = S("WorkGroupConfirm")
                L("D01") = S("PostDate")
                cur_key = S("SD_key")
                i = 1
                L.Update
            Else
                L.MoveLast
                i = i + 1
                itxt = Format(i, "00")
                L.Edit
                L("MaxOP") = i
                L(("OP" & itxt)) = S("Operation_No")
                L(("WC" & itxt)) = S("Work_Center")
                L(("WG" & itxt)) = S("WorkGroupConfirm")
                L(("PG" & itxt)) = S("WorkGroupPrevConfirm")
                L(("LD" & itxt)) = S("PostDate")
                L(("KG" & itxt)) = S("KG")
                L(("ST" & itxt)) = S("ST")
                GroupFound = False
                j = 1
                Do Until GroupFound Or j > L("MaxCombo")
                    jtxt = Format(j, "00")
                    If L(("G" & jtxt)) = S("WorkGroupConfirm") Then
                        GroupFound = True
                        If L(("D" & jtxt)) < S("PostDate") Then
                            L(("D" & jtxt)) = S("PostDate")
                        End If
                    Else
                        j = j + 1
                    End If
                Loop
                If Not GroupFound Then
                    jtxt = Format(j, "00")
                    L("MaxCombo") = j
                    L(("G" & jtxt)) = S("WorkGroupConfirm")
                    L(("D" & jtxt)) = S("PostDate")
                End If
                L.Update
            End If
            S.MoveNext
            z = z + 1
        Loop
        Ret = SysCmd(SYSCMD_REMOVEMETER)
    End If
    
    S.Close
    L.Close
    
Debug.Print SAP_SQL
    
End Function