CODEWITHSUNDEEP
sqloracle-databasegroup-by
Nixon
Nixon

Reputation: 41

SQL GROUP BY ORA-00933: SQL command not properly ended --> Error at offset: 498

Query shows duplicate names so I'm trying to group them so that it won't show me duplicate results. But it's giving me errors.

    Select 
TRIM( S90T1.fname)||'  '||(S90T1.lname)  "_CoWorker" ,L16T3.partno,l16T3.shortl62,L16T3.partrev,L16T3.ordno,L16T40.reascode ||'('|| Trim( E10T1.ma_desc)||')' "_reascode",L16T3.datreg "_Date"

 from L16T40, L16T3, S90T1, E10T1


where L16T3.ordno=L16T40.ordno 
and L16T3.logguser= S90T1.uname
and L16T40.reascode=E10T1.reascode
and  L16T3.L16lcode = 45
and  L16T3.datreg > @('DAYS',#DATE)
and L16T40.reascode != 0

ORDER BY 
"_Date" DESC

GROUP BY 
"_CoWorker"

Upvotes: 1

Views: 4929

Answers (3)

David Faber
David Faber

Reputation: 12485

A couple of things. First, ORDER BY should follow GROUP BY. Second, you can't use a column alias in GROUP BY. But I don't know why you're using GROUP BY at all, I don't see any aggregate functions. If you're getting duplicate records, and want to avoid them, then use DISTINCT. Last, I don't know what this line is a reference to: and L16T3.datreg > @('DAYS',#DATE). Are you maybe borrowing syntax from another RDBMS? I have a hunch that perhaps you want TRUNC(SYSDATE), which will give everything from the current day:

Select DISTINCT TRIM( S90T1.fname) || '  ' || (S90T1.lname)  "_CoWorker" 
     , L16T3.partno,l16T3.shortl62
     , L16T3.partrev,L16T3.ordno,L16T40.reascode ||'('|| Trim( E10T1.ma_desc)||')' "_reascode"
     , L16T3.datreg "_Date"
  from L16T40, L16T3, S90T1, E10T1
 where L16T3.ordno = L16T40.ordno 
   and L16T3.logguser = S90T1.uname
   and L16T40.reascode = E10T1.reascode
   and L16T3.L16lcode = 45
   and L16T3.datreg >= TRUNC(SYSDATE) -- @('DAYS',#DATE) ?
   and L16T40.reascode != 0
 ORDER BY "_Date" DESC

Upvotes: 1

Nishant Gupta
Nishant Gupta

Reputation: 3656

ORDER BY should be after GROUP BY.

General Structure of a Query:

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

Correct Query:

Select 
TRIM( S90T1.fname)||'  '||(S90T1.lname) AS CoWorker ,L16T3.partno,l16T3.shortl62,L16T3.partrev,L16T3.ordno,L16T40.reascode ||'('|| Trim( E10T1.ma_desc)||')' AS reascode,L16T3.datreg AS Dte
from L16T40, L16T3, S90T1, E10T1

where L16T3.ordno=L16T40.ordno 
and L16T3.logguser= S90T1.uname
and L16T40.reascode=E10T1.reascode
and  L16T3.L16lcode = 45
and  L16T3.datreg > @('DAYS',#DATE)
and L16T40.reascode != 0

GROUP BY 
CoWorker

ORDER BY 
Dte DESC

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

Use JOIN instead of , Comma to join table.

You can't ORDER BY the alias name.

I think you might not use GROUP BY,There isn't any aggregate function in your query.

You can try to use DISTINCT on subquery.

Select 
    S90T1._CoWorker,
    L16T3.partno,
    l16T3.shortl62,
    L16T3.partrev,
    L16T3.ordno,
    L16T40.reascode ||'('|| Trim( E10T1.ma_desc)||')' "_reascode",
    L16T3.datreg "_Date"
FROM 
    L16T40 
    INNER JOIN  L16T3 ON L16T3.ordno=L16T40.ordno 
    INNER JOIN  
    (
        SELECT DISTINCT TRIM( S90T1.fname)||'  '||(S90T1.lname)  "_CoWorker",uname
        FROM S90T1
    ) S90T1 ON L16T3.logguser= S90T1.uname
    INNER JOIN  E10T1 ON L16T40.reascode=E10T1.reascode
where 
     L16T3.L16lcode = 45
and  L16T3.datreg > @('DAYS',#DATE)
and  L16T40.reascode != 0
ORDER BY 
    L16T3.datreg DESC

Upvotes: 1

Related Questions