create computed column in temporary table sql-CodePudding

Sales, Date one, Date two

I have above three fields in a temporary table from which I need to create computed column equal to (Date one – Date two) in exact decimal years or days I am running the code below to find the first computed column; however I am getting the error mentioned at the bottom

ALTER TABLE #temptable
ADD [TimeDif_Day] AS (SELECT *, DATEDIFF (DAY, Date_one, Date_two))

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

CodePudding user response：

I'd imagine you need to take off the spurious stuff from what you have:

ALTER TABLE #temptable ADD [TimeDif_Day] AS DATEDIFF (DAY, Date_one, Date_two)

CodePudding user response：

The standard has

CREATE LOCAL TEMPORARY TABLE ... ( Sales ... , Date1 ... , Date2 ... , TimeDif_Day GENERATED ALWAYS AS <computation expression> )

<computation expression> may not reference anything other than the three other columns.

If your DBMS doesn't support this particular feature of the standard, then look for a way to just leave the table as it is and just use a SELECT at the point where you're using it. Otherwise, maybe it's possible in your DBMS to create a second "temporary" table as a view ("temporary view", if such a thing exists) on the first one, but that doesn't seem to be sanctioned by the standard.