Forum Stats

  • 3,757,136 Users
  • 2,251,200 Discussions
  • 7,869,738 Comments

Discussions

Categories

Ignoring a column in select query but need to use as a join condition

JSMQ
JSMQ Member Posts: 187
edited Jul 30, 2018 4:10AM in SQL & PL/SQL

Here i have this proc where i need to insert data into target table 4.In all these subqueries first_scan_issues/last_scan_issues/Prior_scan_issues where i need to join based on the scan_id but same time i should not use the scan_id in select query where the grouping result sets are splitting into more granularity.Even i tried with 'Partition by clause' it also gives more number of records. How can we avoid scan_id column and should use the same as join condition. Target table 4

CREATE OR REPLACE PROCEDURE PROC ASBEGININSERT INTO table4  SELECT first_scan_issues.SOA_SECTOR,        first_scan_issues.SOA_REGION,        a.PROJECT,        a.SOLUTION,        first_scan_issues.CNT1,                    first_scan_issues.CNT2,        file_scans.CNT7,        project_files_count.COUNT,          last_scan_issues.CNT3,        last_scan_issues.CNT4,        file_scans.CNT8,       prior_scan_issues.CNT5,        prior_scan_issues.CNT6,         file_scans.CNT9,             CURRENT_DATE      FROM( select PROJECT,SOLUTION,FIRST_SCAN_ID,LAST_SCAN_ID,PRIOR_SCAN_ID from table1 order by project      )a       LEFT OUTER JOIN        (       select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,         COUNT(DISTINCT ISSUE_ID) CNT3,        COUNT(DISTINCT FILE_ID) CNT4        from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID        ) first_scan_issues on first_scan_issues.SCAN_ID = a.FIRST_SCAN_ID AND first_scan_issues.PROJECT =a.PROJECT      LEFT OUTER JOIN       (      select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,         COUNT(DISTINCT ISSUE_ID) CNT3,        COUNT(DISTINCT FILE_ID) CNT4        from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID        )last_scan_issues ON last_scan_issues.SCAN_ID = a.LAST_SCAN_ID AND last_scan_issues.PROJECT =a.PROJECT      LEFT OUTER JOIN      (      select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,         COUNT(DISTINCT ISSUE_ID) CNT3,        COUNT(DISTINCT FILE_ID) CNT4        from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID      ) prior_scan_issues ON PRIOR_scan_issues.SCAN_ID = a.PRIOR_SCAN_ID AND prior_scan_issues.PROJECT =a.PROJECT      LEFT OUTER JOIN      (      select project,solution, count(distinct FIRST_SCAN_ID) CNT7,       count(distinct LAST_SCAN_ID) CNT8,count(distinct PRIOR_SCAN_ID) CNT9      from table1 group by project,solution order by project      )file_scans ON file_scans.PROJECT=a.PROJECT        JOIN      (       SELECT  PROJECT,SOLUTION,SOA_SECTOR,SOA_REGION,       COUNT(DISTINCT PATH) COUNT       FROM table3       GROUP BY  PROJECT,SOLUTION,SOA_SECTOR,SOA_REGION                                ) project_files_count       ON project_files_count.PROJECT = a.PROJECT;END;                              ) project_files_count       ON project_files_count.PROJECT = a.PROJECT;END;

table 4

