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
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.
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.
Which is the same as my suggestion (2) ;D
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. :)
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,
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?
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
Quote from: cognostechie on 08 Oct 2010 01:56:40 PM
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.
Thanks MFGF ! Appreciate it and I admire how much time and efforts you put in to help people here. That's great work !!