Intrebari

Re: update ciudat

sorinh — Mon, 24 Sep 2007 23:26:43 GMT

Am facut deja cum a zis Diana. Problema mea era sa le grupez fara o cheie primara sau un element de legatura ... Multumesc tuturor pentru ajutor

Re: update ciudat

B_gd_n[ ]Sahlean — Mon, 24 Sep 2007 17:57:07 GMT

Pentru varianta in care apelezi la o cheie externa pentru a putea lega fiecare comanda de articole comandate se poate utiliza o singura tabelă (test , nu este recomandabilă) si poti crea o relaţie circulară de la cheia primară test.col1 la cheia externa test.col4 :

ALTER TABLE test
ADD col4 INT CONSTRAINT test_cheie_externa_1 FOREIGN KEY (col4) REFERENCES test(col1)

UPDATE test
SET col4 = (SELECT MAX(T2.col1) FROM test T2 WHERE T2.col2 = '6a' AND T2.col1 < T1.col1)
FROM test T1
WHERE T1.col2 = '6b'

Exemplu interogare:

SELECT Comanda.*, ArticolComandat.*
FROM
(SELECT * FROM test WHERE col2 = '6a') AS Comanda
INNER JOIN
(SELECT * FROM test WHERE col2 = '6b') AS ArticolComandat
ON Comanda.col1 = ArticolComandat.col4

A doua solutie daca apelezi la crearea unei chei externe consta in creare a doua tabele separate: o tabela care contine doar comenzile, si o tabela care contine doar articolele comandate.

UPDATE test_articol_comanda SET col4 = NULL --optional

SELECT *
INTO test_comanda
FROM test
WHERE col2 = '6a'

SELECT *
INTO test_articol_comanda
FROM test
WHERE col2 = '6b'

SELECT * FROM test_comanda
SELECT * FROM test_articol_comanda

UPDATE test_articol_comanda
SET col4 = (SELECT MAX(T2.col1) FROM test T2 WHERE T2.col2 = '6a' AND T2.col1 < T1.col1)
FROM test_articol_comanda T1
WHERE T1.col2 = '6b'

Exemplu interogare:

SELECT *
FROM test_comanda C
INNER JOIN
test_articol_comanda AC
ON C.col1 = AC.col4

Re: update ciudat

B_gd_n[ ]Sahlean — Mon, 24 Sep 2007 17:27:47 GMT

CREATE TABLE test(
col1 INT IDENTITY(1,1) PRIMARY KEY,
col2 VARCHAR(25) NOT NULL,
col3 VARCHAR(25)
)

INSERT INTO test (col2,col3) VALUES ('6a','2')
INSERT INTO test (col2,col3) VALUES ('6b',NULL)
INSERT INTO test (col2,col3) VALUES ('6b',NULL)
INSERT INTO test (col2,col3) VALUES ('6a','3')
INSERT INTO test (col2,col3) VALUES ('6b',NULL)
INSERT INTO test (col2,col3) VALUES ('6b',NULL)
INSERT INTO test (col2,col3) VALUES ('6b',NULL)
INSERT INTO test (col2,col3) VALUES ('6a','f')
INSERT INTO test (col2,col3) VALUES ('6b',NULL)

SELECT * FROM test

SELECT T1.col1,
   T1.col2,
   T1.col3,
   (SELECT col3 FROM test WHERE col1 = (SELECT MAX(T2.col1) FROM test T2 WHERE T2.col2 = '6a' AND T2.col1 < T1.col1)) NewCol3
FROM test T1
WHERE T1.col2 = '6b'

SELECT *
INTO test_bkp
FROM test

UPDATE test
SET col3 = (SELECT col3 FROM test WHERE col1 = (SELECT MAX(T2.col1) FROM test T2 WHERE T2.col2 = '6a' AND T2.col1 < T1.col1))
FROM test T1
WHERE T1.col2 = '6b'

Am dubii cu privire la 2 aspecte:

[1] ordinea inregistrarilor care sunt transferate / importate inregistrarile. Aici trebuie sa gasesti o metoda pentru ca inregistrarile sa fie importate SIGUR CORECT (pentru a nu amesteca articolele dintr-o comanda cu articolele altei comenzi)

[2] de ce nu apelezi la o cheie externa ? Cheia externa iti va permite sa creezi legaturile intre inregistrarile COMANDA ( 6a ) si inregistrarile ARTICOLE COMANDATE ( 6b )

Re: update ciudat

Gherry — Mon, 24 Sep 2007 16:34:42 GMT

foarte buna observatia Dianei; ar trebui sa ai mult de castigat ca si viteza daca ai scoate comenzile intr-un tabel (primul view) iar continutul lor in altul (al doilea view) mentinand o relatie de "one to many" (o gasesti in al doilea view legatura) plus ca iti da si posibilitatea sa modifici mai usor continutul unei singure comenzi. intr-adevar merita facute niste teste pe tema asta.

Re: update ciudat

Diana — Mon, 24 Sep 2007 16:17:23 GMT

Nu poti "aranja" cumva ca datele sa se afle in doua tabele in relatie "one to many" - unul de clienti, celalalt de comenzi?

Nu stiu care sunt "business rules" la voi - dar nu se poate midifica importul prin DTS, sau nu poti "transfera" datele ulterior?

Re: update ciudat

sorinh — Mon, 24 Sep 2007 16:05:00 GMT

Pur si simplu elegant.

Re: update ciudat

Gherry — Mon, 24 Sep 2007 14:22:52 GMT

date de plecare:

1	6a	2
2	6b	NULL
3	6b	NULL
4	6a	3
5	6b	NULL
6	6b	NULL
7	6b	NULL
8	6a	f
9	6b	NULL
10	6b	NULL
NULL	NULL	NULL

rezultat:

2	6b	NULL	2
3	6b	NULL	2
5	6b	NULL	3
6	6b	NULL	3
7	6b	NULL	3
9	6b	NULL	f
10	6b	NULL	f

Re: update ciudat

Gherry — Mon, 24 Sep 2007 14:20:39 GMT

ok, am gasit si o solutie mai aproape de "spiritul" sql

iata pasii:

1. am facut un camp de id care se autoincrementeaza

asa ar fi tabelul care l-am folosit eu pt test:

USE [Intera_Sql_Functions]
GO
/****** Object: Table [dbo].[eug_tst]    Script Date: 09/24/2007 17:11:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eug_tst](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [cond] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [rez] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_eug_tst] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

si el continea datele de test:

1	6a	2
2	6b	NULL
3	6b	NULL
4	6a	3
5	6b	NULL
6	6b	NULL
7	6b	NULL
8	6a	f
9	6b	NULL
10	6b	NULL
NULL	NULL	NULL

2. am facut un view care sa continea doar inregistrarile ce contineau 6A:

USE [Intera_Sql_Functions]
GO
/****** Object: View [dbo].[eug_tmp]    Script Date: 09/24/2007 17:13:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[eug_tmp]
AS
SELECT TOP (100) PERCENT id, cond, rez
FROM dbo.eug_tst
WHERE (cond = N'6a')
ORDER BY id

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
   Begin PaneConfigurations =
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[36] 4[25] 2[12] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane =
      Begin Origin =
         Top = 0
         Left = 0
      End
      Begin Tables =
         Begin Table = "eug_tst"
            Begin Extent =
               Top = 7
               Left = 48
               Bottom = 125
               Right = 222
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane =
   End
   Begin DataPane =
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
      End
   End
   Begin CriteriaPane =
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1176
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1356
         SortOrder = 1416
         GroupBy = 1356
         Filter = 1356
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp'

3. am facut un view care continea id-urile recordurilor care incep cu 6A corespunzatoare fiecarui record ce incepe cu 6B

USE [Intera_Sql_Functions]
GO
/****** Object: View [dbo].[eug_tmp_2]    Script Date: 09/24/2007 17:16:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[eug_tmp_2]
AS
SELECT TOP (100) PERCENT dbo.eug_tst.id, dbo.eug_tst.cond, dbo.eug_tst.rez, MAX(dbo.eug_tmp.id) AS Expr1
FROM dbo.eug_tst INNER JOIN
               dbo.eug_tmp ON dbo.eug_tst.id > dbo.eug_tmp.id
GROUP BY dbo.eug_tst.id, dbo.eug_tst.cond, dbo.eug_tst.rez
HAVING (dbo.eug_tst.cond = N'6b')
ORDER BY dbo.eug_tst.id

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
   Begin PaneConfigurations =
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[20] 2[11] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane =
      Begin Origin =
         Top = 0
         Left = 0
      End
      Begin Tables =
         Begin Table = "eug_tst"
            Begin Extent =
               Top = 7
               Left = 48
               Bottom = 125
               Right = 222
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "eug_tmp"
            Begin Extent =
               Top = 7
               Left = 270
               Bottom = 125
               Right = 444
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane =
   End
   Begin DataPane =
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
      End
   End
   Begin CriteriaPane =
      Begin ColumnWidths = 12
         Column = 1440
         Alias = 900
         Table = 1176
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1356
         SortOrder = 1416
         GroupBy = 1356
         Filter = 1632
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp_2'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp_2'

4. la sfarsit am facut un query care sa scoata campul care consideram eu necesar:

SELECT dbo.eug_tmp_2.id, dbo.eug_tmp_2.cond, dbo.eug_tmp_2.rez, dbo.eug_tst.rez AS Expr1
FROM dbo.eug_tmp_2 INNER JOIN
dbo.eug_tst ON dbo.eug_tmp_2.Expr1 = dbo.eug_tst.id

--------------------------------------------------------------------------------------------------------------------------

eu am incercat sa simulez cat de cat problema pe care ziceai tu ca o ai, nu stiu exact in ce masura am intuit detaliile, plus de asta stiu ca exprimarea mea mai lasa de dorit asa ca daca sunt ceva neclaritati anuta si vedem..

bafta.

Re: update ciudat

sorinh — Mon, 24 Sep 2007 13:53:39 GMT

Momentan am facut un cursor.Din pacate operatiunea se repeta de mai multe ori pe zi ...

Mie din pacate nu imi vine in minte momentan alta solutie. daca mai apar idei ...

Re: update ciudat

Gherry — Mon, 24 Sep 2007 13:43:22 GMT

prima solutie care imi vine in minte nu e tocmai abordarea ideala pt sql dar daca operatia asta o faci o singura data in ideea ca o sa corectezi niste inregistrari si gata, ai putea folosi un cursor care ia la rand fiecare inregistrare iar daca incepe cu 6A salvezi intr-o variabila ce este in campul are iti trebuie iar daca incepe cu 6B faci update-ul.

oricum daca e o operatie care o sa trebuiasca repetata frecvent trebuie sapat un pic mai mult si gasit un artificiu mai decent. O sa ma gandesc un pic si la o astfel de varianta.

Re: update ciudat

sorinh — Mon, 24 Sep 2007 13:19:05 GMT

Nu este nici un secret, structura tabelei este ceva de genul asta

Col001 Col002 Col003 Col004 ....

sunt importate prin intermediul unui DTS dintr-un soft de palm. se presupune ca ar trebui sa fie un export pentru niste comenzi, in care liniile cu 6A tin informatii despre client si cele cu 6B despre continutul comenzii. problema mea e ca pentru a crea niste tranzactii in baza de date trebuie sa stiu exact cite linii contine fiecare cimanda, pe ce client este. Delimitarea intre 2 comenzi se face printro noua linie care inepe cu 6A.

Re: update ciudat

B_gd_n[ ]Sahlean — Mon, 24 Sep 2007 12:31:29 GMT

Structura tabelei este un secret ?

Inregistrarile afisate sunt ordonate dupa un anumit criteriu ?

Re: update ciudat

sorinh — Mon, 24 Sep 2007 12:18:46 GMT

Din pacate nu.

Re: update ciudat

B_gd_n[ ]Sahlean — Mon, 24 Sep 2007 12:04:09 GMT

Are tabela cheie primara / cheie candidat / un index cu valori UNICE si NENULE ?

later edit: banuiesc ca stii ca noi stim structura tabelei .

update ciudat

sorinh — Mon, 24 Sep 2007 11:11:22 GMT

Am iar nevoie de o chestie mai ciudata ...

am un tabel de forma :

6 A 000000011 10/07/2007 10 C43AB00018 NULL 0.00 170.35000 15:15 5115
6 B E200027 NULL 1 3.00 3.00000 NULL NULL NULL
6 B E200039 NULL 2 2.73 5.46000 NULL NULL NULL
6 B E200029 NULL 2 2.73 5.46000 NULL NULL NULL
6 B E197656NS NULL 2 11.93 23.86000 NULL NULL NULL
6 B E200050 NULL 1 3.00 3.00000 NULL NULL NULL
6 B E198028NEW NULL 3 3.91 11.73000 NULL NULL NULL
6 B E198114NEW NULL 6 9.60 57.60000 NULL NULL NULL
6 B E198026NEW NULL 2 6.92 13.84000 NULL NULL NULL
6 B E200042 NULL 2 3.00 6.00000 NULL NULL NULL
6 B E197661NS NULL 2 6.60 13.20000 NULL NULL NULL
6 A 000000012 10/07/2007 7 C43AB00002 NULL 0.00 629.84000 15:25 5115
6 B E197649NEW NULL 12 4.90 58.80000 NULL NULL NULL
6 B E198027 NULL 10 4.50 45.00000 NULL NULL NULL
6 B E197623 NULL 12 3.65 43.80000 NULL NULL NULL
6 B E900011 NULL 1 30.80 30.80000 NULL NULL NULL
6 B EDSF100 NULL 2 10.68 21.36000 NULL NULL NULL
6 B E198114NEW NULL 30 9.60 288.00000 NULL NULL NULL
6 B E198026NEW NULL 6 6.92 41.52000 NULL NULL NULL

Dupa o linie care incepe cu 6, A (invariabil) urmeaza un numar aleator de linii care incep cu 6, B. Am nevoie ca pentru toate liniile cu 6,B (care se gasesc intre 2 inregistrari cu 6, A) sa fac un update cu un cimp de pe linia cu 6,A.

Cum pot face ?