<span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">SOA_SECTOR VARCHAR2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">100</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> CHAR</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>SOA_REGION VARCHAR2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">100</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> CHAR</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>PROJECT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> VARCHAR2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">256</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> CHAR</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>SOLUTION VARCHAR2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">256</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> CHAR</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>FIRST_SCAN_ISSUE_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>FIRST_FILES_WITH_ISSUES_CNT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>FIRST_SCAN_FILE_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>TOTAL_FILES_FOR_PROJECT_CNT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>LAST_SCAN_ISSUE_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>LAST_FILES_WITH_ISSUES_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>LAST_SCAN_FILE_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>PRIOR_SCAN_ISSUE_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>PRIOR_FILE_WITH_ISSUES_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>PRIOR_SCAN_FILE_CNT  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NUMBER</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">   <br/>LOAD_DATE_TIME  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NOT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">NULL</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> DATE </span>
Frank Kulash
· Share on TwitterShare on Facebook
«13

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 25, 2018 8:15PM 
    JSMQ wrote:Here i have this proc where i need to insert data into target table 4.In all these subqueries first_scan_issues/last_scan_issues/Prior_scan_issues where i need to join based on the scan_id but same time i should not use the scan_id in select query where the grouping result sets are splitting into more granularity.Even i tried with 'Partition by clause' it also gives more number of records. How can we avoid scan_id column and should use the same as join condition. Target table 4
    1. CREATEORREPLACEPROCEDUREPROCAS
    2. BEGIN
    3. INSERTINTOtable4
    4. SELECTfirst_scan_issues.SOA_SECTOR,
    5. first_scan_issues.SOA_REGION,
    6. a.PROJECT,
    7. a.SOLUTION,
    8. first_scan_issues.CNT1,
    9. first_scan_issues.CNT2,
    10. file_scans.CNT7,
    11. project_files_count.COUNT,
    12. last_scan_issues.CNT3,
    13. last_scan_issues.CNT4,
    14. file_scans.CNT8,
    15. prior_scan_issues.CNT5,
    16. prior_scan_issues.CNT6,
    17. file_scans.CNT9,
    18. CURRENT_DATE
    19. FROM(selectPROJECT,SOLUTION,FIRST_SCAN_ID,LAST_SCAN_ID,PRIOR_SCAN_IDfromtable1orderbyproject
    20. )a
    21. LEFTOUTERJOIN
    22. (
    23. selectSOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
    24. COUNT(DISTINCTISSUE_ID)CNT3,
    25. COUNT(DISTINCTFILE_ID)CNT4
    26. fromtable2groupbySOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
    27. )first_scan_issuesonfirst_scan_issues.SCAN_ID=a.FIRST_SCAN_IDANDfirst_scan_issues.PROJECT=a.PROJECT
    28. LEFTOUTERJOIN
    29. (
    30. selectSOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
    31. COUNT(DISTINCTISSUE_ID)CNT3,
    32. COUNT(DISTINCTFILE_ID)CNT4
    33. fromtable2groupbySOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
    34. )last_scan_issuesONlast_scan_issues.SCAN_ID=a.LAST_SCAN_IDANDlast_scan_issues.PROJECT=a.PROJECT
    35. LEFTOUTERJOIN
    36. (
    37. selectSOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
    38. COUNT(DISTINCTISSUE_ID)CNT3,
    39. COUNT(DISTINCTFILE_ID)CNT4
    40. fromtable2groupbySOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
    41. )prior_scan_issuesONPRIOR_scan_issues.SCAN_ID=a.PRIOR_SCAN_IDANDprior_scan_issues.PROJECT=a.PROJECT
    42. LEFTOUTERJOIN
    43. (
    44. selectproject,solution,count(distinctFIRST_SCAN_ID)CNT7,
    45. count(distinctLAST_SCAN_ID)CNT8,count(distinctPRIOR_SCAN_ID)CNT9
    46. fromtable1groupbyproject,solutionorderbyproject
    47. )file_scansONfile_scans.PROJECT=a.PROJECT
    48. JOIN
    49. (
    50. SELECTPROJECT,SOLUTION,SOA_SECTOR,SOA_REGION,
    51. COUNT(DISTINCTPATH)COUNT
    52. FROMtable3
    53. GROUPBYPROJECT,SOLUTION,SOA_SECTOR,SOA_REGION
    54. )project_files_count
    55. ONproject_files_count.PROJECT=a.PROJECT;
    56. END;
    57. )project_files_count
    58. ONproject_files_count.PROJECT=a.PROJECT;
    59. END;
    CREATE OR REPLACE PROCEDURE PROC AS BEGIN INSERT INTO table4  SELECT first_scan_issues.SOA_SECTOR,  first_scan_issues.SOA_REGION,  a.PROJECT,  a.SOLUTION,  first_scan_issues.CNT1,   first_scan_issues.CNT2,   file_scans.CNT7,   project_files_count.COUNT,   last_scan_issues.CNT3,  last_scan_issues.CNT4,  file_scans.CNT8,  prior_scan_issues.CNT5,  prior_scan_issues.CNT6,  file_scans.CNT9,   CURRENT_DATE  FROM( select PROJECT,SOLUTION,FIRST_SCAN_ID,LAST_SCAN_ID,PRIOR_SCAN_ID from table1 order by project  )a  LEFT OUTER JOIN   (  select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,  COUNT(DISTINCT ISSUE_ID) CNT3,  COUNT(DISTINCT FILE_ID) CNT4  from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID  ) first_scan_issues on first_scan_issues.SCAN_ID = a.FIRST_SCAN_ID AND first_scan_issues.PROJECT =a.PROJECT  LEFT OUTER JOIN   (  select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,  COUNT(DISTINCT ISSUE_ID) CNT3,  COUNT(DISTINCT FILE_ID) CNT4  from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID  )last_scan_issues ON last_scan_issues.SCAN_ID = a.LAST_SCAN_ID AND last_scan_issues.PROJECT =a.PROJECT  LEFT OUTER JOIN  (  select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,  COUNT(DISTINCT ISSUE_ID) CNT3,  COUNT(DISTINCT FILE_ID) CNT4  from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID  ) prior_scan_issues ON PRIOR_scan_issues.SCAN_ID = a.PRIOR_SCAN_ID AND prior_scan_issues.PROJECT =a.PROJECT  LEFT OUTER JOIN  (  select project,solution, count(distinct FIRST_SCAN_ID) CNT7,   count(distinct LAST_SCAN_ID) CNT8,count(distinct PRIOR_SCAN_ID) CNT9  from table1 group by project,solution order by project  )file_scans ON file_scans.PROJECT=a.PROJECT   JOIN  (  SELECT PROJECT,SOLUTION,SOA_SECTOR,SOA_REGION,  COUNT(DISTINCT PATH) COUNT  FROM table3  GROUP BY PROJECT,SOLUTION,SOA_SECTOR,SOA_REGION   ) project_files_count  ON project_files_count.PROJECT = a.PROJECT; END;   ) project_files_count  ON project_files_count.PROJECT = a.PROJECT; END;
    table 4
    1. SOA_SECTORVARCHAR2(100CHAR)
    2. SOA_REGIONVARCHAR2(100CHAR)
    3. PROJECTNOTNULLVARCHAR2(256CHAR)
    4. SOLUTIONVARCHAR2(256CHAR)
    5. FIRST_SCAN_ISSUE_CNTNOTNULLNUMBER(10)
    6. FIRST_FILES_WITH_ISSUES_CNTNOTNULLNUMBER(10)
    7. FIRST_SCAN_FILE_CNTNOTNULLNUMBER(10)
    8. TOTAL_FILES_FOR_PROJECT_CNTNOTNULLNUMBER(10)
    9. LAST_SCAN_ISSUE_CNTNOTNULLNUMBER(10)
    10. LAST_FILES_WITH_ISSUES_CNTNOTNULLNUMBER(10)
    11. LAST_SCAN_FILE_CNTNOTNULLNUMBER(10)
    12. PRIOR_SCAN_ISSUE_CNTNOTNULLNUMBER(10)
    13. PRIOR_FILE_WITH_ISSUES_CNTNOTNULLNUMBER(10)
    14. PRIOR_SCAN_FILE_CNTNOTNULLNUMBER(10)
    15. LOAD_DATE_TIMENOTNULLDATE
    SOA_SECTOR VARCHAR2(100 CHAR)  SOA_REGION VARCHAR2(100 CHAR)  PROJECT NOT NULL VARCHAR2(256 CHAR)  SOLUTION VARCHAR2(256 CHAR)  FIRST_SCAN_ISSUE_CNT NOT NULL NUMBER(10)  FIRST_FILES_WITH_ISSUES_CNT NOT NULL NUMBER(10)  FIRST_SCAN_FILE_CNT NOT NULL NUMBER(10)  TOTAL_FILES_FOR_PROJECT_CNT NOT NULL NUMBER(10)  LAST_SCAN_ISSUE_CNT NOT NULL NUMBER(10)  LAST_FILES_WITH_ISSUES_CNT NOT NULL NUMBER(10)  LAST_SCAN_FILE_CNT NOT NULL NUMBER(10)  PRIOR_SCAN_ISSUE_CNT NOT NULL NUMBER(10)  PRIOR_FILE_WITH_ISSUES_CNT NOT NULL NUMBER(10)  PRIOR_SCAN_FILE_CNT NOT NULL NUMBER(10)  LOAD_DATE_TIME NOT NULL DATE

    We  don't have your tables 1-3.

    We don't have your data.

    We don't have your requirements.

    We don't know what should be in table4 after procedure completes.

    How are supposed to discern what is correct in posted procedure & what is incorrect when you decided to NOT tell or show us the expected & correct results?

    How many rows in TABLE1?

    How many rows in TABLE2?

    How many rows in TABLE3?

    How many rows go into TABLE4?

    Please read URL below & provide details as listed in #6 - #9 inclusive

    How do I ask a question on the forums?

    Frank Kulash
    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Jul 25, 2018 7:43PM 
    How can we avoid scan_id column and should use the same as join condition.

    Sorry - your question isn't clear.

    The SELECT list has NOTHING to do with the columns you join on.

    You can join on ANY columns available in the tables or result sets you are joining. You do NOT need to include the join columns in the select list of that query.

    Obviously, though, if you have a subquery A that produces a result set that you want to join to a higher level query B any 'join columns' need to be in the result set that subquery A produces.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,027 Red Diamond
    edited Jul 25, 2018 8:49PM

    By the way:

    ORDER BY in a sub-query (as in line 19 of your code) rarely makes sense.

    COUNT is an Oracle keyword, so it's a really bad column name (line 51 and elsewhere).

    You probably don't need to join any table more than once in this query.  It will be more efficient if you use fewer joins.

    Sorry, I can't answer the question you asked without the information that John (in reply #1), and the Forum FAQ (   ) mentioned.  I hope you will post the required stuff; this could be a very interesting problem.

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy
    edited Jul 26, 2018 2:08AM

    Having order by in a subquery simply makes no sense.

    Just why:

    select ...

    file_scans.CNT9,

    CURRENT_DATE

    FROM(selectPROJECT,SOLUTION,FIRST_SCAN_ID,LAST_SCAN_ID,PRIOR_SCAN_ID from table1 order by project

    ) a

    ...

    ;

    It should be more simple:

    select ...

    file_scans.CNT9,

    CURRENT_DATE

    FROM table1  a

    ...

    That would make more sense.

    Do not abuse the

    select...

    from (select ...)

    join (select ... from select).

    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Jul 26, 2018 5:59AM

    Seems like you need to join then count.

    If you want more help than that, then as the others have mentioned you need to supply more information.

    · Share on TwitterShare on Facebook
  • JSMQ
    JSMQ Member Posts: 187
    edited Jul 26, 2018 9:50AM

    How many rows in TABLE1?  1.3 million records

    How many rows in TABLE2? 10 million

    How many rows in TABLE3?  5 million records

    How many rows go into TABLE4? expecting the same 1.3 million from table 1

    here are the create table for tables 1-3

    CREATE TABLE table1 (  PROJECT       VARCHAR2(256 CHAR) NOT NULL,  SOLUTION      VARCHAR2(256 CHAR),  FIRST_SCAN_ID NUMBER(10)         NOT NULL,  LAST_SCAN_ID  NUMBER(10)         NOT NULL,  PRIOR_SCAN_ID NUMBER(10)         NOT NULL,  CONSTRAINT pk_tmp_first_last_scan_ids_id PRIMARY KEY (PROJECT));CREATE TABLE table2(  ISSUE_ID         NUMBER(10)    CONSTRAINT RPT_ISSUE_SUMMARY_PK    PRIMARY KEY,  SOA_SECTOR       VARCHAR2(128),  SOA_REGION       VARCHAR2(128),  USERNAME         VARCHAR2(128),  PROJECT          VARCHAR2(256),  SOLUTION          VARCHAR2(256),  PATH             VARCHAR2(2048),  TIME             DATE,  CATEGORY         VARCHAR2(512),  TITLE            VARCHAR2(512),  ISSUE_IMP        VARCHAR2(256),  CLICKED          NUMBER(3),  SCAN_ID          NUMBER(10),  SESSION_ID       NUMBER(10),  RULEPACK_RULE_ID NUMBER(10),  FILE_ID          NUMBER(10),  FILE_INFO_ID     NUMBER(10),  SCAN_YEAR_MON    VARCHAR2(7),  SCAN_YEAR        VARCHAR2(4),  SCAN_MONTH       VARCHAR2(2),  LOAD_TIME        DATE NOT NULL);CREATE TABLE table3(  SOA_SECTOR        VARCHAR2(128),  SOA_REGION        VARCHAR2(128),  USERNAME      VARCHAR2(128),  PROJECT       VARCHAR2(256)  NOT NULL,  SOLUTION       VARCHAR2(256),  PATH          VARCHAR2(2048) NOT NULL,  TIME          DATE           NOT NULL,  LOC           NUMBER(10)     NOT NULL,  SCAN_ID       VARCHAR2(256),  SESSION_ID    VARCHAR2(256),  FILE_ID       VARCHAR2(256),  FILE_INFO_ID  VARCHAR2(256),  SCAN_YEAR_MON VARCHAR2(7),  SCAN_YEAR     VARCHAR2(7),  SCAN_MONTH    VARCHAR2(7),  LOAD_DATE_TIME     DATE           NOT NULL,  CONSTRAINT rpt_scan_summary_pk PRIMARY KEY (SCAN_ID, FILE_ID))
    · Share on TwitterShare on Facebook
  • JSMQ
    JSMQ Member Posts: 187
    edited Jul 26, 2018 9:52AM

    If i remove scan_id from the select query then if i join using scan_id here then am getting error - invalid identifier

    1. )a
    2. LEFTOUTERJOIN
    3. (
    4. selectSOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
    5. COUNT(DISTINCTISSUE_ID)CNT3,
    6. COUNT(DISTINCTFILE_ID)CNT4
    7. fromtable2groupbySOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
    8. )first_scan_issuesonfirst_scan_issues.SCAN_ID=a.FIRST_SCAN_ID AND first_scan_issues.PROJECT=a.PROJECT
    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Jul 26, 2018 9:57AM

    And rp pointed out that would happen in the last sentence of his post.

    Like I said - you need to join then count, not the other way round.

    But we really don't know what that would look like in your case since we have no idea what the requirements are - it's not really possible to work out from a select that doesn't fulfil the requirements.

    As the others have said - you need to give sample data, expected result and an explanation of the rules.

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 26, 2018 10:00AM

    It is unwise & foolish  to store computed value like COUNT(*) in static column. See URL below

    Don't store values that can be derived

    You should NOT be duplicating data.

    Consider to CREATE VIEW instead of populating a different table.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,027 Red Diamond
    edited Jul 26, 2018 10:05AM

    Hi,

    JSMQ wrote:If i remove scan_id from the select query then if i join using scan_id here then am getting error - invalid identifier)aLEFTOUTERJOIN(selectSOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,COUNT(DISTINCTISSUE_ID)CNT3,COUNT(DISTINCTFILE_ID)CNT4fromtable2groupbySOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID)first_scan_issuesonfirst_scan_issues.SCAN_ID=a.FIRST_SCAN_ID AND first_scan_issues.PROJECT=a.PROJECT

    That's right.  If there's no scan_id column in first_scan_issues, then first_scan_issues.scan_id is an invalid identifier.

    What's the problem with keeping scan_id in the SELECT clause (line 4 of the code fragment above)?  Whether you include it or not won't change any of the other columns. Whether scan_id is in the GROUP BY clause (line 7) or not changes how many rows are produced, and what the values are, but whether scan_id is in the SELECT clause (line 4) doesn't change those things.

    Once again, post a little sample data and desired results, as described in the Forum FAQ:

    · Share on TwitterShare on Facebook
This discussion has been closed.