how to name my query as a table name and add a dynamic column to it?

Question

good week everyone.
I have the following query :

Select STATUS,USER_TYPE,Count(status) scount FROM TRANSACTIONS tr join TRANSACTION_STATUS_CODES sc on sc.id = tr.user_type join TRANSACTION_USER_TYPES ut on ut.id=tr.user_type WHERE Tr.User_Type = 1 AND Tr.Update_Date BETWEEN TO_DATE('2022-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2022-11-13 23:59:59', 'yyyy-mm-dd HH24:MI:SS') group by status, user_type

the result: ( atleast the result im showing in my UI)

In addition to that I'd like to get a column called Total to sum the values of both (ut1 and ut2 as rowsum) , although specifically with this query I got none UT2 values because I hardcoded usertype1 for studying purposes.

I tried doing select ut1,ut2, ut1+ut2 as "Total" from thiscreatedtable ( and then my select query) but it didnt work.
also I tried using WITH clause so like

With table1 as (my query) and it also didnt work.

one more thing Id like to do is, the table ' TRANSACTION_STATUS_CODES' which is used in the query have DESCRIPTION column, I want to be able to add that description column to my new table without having to use GROUP BY clause on it.

what am I missing? thanks everyone!

Answer 1

Good day Gal and welcome to the Microsoft QnA forums

TO_DATE

According to the tag which you selected to use sql-server-general you are using SQL Server, but your query is not in Transact-SQL (The language which SQL Server uses). The function TO_DATE indicate that you are not using SQL Server but Oracle server.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

The equivalent to the function TO_DATE in SQL Server is using the function CONVERT

https://www.sqlines.com/oracle-to-sql-server/to_date

You should ask the question in relevant forums which discuss Oracle.

how to name my query as a table name

I assume that you want to use a name for the result of the query

This is done using a virtual object named "Common Table Expressions". In Oracle it will look like

WITH  
  VirtualTbaleName AS (<use your query here>)  
SELECT ...  
FROM VirtualTbaleName  

Check the oracle tutorial for CTE: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html

I tried doing select ut1,ut2, ut1+ut2

You are using math operator + but remember that this operator can work on numbers and not NULL or strings (unless there is a convert for example).

I suggest that you close this thread (by marking the answer) and move to Oracle forums. This is not related to SQL Server and this forum is for Microsoft products.