OQL Query Mapping

[Maher]

I would like to map this query to OQL in my silverlight application:

SELECT DISTINCT (DOS.NODOSSIER) AS DOSSIER,
                DOS.CTE AS CTE,
                DOS.SIGCOR AS MED,
                TRIM(MAL.NOM) AS NOM,
                TRIM(MAL.NOMJF) AS NOMJF,
                TRIM(TRIM(MAL.PRENOM) || ' ' || TRIM(MAL.PRENOM2)) AS PRENOM,
                MAL.SEXE AS SEXE,
                MAL.DATNAI AS DATENAI,
                TRUNC((MONTHS_BETWEEN(SYSDATE, TO_DATE(MAL.DATNAI, 'yyyymmdd'))) / 12) AS AGE,
                DOS.PASSDOS AS PASSDOS,
                MAL.DATDC AS DATEDC,
                GL.UNITE AS UNITE,
                GL.NUMETAGE AS ETAGE,
                GL.NUMCHAMBRE AS CHAMBRE,
                GL.PLACELIT AS LIT,
                GL.NUMTEL AS TEL,
                TO_CHAR(DATEDEB_MOUV, 'dd/mm/yyyy hh24:mm') AS DEBMOUV,
                TO_CHAR(DATEFIN_MOUV, 'dd/mm/yyyy hh24:mm') AS FINMOUV,
                TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mm') AS DATESYS
                
  FROM IM_MALAD MAL, IM_DOSSIER DOS
  
  LEFT JOIN GLOM_VIEW_SEJOUR GL ON GL.NODOSSIER = DOS.NODOSSIER AND (GL.FLAG_MOUV IS NULL OR GL.FLAG_MOUV = ' ')
AND (GL.FLAGPROVIS_MOUV != 'P' OR GL.FLAGPROVIS_MOUV IS NULL)
AND (GL.FLAG_OCC IS NULL OR GL.FLAG_OCC = ' ')
AND (GL.FLAGPROVIS_OCC != 'P' OR GL.FLAGPROVIS_OCC IS NULL)
  
 WHERE MAL.NODOSSIER = DOS.NODOSSIER   AND MAL.NOM LIKE 'BERNIER%'   AND MAL.SEXE = 'M'
 
 ORDER BY NOM, PRENOM, DOS.NODOSSIER

[Zoran]

Hi Maher,

 This is a complex query using a lot of server-side functions and querying from two tables without a join specified and then joining with a third table. It is not possible to generate similar query using OQL as most of the SQL functions you are using are not available via OQL. There is however, another approach which I find more straight-forward and easier to implement. This query, looks like the perfect candidate for a database View, which you can create on your server. OpenAccess is able to map views to entities so you would get an object with the name of the view and you can easily query that using the IObjectScope. So imagine your view is called "CustomView":

IObjectScope scope = OpenAccessData.ObjectScopeProvider1.GetNewObjectScope();
string query = "SELECT customView FROM CustomViewExtent AS customView";
var result = scope.GetOqlQuery(query).Execute();
foreach (CustomView cst in result)
{
 //do something with the CustomView objects returned.
}

The code above will execute the exact same query you have in the database and materialize it to an appropriate object.

Kind regards,

Zoran
the Telerik team