PPG PPG - 6 months ago 10
SQL Question

WITH clause with a strange alias

I am familiar with what the

WITH
clause does but I have come across this statement and I am not sure what to make of it:

WITH
H AS (SELECT PS$,HIERY_TYPE,ID$,HIERY_PS$_PARENT,HIERY_TYPE_PARENT,HIERY_ID$_PARENT,LABEL FROM RSPC_HIERARCHY WHERE PS$='SDH' AND HIERY_TYPE LIKE 'STARS%'),
H_TREE(XLEVEL, STARS_FAMILY,PS$,HIERY_TYPE,ID$,HIERY_PS$_PARENT,HIERY_TYPE_PARENT,HIERY_ID$_PARENT,LABEL) AS
(
SELECT 1 XLEVEL, ID$ STARS_FAMILY , PS$,HIERY_TYPE,ID$,HIERY_PS$_PARENT,HIERY_TYPE_PARENT,HIERY_ID$_PARENT,LABEL FROM H WHERE HIERY_TYPE ='STARS_FAMILY'
UNION ALL
SELECT HT.XLEVEL + 1 XLEVEL, HT.STARS_FAMILY,H.PS$,H.HIERY_TYPE,H.ID$,H.HIERY_PS$_PARENT,H.HIERY_TYPE_PARENT,H.HIERY_ID$_PARENT, H.LABEL
FROM H, H_TREE HT)


In particular, I don't understand the
H_TREE
alias. What is the function of the stuff in the parenthesis?

Answer

The "stuff in parentheses" simply a list of the names of the columns for the alias. I think create view supports the same construct.

Normally, the column names are taken directly from the select. But they can also be specified before the as.