5 Replies Latest reply on Jan 18, 2019 1:54 PM by Frank Kulash

    How can i add a sub query for a new column on this table?

    ForSly

      This query creates a table but the resultant table has dups on  ACT_CAND ,  i can get the correct count on ACT_CAND but only after running a subquery on the table. I am looking for way that i can get my count by just executing this code
      select sum(ACT_CAND) from table..(select sum(ACT_CAND ) from my table)

      All the tables involved have a 1:M except for motives_d.app_event_wid = app_fact.appl_csw_motives_wid , the motives_d has a many to many relationship
      with the fact table

       

       

          SELECT
              "CAN_NUM",
              "J_GROUP",
              "PI_COMP_DT",
              "LAST_PI_COMP_DT",
              "DIFF_DATE",
              "J_FAM_N",
              "R_NUM",
              "LST_SM_DT",
              "INTEGRATION_ID",
              "WRKFLW_NM",
              "TRK_STS_NAM",
              "TRK_STEP_NAM",
              "MTVE_NAME",
              "ACT_CAND"
          FROM
              (SELECT
                 APP_FACT.PI_CANDIDATE_NUM AS CAN_NUM,
                 JOB_INFO_D.J_GROUP AS J_GROUP,
                 APP_FACT.PI_COMP_DT AS PI_COMP_DT,
                 NULL AS LAST_PI_COMP_DT,
                 NULL AS DIFF_DATE,
                 JOB_INFO_D.J_FAM_N AS J_FAM_N,
                 REQN_D.R_NUM AS R_NUM,
                 APP_FACT.LAST_SBM_DT AS LST_SM_DT,
                 APP_FACT.INTEGRATION_ID AS INTEGRATION_ID,
                 APP_FACT.WRKFLW_NM AS WRKFLW_NM,
                 APP_FACT.TRK_STS_NAM AS TRK_STS_NAM,
                 APP_FACT.TRK_STEP_NAM AS TRK_STEP_NAM,
                 MOTIVES_D.MTVE_NAME AS MTVE_NAME,
                 CASE WHEN
                   APP_FACT.INITIAL_APP_MEDIUM_ROW_WID IN (SELECT
                                                             ROW_WID
                                                           FROM
                                                             WC_APPLICATION_MEDIUM_D
                                                           WHERE
                                                             CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))
                                                           OR
                                                             APP_FACT.APP_MEDIUM_ROW_WID IN (SELECT
                                                                                               ROW_WID
                                                                                             FROM
                                                                                               WC_APPLICATION_MEDIUM_D
                                                                                             WHERE
                                                                                               CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))
                 THEN 0 ELSE null END ACT_CAND
               FROM
                 ABC_EVENT_F APP_FACT,
                 DEF_ROD_D REQN_D,
                 GHI_INFO_D JOB_INFO_D,
                 JKL_APPLE_MTV_D MOTIVES_D
               WHERE
                 APP_FACT.REQUISITION_ROW_WID = REQN_D.ROW_WID
                 AND APP_FACT.JOB_INFO_ROW_WID = JOB_INFO_D.ROW_WID
                 AND MOTIVES_D.APP_EVENT_WID = APP_FACT.APPL_CSW_MOTIVES_WID
               GROUP BY
                 APP_FACT.PI_CANDIDATE_NUM A,
                 JOB_INFO_D.J_GROUP ,
                 APP_FACT.PI_COMP_DT ,
                 JOB_INFO_D.J_FAM_N ,
                 REQN_D.R_NUM A,
                 APP_FACT.LAST_SBM_DT ,
                 APP_FACT.INTEGRATION_ID ,
                 APP_FACT.WRKFLW_NM,
                 APP_FACT.TRK_STS_NAM ,
                 APP_FACT.TRK_STEP_,
                 MOTIVES_D.MTVE_NAME ,
                 CASE WHEN
                   APP_FACT.INITIAL_APP_MEDIUM_ROW_WID IN (SELECT
                                                             ROW_WID
                                                           FROM
                                                             WC_APPLICATION_MEDIUM_D
                                                           WHERE
                                                             CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))
                                                             OR
                                                             APP_FACT.APP_MEDIUM_ROW_WID IN (SELECT
                                                                                               ROW_WID
                                                                                             FROM
                                                                                               WC_APPLICATION_MEDIUM_D
                                                                                             WHERE
                                                                                               CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))
                 THEN 0 ELSE null
               ORDER BY
                 APP_FACT.PI_CANDIDATE_NUM,
                 APP_FACT.INTEGRATION_ID,
                 JOB_INFO_D.J_GROUP ASC ) IR1
          WHERE
              IR1.ACT_CAND = 0;

        • 1. Re: How can i add a sub query for a new column on this table?
          L. Fernigrini

          Please include data structure definition and some sample data if possible, following the forum guidelines:

          Re: 2. How do I ask a question on the forums?

           

          And it would also help if you change your display name to something meaningful rather than a number:

          Update Your Community Display Name and Avatar!

          • 3. Re: How can i add a sub query for a new column on this table?
            ForSly

            --------------------------------------------------------
            --  DDL for Table MOTIVES_D
            --------------------------------------------------------

              CREATE TABLE MOTIVES_D
               ( "ROW_WID" NUMBER(20,0),
            "PI_NUM" VARCHAR2(20 BYTE),
            "PI_APP_TRK_STS_NUM" VARCHAR2(20 BYTE),
            "PI_APP_TRK_MOTIVE_NUM" VARCHAR2(20 BYTE),
            "PI_MOTIVE_NAME" VARCHAR2(100 BYTE),
            "PI_HIS_ITM_APP_TRK_STEP_NUM" VARCHAR2(20 BYTE),
            "APP_EVENT_WID" VARCHAR2(80 BYTE),
            "INTEGRATION_ID" VARCHAR2(80 BYTE),
            "W_INSERT_DT" DATE,
            "W_UPDATE_DT" DATE
               )
              
              
               --------------------------------------------------------
            --  DDL for Table  ABC_EVENT_F APP_FACT,
            --------------------------------------------------------

              CREATE TABLE APP_FACT
               (
            "PI_CAND_NUM" VARCHAR2(20 BYTE),
            "PI_COMPLETED_DT" DATE,
              "REQ_ROW_WID"NUMBER,
              "JOB_INFO_ROW_WID" NUMBER,
            "PI_HIS_ITM_APP_TRK_CREATION_DT" DATE,
            "PI_TRK_STS_NAME" VARCHAR2(100 BYTE),
            "PI_TRK_STEP_NAME" VARCHAR2(100 BYTE),
            "PI_TRK_WRKFLW_NAME" VARCHAR2(100 BYTE),
            "INTEGRATION_ID" VARCHAR2(80 BYTE),
            "APP_MEDIUM_ROW_WID" NUMBER,
            "APP_MEDIUM_ROW_WID" NUMBER,
            "CANDIDATE_ROW_WID" NUMBER
               )

               --------------------------------------------------------
            --  DDL for Table WC_APPLICATION_MEDIUM_D
            --------------------------------------------------------

              CREATE TABLE WC_APP_D
               ( "CODE" VARCHAR2(50 BYTE),
              "INTEGRATION_ID" VARCHAR2(80 BYTE),
            "ROW_WID" NUMBER,

               )

            --------------------------------------------------------
            --  DDL for Table DEF_ROD_D REQN_D,
            --------------------------------------------------------

              CREATE TABLE REQN_D
               (
            "REQ_NUM" VARCHAR2(50 BYTE),
              "INTEGRATION_ID" VARCHAR2(80 BYTE),
            "ROW_WID" NUMBER(10,0)
               )

            • 4. Re: How can i add a sub query for a new column on this table?
              ForSly

              I have added the table definition, is that enough  to get you started?

              • 5. Re: How can i add a sub query for a new column on this table?
                Frank Kulash

                Hi,

                3337789 wrote:

                 

                I have added the table definition, is that enough to get you started?

                That's a good start.

                The table names in reply #3 aren't the same as in your original message.  Post a revised version of one or the other (or both), so that the tables needed in the query can be created.

                Are all those columns used in this problem?  Don't include columns that aren't needed.

                 

                Also post INSERT statements for a little sample data, the exact results you want from that sample data, and an explanation of how you get those results from that data.

                 

                Always say which version of Oracle you're using (e.g., 12.2.0.2.0).

                 

                No kidding: you need to read (and follow) the Forum FAQ: Re: 2. How do I ask a question on the forums?