Início / user-38925

zerodoc's questions

Martin Hope
zerodoc
Asked: 2021-07-08 09:41:20 +0800 CST
  • 1

Eu tenho uma tabela que contém nomes de atributos com valores, mas cada linha é um atributo com seu valor,

ItemId NomeAtributo do Item ItemAtributeValue
405 Inscrição Conexões de alta ampacidade
405 Cor Amarelo
405 Condições ambientais Estanque
405 Material Composto
406 Inscrição Conexões de alta ampacidade
406 Cor Azul
406 Material Latão

então eu criei outra tabela com todos os nomes de atributos possíveis para achatá-la para que cada linha contenha todos os valores para cada item.

ItemId Inscrição Cor Condições ambientais Material
405 Conexões de alta ampacidade Amarelo Estanque Composto
406 Conexões de alta ampacidade Azul Madeira

Como é possível pegar a primeira tabela e inserir os dados de cada itemId na tabela acima nas colunas correspondentes?

Também preciso considerar que existem mais de cem nomes de atributos diferentes e nem todos os itens terão cada atributo ou valor. Não tenho certeza de que direção devo seguir com esse problema.

sql-server t-sql
Martin Hope
zerodoc
Asked: 2017-02-18 11:35:54 +0800 CST
  • 1

Este AFTER INSERT TRIGGER sempre avalia como OrderTotal

ALTER TRIGGER [dbo].[OrderInformationInsert]
ON [dbo].[Order]
AFTER INSERT
AS
    INSERT INTO [FRTOOLS_Orders]
                (OrderNumber,
                 SubTotal)
    SELECT i.[OrderNumber],
           CASE
             WHEN SUM(oc.Amount) > 0
               THEN i.OrderTotal - SUM(oc.Amount)
             ELSE i.OrderTotal
           END AS subTotal
    FROM   inserted i
           LEFT JOIN [OrderCharge] oc
             ON i.OrderID = oc.OrderID
    WHERE  NOT EXISTS(SELECT '1'
                      FROM   [FRTOOLS_Orders]
                      WHERE  OrderNumber = i.[OrderNumber])
    GROUP  BY i.[OrderNumber],
              i.[OrderTotal]

Quando eu executo a instrução select. Eu recebo as informações como esperado?

SELECT o.[OrderNumber],
       CASE
         WHEN SUM(oc.Amount) > 0
           THEN o.OrderTotal - SUM(oc.Amount)
         ELSE o.OrderTotal
       END AS subTotal
FROM   [Order] o
       LEFT JOIN [OrderCharge] oc
         ON o.OrderID = oc.OrderID
GROUP  BY o.[OrderNumber],
          o.[OrderTotal]

Seria este o caso se a tabela OrderCharge ainda não tiver valores e como FROM INSERTED funciona , devo estar indo em uma direção diferente.

Tabela OrderCharge com script:

CREATE TABLE [dbo].[OrderCharge](
[OrderChargeID] [bigint] IDENTITY(1021,1000) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[OrderID] [bigint] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[Description] [nvarchar](255) NOT NULL,
[Amount] [money] NOT NULL,
CONSTRAINT [PK_OrderCharge] PRIMARY KEY CLUSTERED 
(
[OrderChargeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[OrderCharge]  WITH CHECK ADD  CONSTRAINT [FK_OrderCharge_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO

ALTER TABLE [dbo].[OrderCharge] CHECK CONSTRAINT [FK_OrderCharge_Order]
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderCharge', @level2type=N'COLUMN',@level2name=N'Amount'
GO

Não tenho certeza, mas Order Table I muito longo? A propósito, este não é o nosso banco de dados, esta parte do software Shipworks e sim, eles nomearam uma tabela com uma palavra-chave sql, e é por isso que temos que manipular a tabela.

USE [ShipWorks]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 2/18/2017 11:23:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Order](
    [OrderID] [bigint] IDENTITY(1006,1000) NOT NULL,
    [RowVersion] [timestamp] NOT NULL,
    [StoreID] [bigint] NOT NULL,
    [CustomerID] [bigint] NOT NULL,
    [OrderNumber] [bigint] NOT NULL,
    [OrderNumberComplete] [nvarchar](50) NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [OrderTotal] [money] NOT NULL,
    [LocalStatus] [nvarchar](100) NOT NULL,
    [IsManual] [bit] NOT NULL,
    [OnlineLastModified] [datetime2](7) NOT NULL,
    [OnlineCustomerID] [sql_variant] NULL,
    [OnlineStatus] [nvarchar](100) NOT NULL,
    [OnlineStatusCode] [sql_variant] NULL,
    [RequestedShipping] [nvarchar](50) NOT NULL,
    [BillFirstName] [nvarchar](30) NOT NULL,
    [BillMiddleName] [nvarchar](30) NOT NULL,
    [BillLastName] [nvarchar](30) NOT NULL,
    [BillCompany] [nvarchar](60) NOT NULL,
    [BillStreet1] [nvarchar](60) NOT NULL,
    [BillStreet2] [nvarchar](60) NOT NULL,
    [BillStreet3] [nvarchar](60) NOT NULL,
    [BillCity] [nvarchar](50) NOT NULL,
    [BillStateProvCode] [nvarchar](50) NOT NULL,
    [BillPostalCode] [nvarchar](20) NOT NULL,
    [BillCountryCode] [nvarchar](50) NOT NULL,
    [BillPhone] [nvarchar](25) NOT NULL,
    [BillFax] [nvarchar](35) NOT NULL,
    [BillEmail] [nvarchar](100) NOT NULL,
    [BillWebsite] [nvarchar](50) NOT NULL,
    [BillAddressValidationSuggestionCount] [int] NOT NULL,
    [BillAddressValidationStatus] [int] NOT NULL,
    [BillAddressValidationError] [nvarchar](300) NOT NULL,
    [BillResidentialStatus] [int] NOT NULL,
    [BillPOBox] [int] NOT NULL,
    [BillUSTerritory] [int] NOT NULL,
    [BillMilitaryAddress] [int] NOT NULL,
    [ShipFirstName] [nvarchar](30) NOT NULL,
    [ShipMiddleName] [nvarchar](30) NOT NULL,
    [ShipLastName] [nvarchar](30) NOT NULL,
    [ShipCompany] [nvarchar](60) NOT NULL,
    [ShipStreet1] [nvarchar](60) NOT NULL,
    [ShipStreet2] [nvarchar](60) NOT NULL,
    [ShipStreet3] [nvarchar](60) NOT NULL,
    [ShipCity] [nvarchar](50) NOT NULL,
    [ShipStateProvCode] [nvarchar](50) NOT NULL,
    [ShipPostalCode] [nvarchar](20) NOT NULL,
    [ShipCountryCode] [nvarchar](50) NOT NULL,
    [ShipPhone] [nvarchar](25) NOT NULL,
    [ShipFax] [nvarchar](35) NOT NULL,
    [ShipEmail] [nvarchar](100) NOT NULL,
    [ShipWebsite] [nvarchar](50) NOT NULL,
    [ShipAddressValidationSuggestionCount] [int] NOT NULL,
    [ShipAddressValidationStatus] [int] NOT NULL,
    [ShipAddressValidationError] [nvarchar](300) NOT NULL,
    [ShipResidentialStatus] [int] NOT NULL,
    [ShipPOBox] [int] NOT NULL,
    [ShipUSTerritory] [int] NOT NULL,
    [ShipMilitaryAddress] [int] NOT NULL,
    [RollupItemCount] [int] NOT NULL,
    [RollupItemName] [nvarchar](300) NULL,
    [RollupItemCode] [nvarchar](300) NULL,
    [RollupItemSKU] [nvarchar](100) NULL,
    [RollupItemLocation] [nvarchar](255) NULL,
    [RollupItemQuantity] [float] NULL,
    [RollupItemTotalWeight] [float] NOT NULL,
    [RollupNoteCount] [int] NOT NULL,
    [BillNameParseStatus] [int] NOT NULL,
    [BillUnparsedName] [nvarchar](100) NOT NULL,
    [ShipNameParseStatus] [int] NOT NULL,
    [ShipUnparsedName] [nvarchar](100) NOT NULL,
    [ShipSenseHashKey] [nvarchar](64) NOT NULL,
    [ShipSenseRecognitionStatus] [int] NOT NULL,
    [ShipAddressType] [int] NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerID])
GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]
GO

ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Store] FOREIGN KEY([StoreID])
REFERENCES [dbo].[Store] ([StoreID])
GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Store]
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'StoreID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Store' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'StoreID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Customer' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Order Number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderNumberComplete'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderTotal'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OnlineCustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OnlineStatusCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'BillState' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'BillCountry' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'ShipState' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'ShipCountry' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillNameParseStatus'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipNameParseStatus'
GO
sql-server t-sql
Martin Hope
zerodoc
Asked: 2014-07-25 08:47:08 +0800 CST
  • 7

Eu tenho um procedimento armazenado

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, 
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX([ShipWorks].[dbo].[OrderItem].[Location]) 
         ELSE MAX([Picklist].[dbo].[ItemData].[InventoryLocation])
    END AS Location, 
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity, 
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem] 
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] 
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID] 
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [ShipWorks].[dbo] [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location

que retorna isso:

Code              Location                                  Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AISLE                                   1           M
GM22660003-EA      B1K2                                      1           M

Desejo dividir a coluna Localização em várias colunas.

Code               loc1  loc2  loc3  loc4  loc5 loc6        Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AI                                      1           M
GM22660003-EA      B1K2                                      1           M

Eu encontrei esta resposta, mas era para postgresql . Também isso , mas retorna uma tabela com várias linhas e você pode selecionar o 1 superior, mas quero todos os resultados e não tenho certeza de como usar o tvf Split(string, ',') no procedimento armazenado para obter o conjunto de resultados que eu quero?

Esta instrução select resulta em um erro: 'Não é possível executar uma função de agregação em uma expressão que contém uma agregação ou uma subconsulta.'

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, 
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i)) 
         ELSE MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i))
    END AS Location, 
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity, 
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem] 
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] 
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID] 
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location
sql-server t-sql
Martin Hope
zerodoc
Asked: 2014-05-24 11:04:16 +0800 CST
  • 2

Eu tenho um procedimento armazenado que é executado com o gerenciador de tarefas do Windows e desejo adicionar uma declaração de condição que executa outro procedimento armazenado se a condição for verdadeira. Eu estava pensando em uma instrução case, mas recebo uma sintaxe inválida perto de CASE e, de acordo com esta resposta, a execução de um procedimento armazenado em uma cláusula select ou case não é permitida, como eu executaria um procedimento armazenado em uma tabela se uma condição fosse atendida.

Este é o procedimento armazenado:

 SET NOCOUNT ON;

 UPDATE Supply
 SET [Quantity] = [Quantity] - [SupplyHalfLife] 

 UPDATE Supply  
 SET SupplyAlert = CASE WHEN Quantity <= SupplyQuantityAlert THEN 'Y' ELSE 'N' END

e é isso que eu quero adicionar a ele:

SELECT a.SupplyName
CASE a.SupplyAlert
WHEN 'Y' THEN dbo.sp_SendMail @body = a.SupplyName END
FROM Supply a

que verifica a coluna para um status de alerta de 'Y' e executa um procedimento armazenado.

Código que finalmente usei:

DECLARE @name varchar(255)

DECLARE cur CURSOR LOCAL FOR
SELECT SupplyName FROM Supply WHERE SupplyAlert = 'Y'

OPEN cur

FETCH NEXT FROM cur INTO @name

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC dbo.SendMail @name

    FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur

link

stored-procedures case