Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Oracle sql missing expression

I have an Oracle SQL query and running the query, it gives ORA-00936: missing expression. When I hover over the red in Oracle Sql Developer, it says "Syntax Error. Partially Recognized Rules, railroad diagrams. I think there’s something wrong with my Group By. I think Group by needs to have all query columns in it, but I know the last 3 are min/max/avg, so I don’t think it makes sense to add those to the group by separately. What is the proper way to add them to the group by?

 select        
    do.dcode,
    ds.SERIALNO,  
    ds.BASECOMPONENTCODE,
    TO_CHAR (strt.DLOCALECRTDT,'MON') as MON,--this looks like 13-OCT-15 05.19.03.000000000 PM
    Max (do.METRICVALUE)  as MaxCount,   
    min (do.METRICVALUE)  as MinCount,
    avg (do.METRICVALUE)  as AvgCount

          FROM
            TECH_DWH.D_DIM_OUTPUTCOUNT_TBL do
             join (
                     Select d1.dcode,d1.organizationid
                     from  K_D_VW d1
                     where
                    d1.isactive='Y'
         and d1.organizationid = 7500 -- company id
                    ) d on d.dcode=do.dcode
             left join
            TECH_DWH.D_COMPSTAT_SERIAL_NO_MAP_TBL csm on csm.DCOMPONENTSTATEID = do.DCOMPONENTSTATEID
             join TECH_D.D_DIM_SERIAL_NO_TBL  ds on ds.serialnoid = csm.serialnoid
             left join TECH_DWH.d_dim_medianumber_tbl dm on dm.DCOMPONENTSTATEID = csm.DCOMPONENTSTATEID
    left join TECH_DWH.D_DEVICE_COMPSTATE_STRT_TBL strt on strt.DCOMPONENTSTATEID = csm.DCOMPONENTSTATEID

           WHERE
           instr(upper(ds.basecomponentcode),'PRINT')>0  AND --- return only device components
            LENGTH(TRIM(TRANSLATE((do.METRICVALUE), ' +-.0123456789',' '))) is null -- test for only rows with numberic metrivalue's
     AND do.dcode like '0046'
    AND strt.COMPONENTSTATECODE like '%EP_DEVICE%'
            and strt.DLOCALECRTDT >= to_date ( '30-12-2021', 'DD-MM-YYYY' )
            and 
            Group by  --red squiggly at "by", but error line number is following line
             do.dcode,
    ds.SERIALNO,  
    ds.BASECOMPONENTCODE,
    TO_CHAR(strt.DLOCALECRTDT,'MON'),
    do.METRICVALUE; 

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

What’s obvious, is

and 
    Group by  --red squiggly at "by", but error line number is following line
     do.dcode,

What’s that AND doing alone? Remove it.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading