Using a group by to group a select statement

Question

Using a group by to group a select stament

SELECT
    k.Ivalue, k.JOBDESCRIPTION , 
    count( k.Ivalue) as TOTAL 
FROM
    (SELECT  
        a."ID" as Ivalue, b."JOBDESCRIPTION", rq."CURRENTSTATUS" 
     FROM 
        tblG2o_Requests a
     INNER JOIN
        tblG2o_JOBS b ON a."JOBPOSTID" = b."ID"
     INNER JOIN
        (SELECT 
            r.REQUESTID, ir."CURRENTSTATUS" 
         FROM 
            TBLG2O_RESULTSPOOL r 
         INNER JOIN 
            tblG2o_Requests ir ON r.RequestID = ir."ID" 
         WHERE 
            r.ShortListed = '1') rq ON rq.REQUESTID = a."ID"
     WHERE
         "ACTIVE" = '1' 
         AND "DATECOMPLETED" IS NULL
     ORDER BY 
         "REQUESTDATE" DESC) k 
GROUP BY 
    k.JOBDESCRIPTION

am using navicat to access Oracle 12. getting this error [Err] ORA-00932: inconsistent datatypes: expected - got CLOB — Prince, Aug 28 '14 at 13:40

score 1 · Accepted Answer · answered Aug 28 '14 at 11:15

1

What is the question? You seem to be missing the group by clause, and you do not need double quotes around field names unless you have spaces in them, and even then, if TSQL for example, you would use [] in preference.

I had to remove an ORDER BY in the subquery, that isn't allowed unless other conditions demand it (like TOP n in TSQL)

SELECT
      k.Ivalue
    , k.JOBDESCRIPTION
    , COUNT(k.Ivalue) AS TOTAL
FROM (
            SELECT
                  a.ID AS Ivalue
                , b.JOBDESCRIPTION
                , rq.CURRENTSTATUS
            FROM tblG2o_Requests a

                  INNER JOIN tblG2o_JOBS b
                              ON a.JOBPOSTID = b.ID
                  INNER JOIN (
                              SELECT
                                    r.REQUESTID
                                  , ir.CURRENTSTATUS
                              FROM TBLG2O_RESULTSPOOL r
                                    INNER JOIN tblG2o_Requests ir
                                                ON r.RequestID = ir.ID
                              WHERE r.ShortListed = '1'
                        ) rqenter
                              ON rq.REQUESTID = a.ID
            WHERE ACTIVE = '1'
                  AND DATECOMPLETED IS NULL
      ) k
GROUP BY
      k.Ivalue
    , k.JOBDESCRIPTION

answered Aug 28 '14 at 11:15

Paul Maxwell

33,002
3
32
51

l get the following error = [Err] ORA-00932: inconsistent datatypes: expected - got CLOB – Prince Aug 28 '14 at 13:17
Could `ACTIVE` be integer? Change '1' to 1 Could `r.ShortListed` be integer? Change '1' to 1 If that does not help please read the details of the error carefully http://ora-00932.ora-code.com/ :: I know nothing about the data types in those tables so I cannot offer any insight; Please remember most of that query you provided. – Paul Maxwell Aug 28 '14 at 14:02
One thing you might try, comment out every line that references `JOBDESCRIPTION` is that the CLOB perhaps? use `describe` to learn what the data types are of the fields. – Paul Maxwell Aug 28 '14 at 14:04
Thanks it worked , commented out jobdescription . but the problem is l need to show jobdescription – Prince Aug 28 '14 at 19:53
It cannot be used in the group by, so yo will need to re-join to`tblG2o_Requests` and get `JOBDESCRIPTION` that way (after the group by is done) – Paul Maxwell Aug 28 '14 at 21:23
great, pleased to hear that. – Paul Maxwell Aug 29 '14 at 04:32

Prince · Answer 2 · 2014-08-29T04:34:13.360

Finally worked

SELECT k.Ivalue , l.JOBDESCRIPTION , k.TOTAL, k.CURRENTSTATUS FROM ( SELECT a.ID AS Ivalue ,b.ID as JobPostID , rq."CURRENTSTATUS" ,COUNT(a.ID) AS TOTAL FROM tblG2o_Requests a

              INNER JOIN tblG2o_JOBS b
                          ON a."JOBPOSTID" = b.ID
              INNER JOIN (
                          SELECT
                                r."REQUESTID"
                              , ir."CURRENTSTATUS"
                          FROM TBLG2O_RESULTSPOOL r
                                INNER JOIN tblG2o_Requests ir
                                            ON r."REQUESTID" = ir.ID
                          WHERE r."SHORTLISTED" = 1
                    ) rq

                          ON rq."REQUESTID" = a.ID
        WHERE ACTIVE = '1'
              AND DATECOMPLETED IS NULL

GROUP BY a.ID ,b.ID , rq."CURRENTSTATUS" ) k inner join tblG2o_JOBS l on k.JobPostID =l.ID

enter code here

Using a group by to group a select statement

2 Answers2