Query on SQL in Report Studio

[gshilpa24]

Hi Folks,

I have a SQL query with around 14 tables which has round 11 left outer joins written by somebody and embedded in an SQL item and attached to the query something like this:

(Select  ....
from A, B, C where --- (join condition )
) L
left outer join on ....
And condition

left outer join on .....
and condition

left outer join on ...
and condition

Select ( ...
from ..
where ..
) M

left outer join on ...
and ...

this way it continues.

So pelase provide me yr valuable inputs if i can do the outer joins in the framework manager level or in the report studio joining the queries.

Please provide yr inputs.Thanks in advance.

Thanks,
Shilpa

[blom0344]

Doing this from the frontend is not a very viable option. From the backend side there are several options:

(1) Create a new database view that stores the entire SQL and import the view in the model.
(2) Create an SQL query subject based on the SQL within the model.
(3) Use existing subjects

My two cents are , that the SQL was produced in the first place, cause regular modelling was either not known or insufficient in this particular case.

[cognostechie]

Shilpa -

Yes, you can do outer joins in Fm but like blom said, you will have to do a lot of work for that. Since the SQL is already written, you can create a Database Query Subject, copy and paste the entire SQL in there and use that Query subject in the report.

[blom0344]

Which is the same as my suggestion (2)   ;D

[cognostechie]

Makes me feel stupid..   :-[

I thought she is new to FM so she probably won't understand what is an 'SQL Query Subject', so I spelled it out.  :)

[gshilpa24]

Thank you very much for your valuable inputs/suggestions.
I tried to create a an SQL query subject as you suggested by going to Actions ->create query subject and using the the method of create datasource and build an SQL option . There i did not give any filters or calculations as such but imported all the tables . Now when i edit the query and paste an SQL, it is not able to take the prompt. I have a prompt in my SQL ,
here it goes,

SET @RefGlobalDate = # prompt('TRADate','SMALLDATETIME')#
set @Manager ='%'+ # prompt('Manager','char(12)')# +'%'

It throws an error.

i feel it is not possible to write a prompt here. I hav the whole query below. Also i would like add few columns and modify the changes in the query. The query is not showing the correct results, It doesn't show up all the employee details. I beleive some join is the problem here. Problem here is someone lese has written the query and i am debugging it  :-[


DECLARE @Tdd AS INTEGER
DECLARE @Thr AS INTEGER
DECLARE @Tmm AS INTEGER
DECLARE  @Manager as char(12)
DECLARE @RefGlobalDate AS SMALLDATETIME
DECLARE @RefDate AS SMALLDATETIME
DECLARE @RefStDate AS SMALLDATETIME
DECLARE @RefEnDate AS SMALLDATETIME
SET @RefGlobalDate = # prompt('TRADate','SMALLDATETIME')#
set @Manager ='%'+ # prompt('Manager','char(12)')# +'%'
SET @Tdd = DATEPART(dd,@RefGlobalDate)
SET @Thr = DATEPART(hh,@RefGlobalDate)
SET @Tmm = DATEPART(mi,@RefGlobalDate)
SET @RefDate = DATEADD(hh,-@Thr, @RefGlobalDate)
SET @RefDate = DATEADD(mi,-@Tmm, @RefDate)
SET @RefDate = DATEADD(dd,1-@Tdd, @RefDate)
SET @RefEnDate = DATEADD(dd,9, @RefDate)
SET @RefStDate = DATEADD(mm,-1, DATEADD(dd,20, @RefDate))
--SELECT @RefGlobalDate as 'RefGlobalDate' , @Tdd as 'Tdd' , @Thr as 'Thr',@Tmm as 'Tmm',@RefDate as 'RefDate' ,@RefEnDate as 'RefEnDate',@RefStDate as 'RefStDate'
--GETDATE()
DECLARE @Tdd1 AS INTEGER
DECLARE @Thr1 AS INTEGER
DECLARE @Tmm1 AS INTEGER
DECLARE @RefGlobalDatePrv AS SMALLDATETIME
DECLARE @RefprvDate AS SMALLDATETIME
DECLARE @RefprvStDate AS SMALLDATETIME
DECLARE @RefprvEnDate AS SMALLDATETIME
SET @RefGlobalDatePrv = dateadd(mm,-1,@RefGlobalDate)
SET @Tdd1 = DATEPART(dd,@RefGlobalDateprv)
SET @Thr1 = DATEPART(hh,@RefGlobalDateprv)
SET @Tmm1 = DATEPART(mi,@RefGlobalDateprv)
SET @RefprvDate = DATEADD(hh,-@Thr1, @RefGlobalDateprv)
SET @RefprvDate = DATEADD(mi,-@Tmm1, @RefprvDate)
SET @RefprvDate = DATEADD(dd,1-@Tdd1, @RefprvDate)
SET @RefprvEnDate = DATEADD(dd,9, @RefprvDate)
SET @RefprvStDate = DATEADD(mm,-1, DATEADD(dd,20, @RefprvDate))
--SELECT @RefGlobalDatePrv as 'RefGlobalDatePrv' , @Tdd1 as 'Tdd1' , @Thr1 as 'Thr1',@Tmm1 as 'Tmm1',@RefprvDate as 'RefprvDate' ,@RefprvEnDate as 'RefprvEnDate',@RefprvStDate as 'RefprvStDate'


SELECT M.MATCLE,
CONVERT(CHAR(10),M.STARTDATE,111) AS STARTDATE,
CONVERT(CHAR(10),M.ENDDATE,111) AS ENDDATE,
CASE
  WHEN M.TENURE > 0  AND M.TENURE <= 6   THEN '0-6 MONTHS'
  WHEN M.TENURE > 6  AND M.TENURE <= 12  THEN '6-12 MONTHS'
  WHEN M.TENURE > 12 AND M.TENURE <= 24 THEN '12-24 MONTHS'
  WHEN M.TENURE > 24 AND M.TENURE <= 36 THEN '24-36 MONTHS'
  WHEN M.TENURE > 36 THEN  '>36 MONTHS'
  ELSE 'NA'
END AS TENURE,
M.H1 as 'BU Head ID',M.BU_HEAD_Name as 'BU HEAD Name', M.H2 As 'Delivery Heads ID',M.D_HEAD_Name as 'Delivery Heads Name', M.WORK, M.GRADE ,'' as 'Grade Category
', M.RATING as 'Last Rating',

CASE
   WHEN M.CURM IS NULL AND M.ENDDATE   > @RefDate AND M.STARTDATE < @RefStDate THEN 'NOTRATED'
   WHEN M.CURM IS NULL AND M.STARTDATE >= @RefStDate AND M.STARTDATE <= @RefDate THEN 'HIRED ON/BEFORE 1ST'
   WHEN M.CURM IS NULL AND M.ENDDATE   < @RefDate THEN 'TERMINATED'
   WHEN M.CURM IS NULL AND M.STARTDATE >= @RefDate THEN 'HIRED AFTER 1ST'
   ELSE 'RATED'
END As Status,

CASE
   WHEN M.CURM IS NOT NULL AND M.CURM = 'A' THEN 'AMBER'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'B' THEN 'BLACK'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'G' THEN 'GREEN'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'R' THEN 'RED'
   ELSE M.CURM
END AS TRARating,



CASE
   WHEN M.PREM IS NULL AND M.ENDDATE   > @RefprvDate AND M.STARTDATE < @RefprvStDate THEN 'NOTRATED'
   WHEN M.PREM IS NULL AND M.STARTDATE >= @RefprvStDate AND M.STARTDATE <= @RefprvDate THEN 'HIRED ON/BEFORE 1ST'
   WHEN M.PREM IS NULL AND M.ENDDATE   < @RefprvDate THEN 'TERMINATED'
   WHEN M.PREM IS NULL AND M.STARTDATE >= @RefprvDate THEN 'HIRED AFTER 1ST'
   WHEN M.PREM IS NOT NULL AND M.PREM = 'A' THEN 'AMBER'
   WHEN M.PREM IS NOT NULL AND M.PREM = 'B' THEN 'BLACK'
   WHEN M.PREM IS NOT NULL AND M.PREM = 'G' THEN 'GREEN'
   WHEN M.PREM IS NOT NULL AND M.PREM = 'R' THEN 'RED'
--   WHEN M.PREM IS NOT NULL THEN M.PREM

   ELSE M.PREM
END AS PREM1,
CASE
   WHEN M.CURM IS NULL AND M.ENDDATE   > @RefDate AND M.STARTDATE < @RefStDate THEN 'NOTRATED'
   WHEN M.CURM IS NULL AND M.STARTDATE >= @RefStDate AND M.STARTDATE <= @RefDate THEN 'HIRED ON/BEFORE 1ST'
   WHEN M.CURM IS NULL AND M.ENDDATE   < @RefDate THEN 'TERMINATED'
   WHEN M.CURM IS NULL AND M.STARTDATE >= @RefDate THEN 'HIRED AFTER 1ST'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'A' THEN 'AMBER'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'B' THEN 'BLACK'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'G' THEN 'GREEN'
   WHEN M.CURM IS NOT NULL AND M.CURM = 'R' THEN 'RED'
   ELSE M.CURM
END AS CURM1,

rtrim(M.PREM)+'->'+rtrim(M.CURM)as Movement,M.Reason as 'Reason',M.Costcode as 'Cost code',M.EmployeeName as 'Employee name' ,@RefGlobalDate as tradate,@RefGlobalDatePrv as pre

FROM
(SELECT  D1.MATCLE AS MATCLE,
        D1.DATENT AS STARTDATE,
        D1.DATSOR AS ENDDATE,
        D1.TENURE AS TENURE,
        E.Reason as Trareason,
   D.STATUS AS PREM,
        E.STATUS AS CURM,
        D2.WORK  AS WORK,
        D3.GRADE AS GRADE,
        D4.RATING AS RATING,
        D5.MATCLE AS H1,
        D6.MATCLE AS H2,
   D7.LIBLON as Reason,
   D8.Costcode as Costcode,
   D1.EmployeeName as EmployeeName,
   D9.BU_HEAD_Name as BU_HEAD_Name,
   D10.D_HEAD_Name as D_HEAD_Name
   

--D2.IDWLKO AS WORKLOCATION
FROM
---LIST OF EMPLOYEES WITH START DATE, END DATE AND TENURE
(SELECT A.NUDOSS, A.MATCLE,rtrim(A.PRENOM) +' '+ rtrim(A.NOMUSE) as EmployeeName, B.DATENT, B.DATSOR,
   CASE WHEN B.DATSOR > GETDATE()
   THEN DATEDIFF(MONTH,B.DATENT, GETDATE()) 
   ELSE DATEDIFF(MONTH,B.DATENT, B.DATSOR)
   END AS TENURE
FROM  HRINU.ZY00 A,  HRINU.ZYES B
WHERE A.NUDOSS = B.NUDOSS
   AND B.DATSOR > @RefDate) AS D1 ---(MATCLE,STDAT,ENDAT,TENURE)

LEFT OUTER JOIN  HRINU.ZYYG D
   ON D1.NUDOSS = D.NUDOSS
   AND D.DTEF00 = @RefprvDate

LEFT OUTER JOIN  HRINU.ZYYG E
   ON D1.NUDOSS = E.NUDOSS
   AND E.DTEF00 = @RefDate

LEFT OUTER JOIN
   (SELECT A.NUDOSS AS NUDOSS, B.IDWKLO AS WORK
   FROM  HRINU.ZY00 A,  HRINU.ZYZP B,  HRINU.ZYTD12 C
   WHERE A.NUDOSS = B.NUDOSS
      AND   A.NUDOSS = C.NUDOSS
      AND   C.CDINFO = 'ZP'   
      AND   C.NUCMAX = B.NULIGN) AS D2
ON D1.NUDOSS = D2.NUDOSS

LEFT OUTER JOIN
   (SELECT A.NUDOSS AS NUDOSSD3,
   CASE WHEN C.GPJB02 <> '' THEN C.GPJB02
   ELSE 'NA'
   END AS GRADE
   FROM  HRINU.ZYAF A,  HRINU.ZC00 B,  HRINU.ZC0A C
   WHERE
      A.IDJB00 = B.IDJB00 AND
      B.NUDOSS = C.NUDOSS AND
      A.DATAFF <= @RefGlobalDate AND
      A.DATFIN >= @RefGlobalDate ) AS D3
ON D1.NUDOSS = D3.NUDOSSD3

LEFT OUTER JOIN
   (SELECT A.nudoss, A.IDPOCR as Costcode from  HRINU.ZY4k a,  HRINU.ZY00 b
      where b.nudoss =a.nudoss and a.DTEN00 >= getdate())as D8
ON D1.nudoss =D8.nudoss

LEFT OUTER JOIN
   (SELECT A.NUDOSS AS NUDOSS, A.RATING AS RATING
   FROM  HRINU.ZYZW A,  HRINU.ZYTD12 B
   WHERE A.NUDOSS = B.NUDOSS AND
      B.CDINFO = 'ZW' AND
      B.NUCMAX = A.NULIGN ) AS D4
ON D1.NUDOSS = D4.NUDOSS

left outer JOIN
   (
SELECT nudoss, substring(@Manager,2,7) AS MATCLE
   FROM  HRINU.ZYYF
   WHERE ALLMGR LIKE  @Manager) AS D5
ON D1.NUDOSS = D5.NUDOSS

left outer JOIN
   (
SELECT AA2.NUDOSS AS NUDOSS , AA1.MATCLE AS MATCLE FROM
      (SELECT A.MATCLE AS MATCLE FROM  HRINU.ZY00 A,  HRINU.ZYYF B
      WHERE A.NUDOSS = B.NUDOSS AND B.IMMGER = 'A384075') AS AA1,  HRINU.ZYYF AA2
   WHERE AA2.ALLMGR LIKE '%'+ RTRIM(AA1.MATCLE) + '%') AS D6
ON D1.NUDOSS = D6.NUDOSS

LEFT OUTER JOIN
   (select A.cdcode ,B.LIBLON from  HRINU.ZD00 A, HRINU.ZD01 B where A.nudoss = b.nudoss and CDSTCO = 'UAK') as D7
ON E.reason = D7.cdcode

LEFT OUTER JOIN
   (select A.matcle as BUHID ,rtrim(A.PRENOM) +' '+ rtrim(A.NOMUSE) as BU_HEAD_Name from  HRINU.ZY00 A) as D9
ON D5.MATCLE = D9.BUHID
LEFT OUTER JOIN
   (select A.matcle as DHID ,rtrim(A.PRENOM) +' '+ rtrim(A.NOMUSE) as D_HEAD_Name from  HRINU.ZY00 A) as D10
ON D6.MATCLE = D10.DHID

) AS M  where
M.h1 is not null ORDER BY M.MATCLE
--sp_help HRINU.ZYyf


Thanks in advance,

[blom0344]

Oh boy (girl?),

you are trying to press a database stored procedure into a Cognos SQL subject. Database stored procedures belong in the database..

Do you now the difference between an SQL statement and a procedure?

[gshilpa24]

Thak u for the response.

I know the difference between an SQL statement and a procedure. The code is a procedure with many scalar vaiables declared and is embedded in an SQL Item in cognos. My mistake. I mentioned as an SQL written.  :-X

[MFGF]

Makes me feel stupid..   :-[

I thought she is new to FM so she probably won't understand what is an 'SQL Query Subject', so I spelled it out.  :)

Stupid?  Definitely not!

Helpful and knowledgable?  Absolutely!  We value all your posts and contributions to the site, and we're very grateful for all the help and advice you give freely! :)

MF.

[cognostechie]

Thanks MFGF ! Appreciate it  and I admire how much time and efforts you put in to help people here. That's great work !!