Clarity

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

You can just "wrap" your SQL in the NSQL bits;

i.e.

SELECT @SELECT@........
etc
FROM
(
your SQL here
)
WHERE @FILTER@

Thanks Dave, I tried to do that but i have Sub query1 and sub query 2, Nsql was throwing me an error.
here is the sample that i tried using SQL wrap.

>
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PRO:CLASS:CLASS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRO:HOURS:HOURS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRO:PERC:PERC@
FROM
(
SELECT
ACT.CLASS,
ACT.ACTUAL_HOURS AS HOURS,
ROUND((ACT.ACTUAL_HOURS/TOT.TOT_HOURS)*100, 2) AS 'PERC'
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
INV_INVESTMENTS P,
PRTASK TASK,
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
INV_INVESTMENTS P,
PRTASK TASK,
PRASSIGNMENT PR
WHERE
TASK.PRID = PR.PRTASKID AND P.ID = TASK.PRPROJECTID
) TOT
)
WHERE @FILTER@



ERROR:
NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.

riya,

i haven't checked your sql, but i can think of two more things for you to check:

-name your sql wrap bracketed 'table' just after your last close-bracket, and before the 'WHERE @FILTER@ (eg. name it 'sql_wrap')

-also, in your nsql header, use the table_name.column in the select line, ie instead of "@SELECT:DIM:USER_DEF:IMPLIED:PRO:CLASS:CLASS@," use "@SELECT:DIM:USER_DEF:IMPLIED:PRO:sql_wrap.CLASS:CLASS@,"

-search for maxad's posts regarding sql wrapping, they are priceless...

sam.

Thank you samos. I SQL wraped and it works now.

Regards
Riya

Hi Riya,

Problem is with SQL.

Correct NSQL:


SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PRO:CLASS:CLASS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRO:HOURS:HOURS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRO:PERC:PERC@
FROM
(
SELECT
ACT.CLASS,
ACT.ACTUAL_HOURS AS HOURS,
ROUND((ACT.ACTUAL_HOURS/TOT.TOT_HOURS)*100, 2) PERC
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
INV_INVESTMENTS P,
PRTASK TASK,
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
) ACT,
(
SELECT SUM(PR.PRACTSUM)/3600 AS TOT_HOURS
FROM
INV_INVESTMENTS P,
PRTASK TASK,
PRASSIGNMENT PR
WHERE
TASK.PRID = PR.PRTASKID AND P.ID = TASK.PRPROJECTID
) TOT
)
WHERE @FILTER@

Sachin,
Thanks for your reply.My SQL is correct. It works fine when i run it in database.

Regard
PM