November 13, 2003 at 2:35 am
What does 'DERIVEDTBL' mean in SQL statement?
November 13, 2003 at 3:00 am
It sounds like an alias someone's given to a derived table in a complex query. It's not a SQL keyword AFAIK.
In what context is it used?
Cheers,
- Mark
Cheers,
- Mark
November 13, 2003 at 3:25 am
But
I tried the following complex sql query in SQL Server 2000. I got a syntax error.
SELECT DISTINCT CustomerNo
FROM (SELECT CustomerNo
FROM Customer
WHERE sqno < 0
UNION
(SELECT CP.CustomerNo
FROM CustomerPwd CP INNER JOIN
ClientType CT ON CP.ClientTypeNo = CT.ClientTypeNo INNER JOIN
WebSystem W ON CP.WebSystemNo = W.WebSystemNo
WHERE (W.websystem = 'WebSystem2) AND (CT.ClientType = 'OLD'))
UNION
(SELECT CP.CustomerNo
FROM CustomerPwd CP INNER JOIN
ClientType CT ON CP.ClientTypeNo = CT.ClientTypeNo INNER JOIN
WebSystem W ON CP.WebSystemNo = W.WebSystemNo
WHERE (W.websystem = 'WebSystem1') AND (CT.ClientType = 'NEW'))
UNION
(SELECT 'AAAAAAAAAAA')
UNION
(SELECT ' BBBBBBBBBBB'))
but if I added 'DERIVEDTBL' at the end, it passed.
P.S. in Enterprise Manager, this 'DERIVEDTBL' is added automatically.
November 13, 2003 at 5:59 am
It is an alias and is mandatory when using subqueries (derived tables). As CP is an alias for CustomerPwd in your example. So your query could have looked something like this
SELECT DISTINCT x.CustomerNo
FROM (...)
UNION
(SELECT 'AAAAAAAAAAA')
UNION
(SELECT ' BBBBBBBBBBB')) x
quote:
but if I added 'DERIVEDTBL' at the end, it passed.
because you named the derived table
quote:
P.S. in Enterprise Manager, this 'DERIVEDTBL' is added automatically.
Didn't know this but then I do not use EM to create queries but I suppose it is way that EM makes sure the query is valid.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply