Trying to Convert MS Access Query to Oracle SQL 1

[cantona]

Hi,

I have the following query written in MS Access which i now need to convert to oracle. Ive managed to get most of it done but struggling a bit with the group by clause.

Original Query;

SELECT

IIf(FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION Is Null,"",FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION & " / " & FES_UNIT_INSTANCE_OCCURRENCES.OFFERING_ORGANISATION) AS [Prog/Curr Area], FES_ORGANISATION_UNITS.FES_FULL_NAME, FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION, FES_UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE, FES_UNIT_INSTANCES.FES_LONG_DESCRIPTION, Sum(IIf(FES_APPLICATION_UNITS.CALENDAR_OCCURRENCE_CODE="0607",1,0)) AS Apps_0607, IIf([ENROLS0607] Is Null,0,[ENROLS0607]) AS 0607_Enrols, Sum(IIf(FES_APPLICATION_UNITS.CALENDAR_OCCURRENCE_CODE="0708",1,0)) AS Apps_0708

FROM

(((FES_UNIT_INSTANCE_OCCURRENCES INNER JOIN FES_UNIT_INSTANCES ON FES_UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE = FES_UNIT_INSTANCES.FES_UNIT_INSTANCE_CODE) LEFT JOIN FES_APPLICATION_UNITS ON FES_UNIT_INSTANCE_OCCURRENCES.UIO_ID = FES_APPLICATION_UNITS.UIO_ID) LEFT JOIN FES_DC_REG_UNITS_0607 ON FES_UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE = FES_DC_REG_UNITS_0607.FES_UNIT_INSTANCE_CODE) LEFT JOIN FES_ORGANISATION_UNITS ON FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION = FES_ORGANISATION_UNITS.ORGANISATION_CODE

WHERE

(((FES_UNIT_INSTANCES.UNIT_CATEGORY) In ("CRS","APP")) AND ((FES_UNIT_INSTANCE_OCCURRENCES.FES_USER_4) In ("C","CN")) AND ((FES_UNIT_INSTANCE_OCCURRENCES.CALOCC_OCCURRENCE_CODE) In ("0607","0708")))

GROUP BY

IIf(FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION Is Null,"",FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION & " / " & FES_UNIT_INSTANCE_OCCURRENCES.OFFERING_ORGANISATION), FES_ORGANISATION_UNITS.FES_FULL_NAME, FES_UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION, FES_UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE, FES_UNIT_INSTANCES.FES_LONG_DESCRIPTION, IIf([ENROLS0607] Is Null,0,[ENROLS0607]);

So far, ive managed to come up with this rough SQL;

SELECT Case When unit_instance_occurrences.Owning_organisation Is Null then '' Else unit_instance_occurrences.Owning_organisation||''||unit_instance_occurrences.offering_organisation End As Prog_Area, ORGANISATION_UNITS.FES_FULL_NAME, UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION, UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE, UNIT_INSTANCES.FES_LONG_DESCRIPTION, Sum(Case When application_units.calendar_occurrence_code = '0607' then 1 else 0 end) As Apps_0607,
Case When enrols0607 Is Null then 0 else enrols0607 end As Enrols_0607,Sum(Case When application_units.calendar_occurrence_code = '0708' then 1 else 0 end) As Apps_0708

FROM

Unit_instance_occurrences,
Unit_instances,
Application_units,
dc_reg_units_0607,
Organisation_units

WHERE

unit_instance_occurrences.fes_uins_instance_code = unit_instances.fes_unit_instance_code and
unit_instance_occurrences.uio_id = application_units.uio_id (+) and
unit_instance_occurrences.fes_uins_instance_code = dc_reg_units_0607.fes_unit_instance_code (+) and
unit_instance_occurrences.owning_organisation = organisation_units.organisation_code (+) and
unit_instances.unit_category In ('CRS','APP') and
unit_instance_occurrences.fes_user_4 In ('C','CN') and
unit_instance_occurrences.calocc_occurrence_code In ('0607','0708')

GROUP BY

Case When Owning_organisation Is Null then '' Else Owning_organisation||''||offering_organisation End As Prog_Area,ORGANISATION_UNITS.FES_FULL_NAME, UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION, UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE, UNIT_INSTANCES.FES_LONG_DESCRIPTION,
Case When enrols0607 Is Null then 0 else enrols0607 end As Enrols_0607;

Most of the query works ok but it appears to fall over at the group by clause. What am i doing wrong? i get an error saying SQL command not properly ended. Im not too familiar with writing sql as i usually use MS Access.

 

[Dagon]

Remove the trailing "As Enrols_0607" on the group by.

 

[Dagon]

I can get to "table or view does not exist" with:

SELECT Case When unit_instance_occurrences.Owning_organisation Is Null then '' Else unit_instance_occurrences.Owning_organisation||''||unit_instance_occurrences.offering_organisation End As Prog_Area, ORGANISATION_UNITS.FES_FULL_NAME, UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION, UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE, UNIT_INSTANCES.FES_LONG_DESCRIPTION, Sum(Case When application_units.calendar_occurrence_code = '0607' then 1 else 0 end) As Apps_0607,
Case When enrols0607 Is Null then 0 else enrols0607 end As Enrols_0607,Sum(Case When application_units.calendar_occurrence_code = '0708' then 1 else 0 end) As Apps_0708 
FROM
Unit_instance_occurrences,
Unit_instances,
Application_units,
dc_reg_units_0607,
Organisation_units 
WHERE 
unit_instance_occurrences.fes_uins_instance_code = unit_instances.fes_unit_instance_code and
unit_instance_occurrences.uio_id = application_units.uio_id (+) and
unit_instance_occurrences.fes_uins_instance_code = dc_reg_units_0607.fes_unit_instance_code (+) and
unit_instance_occurrences.owning_organisation = organisation_units.organisation_code (+) and
unit_instances.unit_category In ('CRS','APP') and
unit_instance_occurrences.fes_user_4 In ('C','CN') and
unit_instance_occurrences.calocc_occurrence_code In ('0607','0708') 
GROUP BY 
Case When Owning_organisation Is Null then '' Else Owning_organisation||''||offering_organisation END,ORGANISATION_UNITS.FES_FULL_NAME, UNIT_INSTANCE_OCCURRENCES.OWNING_ORGANISATION, UNIT_INSTANCE_OCCURRENCES.FES_UINS_INSTANCE_CODE, UNIT_INSTANCES.FES_LONG_DESCRIPTION, 
Case When enrols0607 Is Null then 0 else enrols0607 end

I removed two "as" from the group by's and added and "end" to one of the case statements.

 

[cantona]

Hi Dagon,

Thanks for your help! The Sql you supplied works fine on my system! Didnt think to take out the As part. Could you perhaps explain why this is necessary? so i know for future reference

 

[Dagon]

As" is used to create a column alias in the select list, but would be meaningless in the "group by" clause, since you don't need an alias there.