AnsweredAssumed Answered

How to write SUB query in NSQL?

Question asked by Riya on Sep 20, 2010
Latest reply on Sep 22, 2010 by Riya
Our company is new to CA and i'm trying to write a NSQL query with multiple sub query. Any help or suggestions is truly appreciated.

Here is my sample SQL. I'm trying to translate to NSQL to create a portlet.

>
SELECT
ACT.CLASS,
ACT.ACTUAL_HOURS AS HOURS,
ROUND((ACT.ACTUAL_HOURS/TOT.TOT_HOURS)*100, 2) AS 'PERCENT%'
FROM
(
SELECT SUM(PR.PRACTSUM)/3600 AS ACTUAL_HOURS,
CASE WHEN P.NAME LIKE 'ENH_%' THEN 'Enhancement'
WHEN P.NAME LIKE 'MNT_%' THEN 'Maintainence'
WHEN P.NAME LIKE 'PRJ_%' THEN 'projects'
ELSE 'Others' END AS CLASS
FROM
Niku.INV_INVESTMENTS P,
NIKU.PRTASK TASK,
NIKU.PRASSIGNMENT PR
WHERE
TASK.PRID = PR.PRTASKID AND P.ID = TASK.PRPROJECTID
GROUP BY
CASE WHEN P.NAME LIKE 'ENH_%' THEN 'Enhancement'
WHEN P.NAME LIKE 'MNT_%' THEN 'Maintainence'
WHEN P.NAME LIKE 'PRJ_%' THEN 'projects'
ELSE 'Others' END
) AS ACT,

(
SELECT SUM(PR.PRACTSUM)/3600 AS TOT_HOURS
FROM
Niku.INV_INVESTMENTS P,
NIKU.PRTASK TASK,
NIKU.PRASSIGNMENT PR
WHERE
TASK.PRID = PR.PRTASKID AND P.ID = TASK.PRPROJECTID
) TOT

ORDER BY ACT.CLASS;


Please let me know.

Thank you!
PM

Outcomes