September 3, 2018 at 10:58 am
Avec tous mes respects .
Je voudrais vous poser un problème J'ai rencontré avec Microsoft SQL Server Management Studio 17 .
Le problème est
La table est composée de champs
[A] [float] NULL,
[float] NULL,
[C] [float] NULL,
[D] [float] NULL,
[E] AS ([A] + + [C]), (Colonna calculée).
[F] AS ([E] + [D]), (Colonna calculée).
Dans Table Microsoft Access et Table Microsoft Excel il fonction correctement et normalement sans problème.
Mais à Microsoft SQL Server Management Studio 17(requête SQL)
Dans le cas de Script /requête (la création de la table ou de la modification ).
Par exemple
CREATE TABLE Table1 (
[A] [float] NULL,
[float] NULL,
[C] [float] NULL,
[D] [float] NULL,
[E] AS ([A] + + [C]), Colonne calculée
[F] AS ([E] + [D]) )
J'ai ce message
« La colonne calculée 'E' dans la table 'Table1' ne peut pas être utilisée dans une autre définition de colonne calculée ».
Veuillez m'informer de la nature du problème et des solutions possibles
Avec tout mon respect et merci
September 3, 2018 at 11:55 am
You can define the "F" column as a combination of the calculation of the two columns, something like (([A] + + [C]) + [D]), but you cannot cascade the calculations
😎
September 3, 2018 at 12:40 pm
Eirikur Eiriksson - Monday, September 3, 2018 11:55 AMYou can define the "F" column as a combination of the calculation of the two columns, something like (([A] + + [C]) + [D]), but you cannot cascade the calculations
😎
Mr: Eirikur Eiriksson
Merci pour votre réponse
Oui, vous avez raison dans ce que vous proposez.
Tant que le calcul est le même E = A + B + C Ou E = A - B - C
A,
B,
C,
D,
E AS ( A + B + C ),
F AS ( E + D )
en peut dit
[F] AS ([A] + + [C] + [D])
Mais le problème est dans le cas de calculs différents soit en utilisant « = » ou méthode « AS »
Être plus clair le Script /requête comme suit
-------------------------------------------
USE [El]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Resu](
[ID] [float] NOT NULL,
[Clas] [float] NULL,
[Gro] [float] NULL,
[Redo] [nvarchar](255) NULL,
[RedoN] [float] NULL,
[Sexe] [float] NULL,
[Act] [float] NULL,
[DeA] [float] NULL,
[DeB] [float] NULL,
[Exa] [float] NULL,
[Ceo] [float] NULL,
[Sui] AS (((Act + DeA + DeB)*3)/2),
[Tot] AS (Moy*Ceo),
[Moy] AS ((Sui + Exa)/5),
CONSTRAINT [PK_Resu] PRIMARY KEY CLUSTERED
(
[ID] 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
---------------------------------
en fin La question est pourquoi le Script /requête (méthode) s’appliquée sans problèmes avec Access et Excel .
et merci
September 4, 2018 at 7:19 am
[/ Quote]
Mr: Eirikur Eiriksson
Thank you for your answer
Yes, you are right in what you propose.
As long as the calculation is the same E = A + B + C or E = A - B - C
A,
B,
C,
D,
E AS (A + B + C),
F AS (E + D)
can be said
[F] AS ([A] + + [C] + [D])
But the problem is in the case of different calculations either using "=" or "AS" method to
be clearer the script / query as follows
-------------------------------------------
USE [El]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]. [Resu] (
[ID] [float] NOT NULL,
[Class] [float] NULL,
[Gro] [float] NULL,
[Redo] [nvarchar] (255) NULL,
[RedoN] [float] NULL,
[Gender] [float] NULL,
[Act] [float] NULL ,
[DeA] [float] NULL,
[DeB] [float] NULL,
[Exa] [float] NULL,
[Ceo] [float] NULL,
[Sui] AS (((Act + DeA + DeB) * 3) / 2),
[Tot] AS (Moy * Ceo),
[Moy] AS ((Sui + Exa) / 5),
CONSTRAINT [PK_Resu] PRIMARY KEY CLUSTERED
(
[ID] 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
---------------------------- -----
in the end The question is why the Script / Query (method) applied without problems with Access and Excel.
and thank you
[/ Quote]
The problem here is that these tools are NOT all identical. Excel and Access operate much more loosely, and SQL Server, being an RDBMS, has to maintain more discipline. As in this case, there is no actual need to cascade the calculations, the solution can be said of the lowest common denominators, so to speak. SQL Server will not be able to calculate a column in a table. However, one CAN uses CROSS APPLY in a query to allow cascaded calculations to occur within a query, view, stored procedure, or function.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 4, 2018 at 12:28 pm
sgmunson - Tuesday, September 4, 2018 7:19 AM[Quote]aabdma20142 - Monday, September 3, 2018 12:40 PM[/ B][Quote]Eirikur Eiriksson - Monday, September 3, 2018 11:55 AM[/ B]You can define the "F" column as a combination of the two elements, something like (([A] + + [C]) + [D]), but you can not cascade the calculations
: cool:[/ Quote]
Mr: Eirikur Eiriksson
Thank you for your answer
Yes, you are right in what you propose.
As long as the calculation is the same E = A + B + C or E = A - B - C
A,
B,
C,
D,
E AS (A + B + C),
F AS (E + D)
can be said
[F] AS ([A] + + [C] + [D])
But the problem is in the case of different calculations either using "=" or "AS" method to
be clearer the script / query as follows
-------------------------------------------
USE [El]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]. [Resu] (
[ID] [float] NOT NULL,
[Class] [float] NULL,
[Gro] [float] NULL,
[Redo] [nvarchar] (255) NULL,
[RedoN] [float] NULL,
[Gender] [float] NULL,
[Act] [float] NULL ,
[DeA] [float] NULL,
[DeB] [float] NULL,
[Exa] [float] NULL,
[Ceo] [float] NULL,
[Sui] AS (((Act + DeA + DeB) * 3) / 2),
[Tot] AS (Moy * Ceo),
[Moy] AS ((Sui + Exa) / 5),
CONSTRAINT [PK_Resu] PRIMARY KEY CLUSTERED
(
[ID] 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
---------------------------- -----
in the end The question is why the Script / Query (method) applied without problems with Access and Excel.
and thank you[/ Quote]
The problem here is that these tools are NOT all identical. Excel and Access operate much more loosely, and SQL Server, being an RDBMS, has to maintain more discipline. As in this case, there is no actual need to cascade the calculations, the solution can be said of the lowest common denominators, so to speak. SQL Server will not be able to calculate a column in a table. However, one CAN uses CROSS APPLY in a query to allow cascaded calculations to occur within a query, view, stored procedure, or function.
Merci pour votre réponse très utiles.
peut donc être utilisée L'autre méthode comme suit
[Sui] AS (((Act + DeA + DeB) * 3) / 2), Aucun Modification
Et Modifier
[Moy] AS ((Sui + Exa) / 5),
Ver
[Moy] AS ((((([Act]+[DeA])+[DeB])*(3))/(2)+[Exa])),
Et Modifier
[Tot] AS (Moy * Ceo),
Ver
[Tot] AS ((((((([Act]+[DeA])+[DeB])*(3))/(2)+[Exa]) / 5)*[Ceo])),
Selon vous, quelles sont les équations les plus appropriées et ne consomment pas les ressources de l'appareil.
Le premier qui échoue.
------------------------------
[Sui] AS (((Act + DeA + DeB) * 3) / 2),
[Moy] AS ((Sui + Exa) / 5),
[Tot] AS (Moy * Ceo),
------------------------------
au la deuxième.
------------------------------
[Sui] AS (((Act + DeA + DeB) * 3) / 2),
[Moy] AS ((((([Act]+[DeA])+[DeB])*(3))/(2)+[Exa])),
[Tot] AS ((((((([Act]+[DeA])+[DeB])*(3))/(2)+[Exa]) / 5)*[Ceo])),
------------------------------
et merci
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply