October 17, 2006 at 5:36 pm
Hi,
These following SQL give an error,
select DCC,TYPE,NoOfRecord
from (SELECT DCC.Description as 'DCC','AVLS' as 'TYPE', COUNT(LOGON_TYPE.Callsign) as NoOfRecord
FROM DCC LEFT OUTER JOIN
CALLSIGN ON DCC.DCC_ID = CALLSIGN.DCC LEFT OUTER JOIN
LOGON_TYPE ON CALLSIGN.Callsign = LOGON_TYPE.Callsign
AND CALLSIGN.CLIENT_GROUP=1
AND LOGON_TYPE.TIME_RECV>=CONVERT(DATETIME, DBO.YEARFIRSTDAY(GETDATE()),102)
AND LOGON_TYPE.TIME_RECV<=CONVERT(DATETIME,DBO.YEARLASTDAY(GETDATE()),102)
WHERE (DCC.CCC = (select ccc from dcc where description='IBUPEJABAT POLIS KONTINJEN KUALA LUMPUR'))
GROUP BY DCC.DESCRIPTION
union all
SELECT DCC.Description as 'DCC','TEXT' as 'TYPE', COUNT(Text_Message.Callsign) as NoOfRecord
FROM DCC LEFT OUTER JOIN
CALLSIGN ON DCC.DCC_ID = CALLSIGN.DCC LEFT OUTER JOIN
Text_Message ON CALLSIGN.Callsign = Text_Message.Callsign
AND CALLSIGN.CLIENT_GROUP=1
AND Text_Message.TIME_RECV>=CONVERT(DATETIME, DBO.YEARFIRSTDAY(GETDATE()),102)
AND Text_Message.TIME_RECV<=CONVERT(DATETIME,DBO.YEARLASTDAY(GETDATE()),102)
WHERE (DCC.CCC = (select ccc from dcc where description='IBUPEJABAT POLIS KONTINJEN KUALA LUMPUR'))
GROUP BY DCC.DESCRIPTION) as a
----------------------
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 104. Database may not be activated yet or may be in transition.
How to using derived table?
October 19, 2006 at 4:50 am
In this type of query you need to ALIAS all tables and use the ALIAS with the columns. Also, in this instance, I can see no point in using a derived table. Something like the following should work:
SELECT D1.[Description] AS 'DCC'
,'AVLS' AS 'TYPE'
,COUNT(T1.Callsign) AS NoOfRecord
FROM DCC AS D1
LEFT JOIN (
CALLSIGN AS C1
JOIN LOGON_TYPE AS T1 ON C1.Callsign = T1.Callsign
AND C1.CLIENT_GROUP=1
AND T1.TIME_RECV>=CONVERT(DATETIME, DBO.YEARFIRSTDAY(GETDATE()),102)
AND T1.TIME_RECV<=CONVERT(DATETIME,DBO.YEARLASTDAY(GETDATE()),102) )
ON D1.DCC_ID = C1.DCC
WHERE EXISTS (
SELECT *
FROM DCC AS D11
WHERE D11.CCC = D1.CCC
AND D11.[Description] = 'IBUPEJABAT POLIS KONTINJEN KUALA LUMPUR')
GROUP BY D1.[Description]
UNION ALL
SELECT D2.[Description] AS 'DCC'
,'TEXT' AS 'TYPE'
,COUNT(T2.Callsign) AS NoOfRecord
FROM DCC AS D2
LEFT JOIN (
CALLSIGN AS C2
JOIN Text_Message AS T2 ON C2.Callsign = T2.Callsign
AND C2.CLIENT_GROUP=1
AND T2.TIME_RECV>=CONVERT(DATETIME, DBO.YEARFIRSTDAY(GETDATE()),102)
AND T2.TIME_RECV<=CONVERT(DATETIME,DBO.YEARLASTDAY(GETDATE()),102) )
ON D2.DCC_ID = C2.DCC
WHERE EXISTS (
SELECT *
FROM DCC AS D21
WHERE D21.CCC = D2.CCC
AND D21.[Description] = 'IBUPEJABAT POLIS KONTINJEN KUALA LUMPUR')
GROUP BY D2.[Description]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply