When using ROWNUM as a pseudo column how would you write the following query using T-SQL? Here is the full query. I realize it's written in PL/SQL and I will need to change addition objects.
BASE AS (
SELECT NULL AS ES_CMPNY_STATUS_KEY
,CSG.CMPNY_STATUS_GROUP
,CSG.CMPNY_STATUS_GROUP_ID
,CSG.CMPNY_STATUS_REASON
,CSG.CMPNY_STATUS_REASON_ID
,CSF.CMPNY_CURRENT_STATUS_FLAG
,TRUNC(SYSDATE) AS LOAD_DATE
FROM CMPNY_STATUS_GROUP CSG
CROSS JOIN CMPNY_CURRENT_STATUS_FLAG CSF
ORDER BY CSG.CMPNY_STATUS_GROUP, CSG.CMPNY_STATUS_REASON
)SELECT ROWNUM AS ES_CMPNY_STATUS_KEY
,CMPNY_STATUS_GROUP
,CMPNY_STATUS_GROUP_ID
,CMPNY_STATUS_REASON
,CMPNY_STATUS_REASON_ID
,CMPNY_CURRENT_STATUS_FLAG
,LOAD_DATE
FROM BASE B
UNION
SELECT 0 AS ES_CMPNY_STATUS_KEY
,NULL AS CMPNY_STATUS_GROUP
,0 AS CMPNY_STATUS_GROUP_ID
,NULL AS CMPNY_STATUS_REASON
,0 AS CMPNY_STATUS_REASON_ID
,0 AS CMPNY_CURRENT_STATUS_FLAG
,TRUNC(SYSDATE) AS LOAD_DATE
FROM DUAL
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
What is the Difference between ROWNUM And ROW_NUMBER()? ROWNUM is a "Pseudocolumn" that assigns a number to each row returned by a query. ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows.
The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
ROWID & ROWNUM are pseudocolumns which are not actual columns in the table but behave like actual columns. You can select the values from pseudocolumns like ROWID & ROWNUM. ROWID & ROWNUM are very important pseudocolumns in oracle which is used in data retrieval.
Use row_number()
:
select row_number() over (order by (select null)) as ES_CMPNY_STATUS_KEY
Note that the order by
is needed. The (select null)
appears -- in practice -- to avoid any additional sorting. In general, though, you would include a column that specifies a sort order for the data you want.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With