1 2 3 Previous Next 30 Replies Latest reply on Jul 30, 2018 8:10 AM by Cookiemonster76

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

    JSMQ

      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 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

      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 
        • 1. Re: Ignoring a column in select query but need to use as a join condition
          John Thorton

          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;

          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

          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?

          • 2. Re: Ignoring a column in select query but need to use as a join condition

            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.

            • 3. Re: Ignoring a column in select query but need to use as a join condition
              Frank Kulash

              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 ( Re: 2. How do I ask a question on the forums?  ) mentioned.  I hope you will post the required stuff; this could be a very interesting problem.

              • 4. Re: Ignoring a column in select query but need to use as a join condition
                BEDE

                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).

                 

                 

                • 5. Re: Ignoring a column in select query but need to use as a join condition
                  Cookiemonster76

                  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.

                  • 6. Re: Ignoring a column in select query but need to use as a join condition
                    JSMQ

                    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)
                    )
                    
                    • 7. Re: Ignoring a column in select query but need to use as a join condition
                      JSMQ

                      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
                      • 8. Re: Ignoring a column in select query but need to use as a join condition
                        Cookiemonster76

                        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.

                        • 9. Re: Ignoring a column in select query but need to use as a join condition
                          John Thorton

                          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.

                          • 10. Re: Ignoring a column in select query but need to use as a join condition
                            Frank Kulash

                            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

                             

                            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

                            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: Re: 2. How do I ask a question on the forums?

                            • 11. Re: Ignoring a column in select query but need to use as a join condition
                              JSMQ

                              If i use scan_id then i should get under group by clause where it splits the records into more granularity level.so i want to avoid scan_id in select query

                               

                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','wallet-infra',null,2197,1,1,54,2197,1,1,2197,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','CBATRANSTOTAX',null,125,1,1,6,125,1,1,125,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','CBATRANSTOZUS',null,125,1,1,9,125,1,1,125,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','QuarterlyStmt',null,216,1,1,8,216,1,1,216,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','Rainbow_Dummy',null,1728,1,1,2,1728,1,1,1728,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','RegisterToken',null,1,1,1,8,1,1,1,1,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','ThreadExample',null,64,1,1,13,64,1,1,64,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','cbol-cbl-apfa',null,157464,1,1,4,157464,1,1,157464,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','cbol-cbl-esa1',null,64000,1,1,6,64000,1,1,64000,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              Insert into EXPORT_TABLE (SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME) values ('GC','ASPAC','citiplannerRA',null,21952,1,1,774,21952,1,1,21952,1,1,to_date('06-JUL-18','DD-MON-RR'));
                              
                              • 12. Re: Ignoring a column in select query but need to use as a join condition
                                Cookiemonster76

                                So we've got create tables for table1, table2 and table3.

                                We've got inserts for export_table - preumably that's one of the 3 tables, you need to state which and give inserts for the other two.

                                You need to give us the expected result and explain the logic to get from the data in the three tables to the expected result.

                                • 13. Re: Ignoring a column in select query but need to use as a join condition
                                  Frank Kulash

                                  Hi,

                                  JSMQ wrote:

                                   

                                  If i use scan_id then i should get under group by clause where it splits the records into more granularity level.so i want to avoid scan_id in select query

                                   

                                  1. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','wallet-infra',null,2197,1,1,54,2197,1,1,2197,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  2. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','CBATRANSTOTAX',null,125,1,1,6,125,1,1,125,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  3. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','CBATRANSTOZUS',null,125,1,1,9,125,1,1,125,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  4. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','QuarterlyStmt',null,216,1,1,8,216,1,1,216,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  5. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','Rainbow_Dummy',null,1728,1,1,2,1728,1,1,1728,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  6. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','RegisterToken',null,1,1,1,8,1,1,1,1,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  7. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','ThreadExample',null,64,1,1,13,64,1,1,64,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  8. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','cbol-cbl-apfa',null,157464,1,1,4,157464,1,1,157464,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  9. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','cbol-cbl-esa1',null,64000,1,1,6,64000,1,1,64000,1,1,to_date('06-JUL-18','DD-MON-RR'));
                                  10. InsertintoEXPORT_TABLE(SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,FIRST_SCAN_ISSUE_CNT,FIRST_FILES_WITH_ISSUES_CNT,FIRST_SCAN_FILE_CNT,TOTAL_FILES_FOR_PROJECT_CNT,LAST_SCAN_ISSUE_CNT,LAST_FILES_WITH_ISSUES_CNT,LAST_SCAN_FILE_CNT,PRIOR_SCAN_ISSUE_CNT,PRIOR_FILE_WITH_ISSUES_CNT,PRIOR_SCAN_FILE_CNT,LOAD_DATE_TIME)values('GC','ASPAC','citiplannerRA',null,21952,1,1,774,21952,1,1,21952,1,1,to_date('06-JUL-18','DD-MON-RR'));

                                  Does export_table correspond to table1, table2, table3 or to table4 in your original message?

                                   

                                  Whichever it is, post CREATE TABLE and INSERT statements for all the other tables, the exact results you want from that data (that is, what you want table4 to contain when the INSERT is finished), and an explanation of how you get those results from that data.

                                  • 14. Re: Ignoring a column in select query but need to use as a join condition
                                    John Thorton

                                    EXPORT_TABLE does NOT exist in your original post of this thread.

                                    Please stop obfuscating reality & provide previously requested details as shown in URL below

                                     

                                    How do I ask a question on the forums?

                                    1 2 3 Previous Next