Hibernate Errors: (could not extract ResultSet) & ORA-00923: FROM keyword not found where expected

Question

I am working with Hibernate Named Queries (Native SQL Queries).

Here is the Named Query that I have written in NamedQueries.java:

public static final String FMC_GET_CUSTOMER_DATA_BY_LOCATION_ID_AND_NOTIFICATION_ID = "select fmc_f_decrypt(c.firstName) as firstName, fmc_f_decrypt(c.lastName) as lastName, "
        + "fmc_f_decrypt(c.emailAddress) as emailAddress, fmc_f_decrypt(c.mobileNumber) as mobileNumber, c.mobileCarrier || '_SMS' as mobileCarrier, c.country as country, "
        + "c.language as language, v.vehicleNickname as vehicleNickname, "
        + "CASE WHEN c.COUNTRY='W_COUNTRY_EU' THEN CASE "
        + "WHEN c.pref24hr=1 THEN TO_CHAR(n.MESSAGEID, 'DD.MM.YYYY hh24:mi:ss') ELSE TO_CHAR(n.MESSAGEID, 'DD.MM.YYYY hh:mi:ss AM') END ELSE CASE "
        + "WHEN c.pref24hr=1 THEN TO_CHAR(n.MESSAGEID, 'MM/DD/YYYY hh24:mi:ss') ELSE TO_CHAR(n.MESSAGEID, 'MM/DD/YYYY hh:mi:ss AM') END "
        + "END AS alertDateTime, CASE WHEN c.COUNTRY='W_COUNTRY_EU' THEN TO_CHAR(n.MESSAGEID, 'DD.MM.YYYY') ELSE TO_CHAR(n.MESSAGEID, 'MM/DD/YYYY') END AS alertDate, CASE "
        + "WHEN c.pref24hr=1 THEN TO_CHAR(n.MESSAGEID, 'hh24:mi:ss') ELSE TO_CHAR(n.MESSAGEID, 'hh:mi:ss AM') END AS alertLongTime, CASE"
        + "WHEN c.pref24hr=1 THEN TO_CHAR(n.MESSAGEID, 'hh24:mi') ELSE TO_CHAR(n.MESSAGEID, 'hh:mi AM') END AS alertShortTime, CASE "
        + "WHEN c.COUNTRY='W_COUNTRY_EU' THEN TO_CHAR((v.NEXTCHARGEBEGINTIME AS TIMESTAMP), 'DD.MM.YYYY hh:mi AM') "
        + "ELSE TO_CHAR((v.NEXTCHARGEBEGINTIME AS TIMESTAMP), 'MM/DD/YYYY hh:mi AM') "
        + "END AS nextChargeBeginTIme, ROUND((to_date(TO_CHAR(v.NEXTCHARGEBEGINTIME,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') - to_date(TO_CHAR(t.messageid, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60) "
        + "AS minutesTillNextCharge, "
        + "TO_CHAR(n.MESSAGEID + (TIMETOFULLCHARGE / 60 / 24), 'hh:mi AM') AS chargeEndTime, p.SETTINGLEVEL AS chargePct, n.ACHIEVEMENTCODE as achievementCode, CASE "
        + "WHEN t.batteryperformancestate IN (2,3) THEN 'COLD' WHEN t.batteryperformancestate IN (5,6) THEN 'HOT' ELSE 'NONE' "
        + "END AS hotOrCold, v.VIN as vin, cl.locationname as locationName, c.custid  "
        + "FROM fmc_notified n LEFT OUTER JOIN fmc_tcumessage t ON t.messageid = n.messageid INNER JOIN fmc_vehicle v  ON n.vehicleid = v.vehicleid ON n.vehicleid = v.vehicleid "
        + "INNER JOIN fmc_customer c ON n.custid = c.custid INNER JOIN fmc_preference p  ON p.custid = c.custid AND p.notifiedid = 'W_NTF_BATTLEVEL' "
        + "LEFT OUTER JOIN fmc_chargelocation cl ON cl.locationid = NVL(:locationId,-1) WHERE n.NOTIFIEDPK=:notificationId";

Here is how I am calling that query:

public CustomerDto getCustomerDataByLocationIdAndNotificationId(Integer locationId, Integer notificationId) throws DaoException {

        logger.info("SDN:  Before Getting...");
        CustomerDto result = null;

        Session session = sessionFactory.getCurrentSession();
        Query query = session.createSQLQuery(FMC_GET_CUSTOMER_DATA_BY_LOCATION_ID_AND_NOTIFICATION_ID).addScalar("firstName")
                .addScalar("lastName").addScalar("emailAddress").addScalar("mobileNumber").addScalar("mobileCarrier")
                .addScalar("country").addScalar("language").addScalar("vehicleNickname").addScalar("alertDateTime").addScalar("alertDate").addScalar("alertLongTime").addScalar("alertShortTime")
                .addScalar("nextChargeBeginTime").addScalar("minutesTillNextCharge").addScalar("chargeEndTime").addScalar("chargePct").addScalar("achievementCode").addScalar("hotOrCold")
                .addScalar("vin").addScalar("locationName");
        query.setParameter("locationId", locationId);
        query.setParameter("notificationId", notificationId);
        logger.debug("Here 11");
        query.setResultTransformer(new AliasToBeanResultTransformer(CustomerDto.class));

        try {
            result = (CustomerDto) query.uniqueResult();
            if (logger.isDebugEnabled()) {
                logger.debug(SDNConstants.RESULTSIZE + (result != null ? 1 : -1) + "");
            }
        } catch (Exception ex) {
            logger.error("error", ex);
        }
        return result;
    }

I am trying to populate a Dto, which in this case is the CustomerDto (result), with the returned results of the query. All the scalar values are attributes of the customerDto but for some reason the query is not running as expected and nothing is being returned.

I've tried debugging this issue. Tried logging the the different attributes of the CustomerDto and nothing is logged. There is a null pointer exception thrown. Do I have to use anything in my session.createSQLQuery call when I have "joins" in my sql query? Should I be using .addJoin? Am I getting the results and populating the CustomerDto properly?

Any ideas on what could be wrong?

Thanks!