All Forums Database
CCSlice 36 posts Joined 03/14
06 Apr 2014
Is there an equivalent to the FIRST function?

SELECT DISTINCT‘ 
 ON GOING’ AS RPT_TYPE , CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING ,
  D.NAME AS CATEGORY , B.STATUS_ID AS “TYPE” ,INCIDENT_ID  AS INCIDENT_ID,
  E.RISK AS RISK_REASON , C.STATUS_NAME AS STAT_DESCRIPTION , I.COMP_UNT_NM AS DIVISION ,
  F.AGT_FRST_NM AS FIRST_NAME ,F.AGT_LAST_NM AS LAST_NAME ,(CURRENT_TIMESTAMP (FORMAT 'MM/DD/YYYY HH:MI:SS')(CHAR(20))) AS REPORT_RUN_DATE   
FROM  
(
 SELECT WARNING_ID AS  INCIDENT_ID, SRC_WARNING_KEY   FROM WARNINGS
 UNION
 SELECT BUREAU_ID AS  INCIDENT_ID, SRC_WARNING_KEY FROM BUREAU
  ) A INNER JOIN   WORK_HISTORY B 
 ON   A.SRC_WARNING_KEY = B.ARTFCT_KEY 
 INNER JOIN   WORK_JOB_STATUS C 
 ON   B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID
  INNER JOIN   ORGANIZATION_HIER D 
 ON   D.ORG_ID = B.ORG_ID
  LEFT OUTER JOIN   WARNINGS_DTL_MEMO E 
 ON   E.SRC_WARNING_KEY = A.SRC_WARNING_KEY 
 LEFT OUTER JOIN   AGT_PRFL F 
 ON   F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0)) 
 LEFT OUTER JOIN   AGNT_GROUP_RELATION G 
 ON   F.AGTID  = CAST(G.AGTID AS DECIMAL(38,0))  
  LEFT OUTER JOIN   GROUP_ORGANIZATION_HIER H 
 ON   G.GRP_ID = H.GRP_ID
  LEFT OUTER JOIN   ORGANIZATION_DIVISION I 
 ON   I.SRC_ORG_UNT = H.SRC_ORG_UNT   
WHERE   WORK_JOB_STATUS ID IN ('5609','6934','5128','5118')

Hi,
I am trying to return unique rows for bureaus and warnings from an incident database and have run into a related table that has a one to many relationship. The situation is this a warning or bureau could have many reasons why it was logged but all I require is one (the first reason why the incident was logged).
Without a first function (sorry I use Access a lot), how do I resolve the join the Warnings Dtl Memo table that many reasons that are returned because of the left join?  There are 10's of thousands of records to consider and a Select Distinct will not work.  How is an issue such as this resolved using SQL?  In the code the column is E.Risk as Risk Reason.
 
Let me know your thoughts
 
Thanks.
 

Adharssh 36 posts Joined 08/13
07 Apr 2014

Hi,
I am not sure about the exact date column here. From what you have told, it seems that you need the distinct number of Incident Id and if there are duplicates on it, we should take the incident_id about when it was created.
We have QUALIFY Functions which will act on ROW_NUMBER() and RANK() Window functions that will get the required one.

SELECT 'ON GOING' AS RPT_TYPE , CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING ,
    D.NAME AS CATEGORY , B.STATUS_ID AS “TYPE” ,INCIDENT_ID  AS INCIDENT_ID,
    E.RISK AS RISK_REASON , C.STATUS_NAME AS STAT_DESCRIPTION ,
  I.COMP_UNT_NM AS DIVISION ,   F.AGT_FRST_NM AS FIRST_NAME ,F.AGT_LAST_NM AS LAST_NAME ,
  (CURRENT_TIMESTAMP (FORMAT 'MM/DD/YYYY HH:MI:SS')(CHAR(20))) AS REPORT_RUN_DATE    
FROM  (  
SELECT WARNING_ID AS  INCIDENT_ID, SRC_WARNING_KEY   
FROM WARNINGS  
UNION
 SELECT BUREAU_ID AS  INCIDENT_ID, SRC_WARNING_KEY 
FROM BUREAU   ) A INNER JOIN   WORK_HISTORY B   
 ON   A.SRC_WARNING_KEY = B.ARTFCT_KEY   INNER JOIN   WORK_JOB_STATUS C   
 ON   B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID   INNER JOIN   ORGANIZATION_HIER D   
 ON   D.ORG_ID = B.ORG_ID   LEFT OUTER JOIN   WARNINGS_DTL_MEMO E   
 ON   E.SRC_WARNING_KEY = A.SRC_WARNING_KEY   LEFT OUTER JOIN   AGT_PRFL F   
 ON   F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0))   LEFT OUTER JOIN   AGNT_GROUP_RELATION G   
 ON   F.AGTID  = CAST(G.AGTID AS DECIMAL(38,0))     LEFT OUTER JOIN   GROUP_ORGANIZATION_HIER H   
 ON   G.GRP_ID = H.GRP_ID   LEFT OUTER JOIN   ORGANIZATION_DIVISION I   
 ON   I.SRC_ORG_UNT = H.SRC_ORG_UNT    
WHERE   WORK_JOB_STATUS ID IN ('5609','6934','5128','5118')
QUALIFY ROW_NUMBER() OVER( PARTITION BY INCIDENT_ID order by REPORT_RUN_DATE ASC   )=1

I have given the REPORT_RUN_DATE in the Order by clause, I am not sure abt the date coulmn that you have. This will give the distinct Incident_Ids.
Thanks & Regards,
Adharssh Rao.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

You must sign in to leave a comment.