Avoiding a column from group by clause

Question

0.00/5 (No votes)

See more:

I have this SP, where am trying to insert records in an Oracle target table(11.2). Here in the subquery naming as 'a' where i have a column PROJECT which is one of the joining condition for the subquery 'b' but here am not getting the desired result if i put the column 'project' in the group by clause in subquery 'a'. But at same time i need to refer the same as one of the joining conditions of subquery 'b'

What I have tried:

SQL

create or replace PROCEDURE PROC1 AS 
BEGIN       
INSERT INTO TARGET TABLE
 SELECT
        a.SECT,
        a. REG,
        a.USRN,
        a.PRO_CT,
        a.CATG,
        a.TLT,
        a.SN_YR_MN,
        a.SN_YR,
        a.SN_MN,
        a.UNIQUE_ISSUES_CNT,
        a.FILES_SN_ISS_CT,
        b.TOT_FILES_SN_CT,
        b.TOT_SN_CT,
        CURRENT_DATE
       FROM(select SECT, REG,USRN,PROJECT,count(DISTINCT concat(PROJECT,SOLUTION)) PRO_CT,CATG,TLT,SN_YR_MN,SN_YR,SN_MN,
      count(DISTINCT ISSUE_ID) UNIQUE_ISSUES_CNT,count(DISTINCT PATH)FILES_SN_ISS_CT
      FROM table1
      GROUP BY SECT, REG,PROJECT,USRN,CATG,TLT,SN_YR_MN,SN_YR,SN_MN)a
      JOIN
      (
      SELECT    USRN,
                PROJECT,
                SECT,
                 REG,
                SN_YR_MN,
                SN_YR,
                SN_MN,
                count(DISTINCT RPT_SCAN_SUMMARY.PATH)  TOT_FILES_SN_CT,
                count(DISTINCT RPT_SCAN_SUMMARY.SCAN_ID) TOT_SN_CT
                FROM table2                
                GROUP BY USRN,
                      PROJECT,
                      SECT, 
                       REG,
                      SN_YR_MN,
                      SN_YR,
                      PROJECT,
                      SN_MN) b ON a.USRN = b.USRN AND a.SECT = b.SECT AND 
         a. REG = b. REG AND a.SN_YR_MN = b.SN_YR_MN  AND a.SN_YR= b.SN_YR AND a.SN_MN= b.SN_MN AND a.PROJECT=b.PROJECT;

Posted 12-Jul-18 11:24am

GJSS

Updated 13-Jul-18 6:58am

Bryian Tan

v2

Add a Solution

Comments

CHill60 12-Jul-18 19:25pm

Some sample data and expected results will help us to help you

GJSS 12-Jul-18 23:50pm

It's tricky part to explain with sample data here.I can get my desired output by removing column 'PROJECT' from subquery 'a' but need to retain the same in select query where i need to refer the same in subquery 'b' in the join condition.

CHill60 13-Jul-18 3:43am

Sample data is not tricky ... there are only two tables and a couple of rows from each is often enough to get the gist of what is required. Without seeing the "desired output" and knowing in what way things are going wrong how can we possibly help you?
By the way - if you use the "Reply" link next to a comment, the poster is notified that you have responded. They won't be notified if you just leave random comments on your own post

GJSS 13-Jul-18 10:50am

Here is my expected result

SECT REG USRN PRO_CT CATG TLT SN_YR_MN SN_YR SN_MN UQ_ISS_CT FILES_SN_ISS_CT
GCA ASPK cl845 2 Log Handling Use of System.out.println 2018-05 2018 05 23 4
GCA ASPK cl846 1 Log Handling Unsanitized data written to logs 2018-05 2018 05 1 1
GCA ASPK cl847 1 Pin Management Hardcoded Pin 2018-05 2018 05 2 2
GCA ASPK cl848 2 Race Condition Use of SimpleDateFormat class 2018-05 2018 05 95 8
GCA ASPK cl849 1 Code Correctness Secure Randomization - java.lang.Math 2018-05 2018 05 4 1
GCA ASPK cl850 1 Code Correctness Secure Randomization - java.util.Random 2018-05 2018 05 20 2
GCA ASPK cl851 1 Information Leakage HTML Comments in JSP 2018-05 2018 05 3 2
GCA ASPK cl852 2 Information Leakage Use of printStackTrace 2018-05 2018 05 23 6
GCA ASPK cl853 1 Password Management Hardcoded password 2018-05 2018 05 1 1
GCA ASPK cl854 1 Cross-Site Scripting Unsanitized data written to an output web page 2018-05 2018 05 9 1
GCA ASPK cl855 1 Validation and sanitization of untrusted data Validate data contained in hidden field 2018-05 2018 05 1 1
GCA ASPK cl856 2 Validation and sanitization of untrusted data Untrusted data being used without validation 2018-05 2018 05 64 3

GJSS 13-Jul-18 10:51am

Sorry ..am not able to format it.

GJSS 13-Jul-18 10:55am

Actual result
SECT REG USRN PRO_CT CATG TLT SN_YR_MN SN_YR SN_MN UQ_ISS_CT FILES_SN_ISS_CT
GC ASPAC cl84433 1 Log Handling Use of System.out.println 2018-05 2018 05 6 2
GC ASPAC cl84433 1 Log Handling Use of System.out.println 2018-05 2018 05 17 2
GC ASPAC cl84433 1 Log Handling Unsanitized data written to logs 2018-05 2018 05 1 1
GC ASPAC cl84433 1 Pin Management Hardcoded Pin 2018-05 2018 05 2 2
GC ASPAC cl84433 1 Race Condition Use of SimpleDateFormat class 2018-05 2018 05 23 2
GC ASPAC cl84433 1 Race Condition Use of SimpleDateFormat class 2018-05 2018 05 72 6
GC ASPAC cl84433 1 Code Correctness Secure Randomization - java.lang.Math 2018-05 2018 05 4 1
GC ASPAC cl84433 1 Code Correctness Secure Randomization - java.util.Random 2018-05 2018 05 20 2
GC ASPAC cl84433 1 Information Leakage HTML Comments in JSP 2018-05 2018 05 3 2
GC ASPAC cl84433 1 Information Leakage Use of printStackTrace 2018-05 2018 05 2 2
GC ASPAC cl84433 1 Information Leakage Use of printStackTrace 2018-05 2018 05 21 4
GC ASPAC cl84433 1 Password Management Hardcoded password 2018-05 2018 05 1 1
GC ASPAC cl84433 1 Cross-Site Scripting Unsanitized data written to an output web page 2018-05 2018 05 9 1
GC ASPAC cl84433 1 Validation and sanitization of untrusted data Validate data contained in hidden field 2018-05 2018 05 1 1
GC ASPAC cl84433 1 Validation and sanitization of untrusted data Untrusted data being used without validation 2018-05 2018 05 16 1
GC ASPAC cl84433 1 Validation and sanitization of untrusted data Untrusted data being used without validation 2018-05 2018 05 48 2

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Answer 1 · 2018-07-13T06:58:00

Solution 1

GROUP BY USRN,
         PROJECT,   -- !!!
         SECT,
          REG,
         SN_YR_MN,
         SN_YR,
         PROJECT, --- !!!
         SN_MN) b

"Formatting" you queries better helps to spot these things.

Posted 13-Jul-18 6:58am

Gerry Schmitz

Comments

GJSS 13-Jul-18 17:31pm

It's a typo while copying .but am able to run the query not getting the desired output