hi boris,

OK for the view .
I have a good result.

here is the laatst code.

i have still a little problem.

The cast claus is not good ...?

i put CAST( Cor9.IetatId AS I ) AS IetatId and when i haven't no ETATS for R1
i have vart( v_resident.ietatid ) = "X" ?????

it must be 'N' ?

please see the attachment


bernhart

SELECT TELEPH.Cnomteleph ,
R1.Iid ,
R1.Itelephoneid ,
R1.Iidmedecin ,
R1.Iidmedecin2 ,
R1.Ichambreid ,
cast( cor9.ddebut as D ) as ddebut ,
cast( cor9.Ltransfert as L ) as ltransfert ,
CAST( cor9.nPoscatEtat AS N( 2, 0) ) AS nPoscatEtat,
CAST( cPlf( Cor9.ntype , Cor9.nPoscatEtat , Cor9.lcertD , Cor9.ccat_Physiq , Cor9.ltransfert )   AS C(5) )  AS cetats,
Cor9.ddepart,
Cor9.dretour,
CAST( Cor9.nType AS N(1,0) ) AS nEtat_Type  ,
CAST( Cor9.IetatId AS I ) AS IetatId  ,
Cor9.ccat_Physiq ,
R1.Datemodif , R1.Iidmutuelle , R1.Ddna ,
R1.Clna , R1.Csexe , R1.Cnationalit , R1.Corigine ,
R1.Cetat_civil , R1.Cnom_conj , R1.Cci , R1.Creligion ,
R1.Cno_pension , R1.Cno_dom_bq , R1.No_comp_ex , R1.Cregime ,
R1.Nnbrgr , R1.Nnbrbl , R1.Lfst , R1.Nou , R1.Cwe ,
R1.Ctype_lange , R1.Dentree , R1.Dsortie , R1.Ldomicilie ,
R1.Ddeces , R1.Lenveloppe , R1.Lassimille , R1.Lpharmacie ,
R1.Ginfirmier , R1.Gmedecins , R1.Gmenu , R1.Gadminkatz ,
R1.Gadminfact , R1.Cnomtitulaire , R1.Ccodetitulaire ,
R1.Ccode , R1.Cmatricule , R1.Cmatriculetitulaire ,
R1.Lfactureres , R1.Lfacturemut , R1.Nnolit , R1.Cnumtitulaire ,
R1.Ctitulaire , R1.Ihopitalid , R1.Gadmindeces , R1.Lallowprintgdeces ,
R1.Dliberationchambre , R1.Dreservationchambre , R1.Mpa ,
R1.Mpoids , R1.Mpouls , R1.Mtemperature , R1.Lputinpopsy ,
R1.Gadmintraiter , R1.Dfin_linge , R1.Llinge , R1.Ddebut_linge ,
R1.Lcentre_accueil , R1.Ndestinationfacture , R1.Nanreouverture ,
R1.Nsoldeannee , R1.Cservice , R1.Lcpas , R1.Lcartesis ,
R1.Gcartesis , R1.Gdejeuner , R1.Iadressetitulaire ,
R1.lprovisionneAcompte , R1.imatin , R1.imidi , R1.isoir ,
R1.icoucher , R1.cEmplacement_Table , R1.cregistreNational ,
R1.gboissons , R1.ldotartinnes , R1.nLanges1 , R1.nLanges2 ,
R1.nLanges3 , R1.nLanges4 , R1.nLanges5 , R1.nLanges6 ,
R1.nLanges7 , R1.crefCompt , TELEPH.Mboite ,
MUTUELLE.Cmatricule AS cmatriculeMut ,
CAST( NVL( Titre_1.Ctitre, '') + R1.Cservice AS c(14) ) AS CtitreService ,
CAST( NVL(RUE_Titulaire.Cnomrue , '' ) AS C(30) ) AS CnomrueTitulaire ,
CAST( ALLTRIM(NVL(Poste_Titulaire.clocalite, '' )) + '-' + NVL(Poste_Titulaire.ccp,'') AS c( 40 ) ) AS clocaliteTitulaire ,
CalcAge( R1.ddna, ICASE(EMPTY( R1.ddeces )  , R1.dsortie , R1.ddeces ) ) AS age ,
cast( PADL( alltr(nvl(Chambre.cnom,'')) , 5 , '.' ) + ICASE( Chambre.nnolit > 1 , '-' + TRANS( R1.nnolit , '@KZR 9'  ), '' ) AS C(7) ) as cchambre,
CalcAge( R1.dentree ,R1.dsortie) AS npresence ,
R1.gsoldeCompte ,
R1.ldomiciliationSolde , R1.Lrelevecompte , R1.lsoldeCompte ,
R1.nProvision , R1.nSoldeCompte , R1.ltuteurjudiciaire ,
R1.dEntree_OA , R1.iRepTelid , R1.lanticipatif , R1.iPausePrepMedId ,
CAST( cDateTimeValue( R1.Dentree , .T. ) AS C(18 ) ) AS cdateEntree ,
CAST( cDateTimeValue( R1.Dsortie , .T. ) AS C(18 ) ) AS cdateSortie
FROM Amline!Resident R1
LEFT OUTER JOIN Amline!Chambre ON R1.Ichambreid = Chambre.Iid
LEFT OUTER JOIN Amline!Titre Titre_1 ON  Chambre.IserviceId = Titre_1.Iid
LEFT OUTER JOIN Amline!Rue RUE_Titulaire ON  R1.Iadressetitulaire = RUE_Titulaire.Iid
LEFT OUTER JOIN Amline!Poste POSTE_Titulaire ON RUE_Titulaire.Iposteid = POSTE_Titulaire.Iid
INNER JOIN Amline!Teleph TELEPH ON R1.Itelephoneid = TELEPH.Iid
LEFT OUTER JOIN Amline!Mutuelle MUTUELLE ON R1.Iidmutuelle = MUTUELLE.Iidmutuelle 
LEFT JOIN
( SELECT  NVL( E.iresidentId , 0 ) AS iresidentId ,
NVL( E.nPoscat , 0 ) AS nPoscatEtat,
NVL( E.lcertD , .F. ) AS  lcertD,
NVL( E.nType, 0 ) AS nType ,
cast( NVL( E.iid , 0 ) as I ) AS IetatId ,
NVL( E.ccat_Physiq , '' ) AS ccat_Physiq ,
NVL( E.lTransfert , .F. ) AS lTransfert  ,
NVL( E.ddebut , {  /  /  } ) AS ddebut,
CAST( nvl( E.ddepart , CTOT("    -  -  T00:00:00") ) AS T ) AS ddepart,
CAST( nvl( E.dRetour , CTOT("    -  -  T00:00:00") ) AS T ) AS dretour
FROM AMLINE!Etats E INNER JOIN  (
SELECT IresidentId,
MAX(nvl(IID,0)) AS IId
FROM AMLINE!Etats GROUP BY IresidentId ) Etst
ON E.IresidentId = Etst.IresidentId  AND  E.IId = Etst.IID ) Cor9 
on R1.iid = cor9.iresidentId
WHERE  ICASE( EMPTY( ALLTR(  ?Vp_nom ) ) , .T. , Teleph.cnomteleph = ALLTR( ?Vp_nom ))
AND ICASE( ?vp_xt = .T. ,  cor9.ltransfert = .T. , .T. )
and ICASE( inlist( .T. , ( ?vp_x1 +  ?vp_x2 + ?vp_x3 + ?vp_x4 + ?vp_x5 > 0 ) , ?vp_xt = .T. ) , 
INLIST( cor9.nType  , ?vp_x1 , ?vp_x2 , ?vp_x3 , ?vp_x4 , ?vp_x5 ) or cor9.ltransfert = .T. , .T. )
AND ICASE( ?Vp_lfacturable = .T. , R1.lfactureres =.T. , .T. )
AND ICASE( ?Vp_lpresent =.T. , EMPTY( TTOD( R1.dsortie ) ) , .T. )