Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: HAVING / WHERE / COUNT (Version 11g)

    Having a problem, I don't think it's the join, I believe it's in the having/where clause.
    Display the insureds names and the marital status code for mortals who are on a policy with more than two insureds.

    Insured table:

    Code:
    SQL>  select * from insured;
    
    INSURED_ID I  POLICY_ID  MORTAL_ID 
    ----------- - ----------- -----------
           602 P        501        102
           603 s        501        103
           604 c        501        104
           605 p        502        105
           606 S        502        106
           607 C        502        107
           608 P        503        108
           609 c        503        109
           610 p        504        110
           611 C        504        111
           612 P        505        112
           613 s        505        113
           614 c        506        114
           615 C        506        115
           616 P        507        116
           617 S        507        117
           618 c        507        118
           619 p        508        119
           620 C        508        120
    
    19 rows selected.
    Mortal Table:
    (Note, I showed the mortal_id here, even though it isn't to be displayed in the final result)
    Code:
    SQL> select mortal_id, first_name, last_name
      2  from mortal;
    
     MORTAL_ID FIRST_NAME   LAST_NAME
    ---------- ------------ ------------------
           102 THOMAS       SMITH
           103 SarITA       Smith
           104 Glenda       SMith
           105 Tamara       Johnson
           106 Charles      JOHNSON
           107 Randy        Johnson
           108 Deidre       Williams
           109 DAVID        WILLIAMS
           110 John         Vaubel
           111 Kimberly     VAUBEL
           112 DEREK        Ivanson
           113 Jessica      Ivanson
           114 Sherri       WISHER
           115 JOSEPH       Wisher
           116 Bruce        OBrien
           117 Megan        OBRIEN
           118 MELINDA      OBRien
           119 Danielle     Schwitzer-Pless
           120 BEVERLY      Schwitzer-Pless
    I've tried 2 diff ways, seen below:
    Code:
    SQL> SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  WHERE COUNT(POLICY_ID) > 2;
    WHERE COUNT(POLICY_ID) > 2
          *
    ERROR at line 4:
    ORA-00934: group function is not allowed here
    
    SQL> SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  HAVING COUNT(POLICY_ID) > 2;
    SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    
    SQL> SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  WHERE COUNT*(POLICY_ID) > 2;
    WHERE COUNT*(POLICY_ID) > 2
          *
    ERROR at line 4:
    ORA-00904: "COUNT": invalid identifier
    
    
    SQL> SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  WHERE COUNT(POLICY_ID) > 2;
    WHERE COUNT(POLICY_ID) > 2
          *
    ERROR at line 4:
    ORA-00934: group function is not allowed here
    
    
    SQL> SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  HAVING COUNT(POLICY_ID) > 2;
    SELECT FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    
    
    SQL> SELECT MORTAL.FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  HAVING COUNT(POLICY_ID) > 2;
    SELECT MORTAL.FIRST_NAME, LAST_NAME, MARITAL_STATUS_CODE
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    
    
    SQL> SELECT MORTAL.FIRST_NAME, MORTAL.LAST_NAME, MARITAL_STATUS_CODE
      2  FROM MORTAL
      3  INNER JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  HAVING COUNT(POLICY_ID) > 2;
    SELECT MORTAL.FIRST_NAME, MORTAL.LAST_NAME, MARITAL_STATUS_CODE
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    Last edited by bwilson95; 11-24-13 at 20:23. Reason: Bad code

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First,
    add GROUP BY clause additional to HAVING clause to find policies with more than two insureds.

    Second,
    find mortals who are on the policies found in the first step.

    Third,
    Display the insureds names and the marital status code for mortals found in the second step.
    Last edited by tonkuma; 11-25-13 at 00:00. Reason: Break Second sted into Second and Third.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How do you know marital status?

    There was a column MARITAL_STATUS_CODE in your select statements.
    But, it was not in your sample data.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    WITH cnt 
         AS (SELECT policy_id, 
                    mortal_id, 
                    Count(policy_id) POL_CNT 
             FROM   insured 
             GROUP  BY policy_id, 
                       mortal_id 
             HAVING pol_cnt > 2) 
    SELECT first_name, 
           last_name 
    FROM   mortal, 
           cnt 
    WHERE  cnt.mortal_id = mortal.mortal_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2013
    Posts
    57
    Code:
    WITH cnt 
         AS (SELECT policy_id, 
                    mortal_id, 
                    Count(policy_id) "POL_CNT"
             FROM   insured 
             GROUP  BY policy_id, 
                       mortal_id 
             HAVING count(policy_id) > 2) 
    SELECT first_name, 
           last_name 
    FROM   mortal, 
           cnt 
    WHERE  cnt.mortal_id = mortal.mortal_id;
    Altered and played around w/ your code a bit (line 8), still get 'no rows selected'.

    Explain the 'cnt' operator to me? I understand "count' and stored procedures, somewhat, but this shouldnt be that advanced.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, there might be other(more simple or elegant) ways(e.g. analytic COUNT function),
    the following steps would return the expected result.


    Quote Originally Posted by tonkuma View Post
    First,
    add GROUP BY clause additional to HAVING clause to find policies with more than two insureds.
    An example might be
    Code:
    SELECT *
     FROM  (SELECT policy_id
             FROM  insured
             GROUP BY
                   policy_id
             HAVING
                   COUNT(*) > 2
           ) AS p
    /* ... */
    ;
    Second,
    find mortals who are on the policies found in the first step.
    Third,
    Display the insureds names and the marital status code for mortals found in the second step.
    Last edited by tonkuma; 11-25-13 at 13:40. Reason: Add comment on top.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The Mortal_ID is unique in the sample. I assume that is intentional. Grouping on mortal_id is going to give you all of the rows with no effective grouping.

    This works in SQL Server, and by the manual, should work in Oracle 11g (may need some tweaks to syntax, I don't have an Oracle database handy, just now)
    Code:
    with policiesCTE
    as
    (SELECT policy_id, 
           mortal_id, 
           Count(policy_id) over(partition by policy_id) POL_CNT 
    FROM   insured)
    select *
    from policiesCTE p join mortals m on p.mortal_id = m.mortal_id
    where p.pol_cnt > 2
    Last edited by MCrowley; 11-25-13 at 13:09. Reason: Fixed cartesian join in code section

  8. #8
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by tonkuma View Post
    How do you know marital status?

    There was a column MARITAL_STATUS_CODE in your select statements.
    But, it was not in your sample data.
    There is, should have included this, my bad:

    Code:
    SQL> desc mortal
     Name
     --------------------------
     MORTAL_ID
     SEX_TYPE_CODE
     FIRST_NAME
     LAST_NAME
     DOB
     MARITAL_STATUS_CODE
     SSN
     MIDDLE_NAME
     WORK_PHONE

  9. #9
    Join Date
    Sep 2013
    Posts
    57
    I really should have included this, sorry, still no luck with it, think im gonna walk away from it for an hour or two:
    Code:
    SQL> desc insured
     Name
     --------------------------
     INSURED_ID
     INSURED_TYPE_CODE
     POLICY_ID
     MORTAL_ID
     BANK_ACCOUNT_NUM
     INSURED_NUM
    
    SQL> desc mortal
     Name
     --------------------------
     MORTAL_ID
     SEX_TYPE_CODE
     FIRST_NAME
     LAST_NAME
     DOB
     MARITAL_STATUS_CODE
     SSN
     MIDDLE_NAME
     WORK_PHONE

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you actually post the CREATE TABLE & INSERT statements, it would improve the odds that working SELECT would be the provided response.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    If you actually post the CREATE TABLE & INSERT statements, it would improve the odds that working SELECT would be the provided response.
    Oh, ok, .....the tables were previously created. I dont have those statements.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    [oracle@localhost ~]$ sqlplus scott/tiger
    
    SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 25 16:44:20 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set long 32000
    SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;
    
    DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
    --------------------------------------------------------------------------------
    
      CREATE TABLE "SCOTT"."EMP"
       (    "EMPNO" NUMBER(4,0),
            "ENAME" VARCHAR2(10),
            "JOB" VARCHAR2(9),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0),
             CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
    
    DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
    --------------------------------------------------------------------------------
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
    FAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"  ENABLE,
             CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
              REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
    
    DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
    --------------------------------------------------------------------------------
    FAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"
    
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    [oracle@localhost ~]$ sqlplus scott/tiger
    
    SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 25 16:44:20 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set long 32000
    SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;
    
    DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
    --------------------------------------------------------------------------------
    
      CREATE TABLE "SCOTT"."EMP"
       (    "EMPNO" NUMBER(4,0),
            "ENAME" VARCHAR2(10),
            "JOB" VARCHAR2(9),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0),
             CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
    
    DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
    --------------------------------------------------------------------------------
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
    FAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"  ENABLE,
             CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
              REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
    
    DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
    --------------------------------------------------------------------------------
    FAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"
    
    
    SQL>
    Ok, I get it, I can query as to how the table was connected, correct?
    See, This is a basic course, the text doesnt go there, but it seems this is necc. to get the results expected..
    I'll try it now.

    Code:
    SQL> select dbms_metadata.get_ddl('TEAM') FROM DUAL;
    select dbms_metadata.get_ddl('TEAM') FROM DUAL
           *
    ERROR at line 1:
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_DDL'
    
    
    SQL> select dbms_metadata.get_ddl('TABLE','team','Team_id') FROM DUAL;
    ERROR:
    ORA-31603: object "team" of type TABLE not found in schema "Team_id"
    ORA-06512: at "SYS.DBMS_METADATA", line 4018
    ORA-06512: at "SYS.DBMS_METADATA", line 5843
    ORA-06512: at line 1
    
    
    
    no rows selected
    
    SQL> select dbms_metadata.get_ddl('TABLE','School','team') FROM DUAL;
    ERROR:
    ORA-31603: object "School" of type TABLE not found in schema "team"
    ORA-06512: at "SYS.DBMS_METADATA", line 4018
    ORA-06512: at "SYS.DBMS_METADATA", line 5843
    ORA-06512: at line 1
    
    
    
    no rows selected
    
    SQL> select dbms_metadata.get_ddl('TABLE','team','school') FROM DUAL;
    ERROR:
    ORA-31603: object "team" of type TABLE not found in schema "school"
    ORA-06512: at "SYS.DBMS_METADATA", line 4018
    ORA-06512: at "SYS.DBMS_METADATA", line 5843
    ORA-06512: at line 1
    
    
    
    no rows selected
    Okay, I have tried various configurations.
    From your line of code, can you explain the following things?

    Code:
    SQL> set long 32000
    What is this setting?
    SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;


    Code:
    SQL> select dbms_metadata.get_ddl
    I 'get' this, you're asking the DB for the statement that was used to create the table, correct?

    Code:
    ('TABLE','EMP','SCOTT')
    Okay, the 'table' refers to the entity that you are trying to get the data from. Could you explain the ''EMP','SCOTT'' section?
    Is 'EMP' the table name, and 'SCOTT' the username (for permissions)?

    Thanks for your patience. I'm actually trying to learn this,
    bwilson95

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    below shortened to improve readability

    Code:
    SQL> set pages 0
    SQL> spool dbms_metadata.lis
    SQL> desc dbms_metadata
    FUNCTION GET_DDL RETURNS CLOB
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     OBJECT_TYPE                    VARCHAR2                IN
     NAME                           VARCHAR2                IN
     SCHEMA                         VARCHAR2                IN     DEFAULT
     VERSION                        VARCHAR2                IN     DEFAULT
     MODEL                          VARCHAR2                IN     DEFAULT
     TRANSFORM                      VARCHAR2                IN     DEFAULT
    SQL> spool off
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In the examples in (most) manuals, SCOTT is the schema owner, and EMP is one of the tables. They are only used as examples.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •