'create computed column in temporary table sql
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.
Solution 1:[1]
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)
Solution 2:[2]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | LoztInSpace |
| Solution 2 |