1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dbms output group by query

Discussion in 'SQL PL/SQL' started by Sparamanga, Dec 4, 2017.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi I need to check various columns of a particular table to see if they are not really being populated or the data is always the same. So that we can perhaps remove some columns going forward. This table will be filtered by std_job_no column as i have a list of values for this column which pertain to a particular area of the data.

    To retrieve the column names for the table i have written this which produces the correct results.. note i do not require std_job_no column as i will be using this later as part of the group by query.

    SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE OWNER ='ELLIPSE'
    AND TABLE_NAME='MSF690'
    AND COLUMN_NAME<>'STD_JOB_NO'
    ORDER BY TABLE_NAME

    Now i want to loop through these column names and substitute column_name into the following query

    select
    COL,UMN_NAME, COUNT(STD_JOB_NO)
    from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1'
    GROUP BY COLUMN_NAME

    So i would then get the list of all values for each column name and how often they are populated.

    It should be something along the lines of..

    DECLARE

    BEGIN
    FOR RRR IN (

    SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE OWNER ='ELLIPSE'
    AND TABLE_NAME='MSF690'
    AND COLUMN_NAME<>'STD_JOB_NO'
    ORDER BY TABLE_NAME
    )
    LOOP
    EXECUTE IMMEDIATE 'select ' ||
    RRR.COLUMN_NAME || ', COUNT(STD_JOB_NO)
    from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1'
    GROUP BY RRR.COLUMN_NAME'

    and then i want to dbms_output this without inserting the values into a table as I don't have permissions to create tables.

    Am i along the right lines?

    Thanks,

    Rob
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,538
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, however you need to declare variables for the query output so you can use dbms_output to display those values; the EXECUTE IMMEDIATE command also takes the INTO argument that requires one or more variable names:

    execute immediate sqlstr into myfirstval, mysecondval;

    dbms_output.put_line(myfirstval||' '||mysecondval);
     
  3. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi Zargon,

    Thanks for the quick reply.

    I have been working on the query this morning and will paste the code below.

    Unfortunately i am getting this error..

    Error report:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 20
    01422. 00000 - "exact fetch returns more than requested number of rows"
    *Cause: The number specified in exact fetch is less than the rows returned.
    *Action: Rewrite the query or change number of rows requested

    This is the code as of this morning..

    DECLARE
    TABLE_COUNT INTEGER;
    COLUMN_DATA NVARCHAR2(100);
    STATEMENT NVARCHAR2(1000) := q'[from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1']';

    BEGIN
    FOR RRR IN (
    SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE OWNER ='ELLIPSE'
    AND TABLE_NAME='MSF690'
    AND COLUMN_NAME<>'STD_JOB_NO'
    --AND COLUMN_NAME='ACCOUNT_CODE'
    ORDER BY TABLE_NAME
    )
    LOOP
    EXECUTE IMMEDIATE 'SELECT ' || RRR.COLUMN_NAME || ', COUNT(STD_JOB_NO) ' || STATEMENT || ' GROUP BY ' || RRR.COLUMN_NAME INTO COLUMN_DATA, TABLE_COUNT;
    --DBMS_OUPUT.PUT_LINE(RRR.COLUMN_NAME);
    DBMS_OUTPUT.PUT_LINE(COLUMN_DATA);
    DBMS_OUTPUT.PUT_LINE(TABLE_COUNT);

    END LOOP;


    END;

    Think I am returning too many results for the variables.

    Basically I am trying to run the below query for every column in the table

    SELECT account_code, COUNT(STD_JOB_NO) from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1'
    GROUP BY ACCOUNT_CODE

    which gives results like this..

    ACCOUNT_CODE COUNT(STD_JOB_NO)
    YYYY 6
    NNNN 32

    Thanks,

    Rob.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Of corse, your query will be terminate with error TO_MAY_ROWS .
    Code (Text):

    DECLARE
    TABLE_COUNT ora_mining_number_nt;
    COLUMN_DATA ora_mining_varchar2_nt;
    STATEMENT NVARCHAR2(1000) := q'[from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1']';

    BEGIN
    FOR RRR IN (
    SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE OWNER ='ELLIPSE'
    AND TABLE_NAME='MSF690'
    AND COLUMN_NAME<>'STD_JOB_NO'
    --AND COLUMN_NAME='ACCOUNT_CODE'
    ORDER BY TABLE_NAME
    )
    LOOP
      EXECUTE IMMEDIATE 'SELECT  ' || RRR.COLUMN_NAME || ', COUNT(STD_JOB_NO) ' || STATEMENT || ' GROUP BY ' || RRR.COLUMN_NAME  BULK COLLECT INTO COLUMN_DATA, TABLE_COUNT;
      --DBMS_OUPUT.PUT_LINE(RRR.COLUMN_NAME);
      FOR z  IN 1 .. column_data.count
      LOOP          
        DBMS_OUTPUT.PUT_LINE('COLUMN_DATA('||z||')'||COLUMN_DATA(z));
        DBMS_OUTPUT.PUT_LINE('TABLE_COUNT('||z||')'||TABLE_COUNT(z));

      END LOOP;

    END LOOP;


    END;
     

    What is result you waited as result ?
    You need to find out the number of rows in which the fields with NOT NULL for the district_code are filled RTK1?