CODEWITHSUNDEEP
sql-servert-sql
Isaac Tuncar Cedron
Isaac Tuncar Cedron

Reputation: 189

Subquery not allowed

I'm trying to "auto-insert" my MONEY field on this table as the quantity of a product times the price of the product. I have the quantity value but the price will come from the foreign key PRODUCT_ID but I get error message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

This is the table. How can I do this "auto-insert"? If it is not possible this way please tell me another way I can do that. Thanks in advance.

CREATE TABLE ORDER_DETAILS
(
NUM_ORDER INT PRIMARY KEY,
PRODUCT_ID VARCHAR(4) NOT NULL,
QUANTIITY INT NOT NULL,
MONEY AS (QUANTITY * (SELECT PRICE FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = PRODUCT_ID)),
ORDER_DATE DATETIME NOT NULL DEFAULT GETDATE()
)

Upvotes: 0

Views: 347

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You should create a user defined function for this.

CREATE TABLE ORDER_DETAILS
(
NUM_ORDER INT PRIMARY KEY,
PRODUCT_ID VARCHAR(4) NOT NULL,
QUANTIITY INT NOT NULL,
MONEY AS dbo.YourFunction(Quntity,ProductId),
ORDER_DATE DATETIME NOT NULL DEFAULT GETDATE()
)

Upvotes: 1

Fka
Fka

Reputation: 6234

You can't write this line:

MONEY AS (QUANTITY * (SELECT PRICE FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = PRODUCT_ID))

To count automatically value of this attribute you have to use one of these:

  1. Trigger
  2. User defined function

Upvotes: 0

Related Questions