Re: Re: fetching on joined table
Hi , Thank you everyone for your information about my problem. It's true I just want to read my data, because for modification or recording, this side is already set. To Chuck, I think that we can make everything with a framework ( WO) and even if it is difficult but there is always an issue. To Joe, I do not necessarily execute SQL but just wondering if there is a way to do this in WO? Or make a stored procedure with my complex SQL query in the lower part and just call it in WO. It would avoid me to every new version of my database to touch my WO code. I'm just looking for ways and John gave me a way Message du : 13/04/2011 De : John Huss johnth...@gmail.com A : Copie à : naneon.raym...@neuf.fr, webobjects-dev@lists.apple.com Sujet : Re: fetching on joined table IF your data is read-only and you really, really want to use a custom SQL statement, then it's ok to fetch raw rows and set the SQL using a hint: String sqlQuery = ... NSMutableDictionary hints = new NSMutableDictionary(); hints.setObjectForKey( sqlQuery, EODatabaseContext.CustomQueryExpressionHintKey); fetchSpec.setHints( hints ); fetchSpec.setRawRowKeyPaths( ... ) return editingContext.objectsWithFetchSpecification( fetchSpec ); If this is editable data then this is definitely NOT the way to do it. John On Wed, Apr 13, 2011 at 10:18 AM, Chuck Hill ch...@global-village.net wrote: You will notice that Joe works at a rather well known university too. :-) I'd listen to his advice or decide to not use WO. Chuck On Apr 13, 2011, at 8:00 AM, Joe Little wrote: Others will say similarly, but as someone who once used views, etc, you'll want to avoid these and stored procedures and build out all of this logic in EOF/Modeler and let WebObjects do it's thing. If you are executing SQL for results lists, you are likely trying too hard to outthink WO ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Re: fetching on joined table
Hi Chuck,
thanks for your reply. So I thought about stored procedures? But How I include
in my code and can I cast resultset in EOEntities Objects?
I want to use data in my WOPopUpButton.
Thanks
Ray
Message du : 12/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hi Ray,
WebObjects is not going to generate SQL like that for you (maybe it is
possible, but it would be very difficult). I think that you have two choices:
1. Use views
2. Think and fetch in terms of Objects not tables and rows and joins. This is
going to be VERY different than how you are approaching the data now.
It is possible that WebObjects is just not a good thing to use with your schema.
Chuck
On Apr 12, 2011, at 12:49 AM, naneon.raym...@neuf.fr naneon.raym...@neuf.fr
wrote:
Hi chuck,
I go to give you more informations about data. I work in IT Universtity in
France and we have huge database with lot of table. Here is an example of PDM
(5 tables)
person
students-degree--diploma--academic
year--period_param-admin_resgistration
(fk_person) (fk_student)
(fk_academic_year) (pk an_univ prd_univ) (fields an_univ_prd_univ)
(fk_diploma, fk_person, fk_student, fk_academic_year)
and I have lof view (view_student_course on a period ...),
Here is code of view vue_parcours_profil using to have student curse :
CREATE OR REPLACE FORCE VIEW GEST_UV.VUE_PROFIL_PARCOURS (ETU_ID,
DIPLOME_$C, REORIENT_$F, PRD_DEBUT, PRD_FIN)
AS
SELECT DISTINCT t3.e,
t3.d,
t3.reorient_$f,
MIN(p_deb) over (partition BY t3.e, t3.d, t3.p_fin) debut,
t3.p_fin fin
FROM
(SELECT DISTINCT t1.e,
t1.d,
t1.no,
t1.p p_deb,
NVL(MIN(t2.p) over (partition BY t1.e, t1.d, t1.no), MAX (t1.p) over
(partition BY t1.e, t1.d)) p_fin,
CASE
WHEN t1.no = 1
AND t2.r IS NULL
THEN t1.r
ELSE DECODE(t2.r,NULL,'N',t2.r)
END reorient_$f
--DECODE(t2.r,null,'N',t2.r) reorient_$f
FROM
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia1
) t1,
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia2
) t2
WHERE t1.e = t2.e(+)
AND t1.d = t2.d(+)
AND t1.no t2.no(+)
AND t1.r t2.r(+)
--and t1.r = 'N'
AND ((t1.no = 1
AND NVL(t1.r,'N') IN ('O','N'))
OR (t1.no 1
AND NVL(t1.r,'N') = 'N'))
) t3;
So when I want all students curse in sql developer this my request :
SELECT vtp.etu_id as STUDENT_ID,
vtp.diplome_$c as DIPLOMA_CODE,
DECODE (vtp.diplome_$c,'ING',d.diplome
' ancien règlement',d.diplome) as DIPLOMA_NAME,
vtp.prd_debut as PERIOD_BEGIN,
vtp.prd_fin as PERIOD_END,
gerry.fonc_trad_prd_lib(vtp.prd_debut,'anprd') as ACADEMIC_YEAR_BEGIN,
gerry.fonc_trad_prd_lib(vtp.prd_fin,'anprd') ACADEMIC_YEAR_END,
vtp.reorient_$f as ORIENTATION,
(SELECT DIPL_SPEC_ABR
' '
DIPL_OPT_ABR
' '
NIVEAU_ADM
FROM gerry.vue_eia_abr veia
WHERE veia.etu_id = vtp.etu_id
AND veia.diplome_$c=vtp.diplome_$c
AND veia.an_univ
veia.prd_univ=vtp.prd_fin
) as DIPLOMA_SPECIFICITY,
(SELECT diplome_num
FROM gest_uv.etu_diplome
WHERE etu_id = vtp.etu_id
AND diplome_$c = vtp.diplome_$c
AND an_univ
prd_univ BETWEEN vtp.prd_debut AND vtp.prd_fin
) as DIPLOMA_NUMBER,
(SELECT SITUATION
FROM gerry.etu_inscr_adm eia,
gerry.ref_situation rs
WHERE rs.situation_$c = eia.situation_$c
AND eia.etu_id = vtp.etu_id
AND eia.diplome_$c =vtp.diplome_$c
AND eia.an_univ
eia.prd_univ=vtp.prd_fin
) as STUDENT_SITUATION,
NVL(
(SELECT DECODE(situation_$c,'DEMI','O','N')
FROM gerry.etu_inscr_adm
WHERE etu_id = vtp.etu_id
AND diplome_$c =vtp.diplome_$c
AND situation_$c='DEMI'
AND an_univ
prd_univ=vtp.prd_fin
),'N') as IS_STUDENT_RESIGNED,
NVL(
(SELECT DECODE(situation_$c,'UTX','O','N')
FROM gerry.etu_inscr_adm
WHERE etu_id = vtp.etu_id
AND diplome_$c =vtp.diplome_$c
AND situation_$c='UTX'
AND an_univ
prd_univ=vtp.prd_fin
),'N') as IS_STUDENT_TRANSFERED
FROM gest_uv.VUE_PROFIL_PARCOURS vtp,
gerry.diplome d
WHERE vtp.diplome_$c = d.diplome_$c
Re: fetching on joined table
Others will say similarly, but as someone who once used views, etc, you'll want
to avoid these and stored procedures and build out all of this logic in
EOF/Modeler and let WebObjects do it's thing. If you are executing SQL for
results lists, you are likely trying too hard to outthink WO
On Apr 12, 2011, at 11:46 PM, naneon.raym...@neuf.fr wrote:
Hi Chuck,
thanks for your reply. So I thought about stored procedures? But How I
include in my code and can I cast resultset in EOEntities Objects?
I want to use data in my WOPopUpButton.
Thanks
Ray
Message du : 12/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hi Ray,
WebObjects is not going to generate SQL like that for you (maybe it is
possible, but it would be very difficult). I think that you have two
choices:
1. Use views
2. Think and fetch in terms of Objects not tables and rows and joins. This
is going to be VERY different than how you are approaching the data now.
It is possible that WebObjects is just not a good thing to use with your
schema.
Chuck
On Apr 12, 2011, at 12:49 AM, naneon.raym...@neuf.fr
naneon.raym...@neuf.fr wrote:
Hi chuck,
I go to give you more informations about data. I work in IT Universtity in
France and we have huge database with lot of table. Here is an example of
PDM (5 tables)
person
students-degree--diploma--academic
year--period_param-admin_resgistration
(fk_person)(fk_student)
(fk_academic_year) (pk an_univ prd_univ)(fields an_univ_prd_univ)
(fk_diploma, fk_person, fk_student, fk_academic_year)
and I have lof view (view_student_course on a period ...),
Here is code of view vue_parcours_profil using to have student curse :
CREATE OR REPLACE FORCE VIEW GEST_UV.VUE_PROFIL_PARCOURS (ETU_ID,
DIPLOME_$C, REORIENT_$F, PRD_DEBUT, PRD_FIN)
AS
SELECT DISTINCT t3.e,
t3.d,
t3.reorient_$f,
MIN(p_deb) over (partition BY t3.e, t3.d, t3.p_fin) debut,
t3.p_fin fin
FROM
(SELECT DISTINCT t1.e,
t1.d,
t1.no,
t1.p p_deb,
NVL(MIN(t2.p) over (partition BY t1.e, t1.d, t1.no), MAX (t1.p) over
(partition BY t1.e, t1.d)) p_fin,
CASE
WHEN t1.no = 1
AND t2.r IS NULL
THEN t1.r
ELSE DECODE(t2.r,NULL,'N',t2.r)
END reorient_$f
--DECODE(t2.r,null,'N',t2.r) reorient_$f
FROM
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia1
) t1,
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia2
) t2
WHERE t1.e = t2.e(+)
AND t1.d = t2.d(+)
AND t1.no t2.no(+)
AND t1.r t2.r(+)
--and t1.r = 'N'
AND ((t1.no= 1
AND NVL(t1.r,'N') IN ('O','N'))
OR (t1.no 1
AND NVL(t1.r,'N') = 'N'))
) t3;
So when I want all students curse in sql developer this my request :
SELECT vtp.etu_id as STUDENT_ID,
vtp.diplome_$c as DIPLOMA_CODE,
DECODE (vtp.diplome_$c,'ING',d.diplome
' ancien règlement',d.diplome) as DIPLOMA_NAME,
vtp.prd_debut as PERIOD_BEGIN,
vtp.prd_fin as PERIOD_END,
gerry.fonc_trad_prd_lib(vtp.prd_debut,'anprd') as ACADEMIC_YEAR_BEGIN,
gerry.fonc_trad_prd_lib(vtp.prd_fin,'anprd') ACADEMIC_YEAR_END,
vtp.reorient_$f as ORIENTATION,
(SELECT DIPL_SPEC_ABR
' '
DIPL_OPT_ABR
' '
NIVEAU_ADM
FROM gerry.vue_eia_abr veia
WHERE veia.etu_id = vtp.etu_id
AND veia.diplome_$c=vtp.diplome_$c
AND veia.an_univ
veia.prd_univ=vtp.prd_fin
) as DIPLOMA_SPECIFICITY,
(SELECT diplome_num
FROM gest_uv.etu_diplome
WHERE etu_id = vtp.etu_id
AND diplome_$c = vtp.diplome_$c
AND an_univ
prd_univ BETWEEN vtp.prd_debut AND vtp.prd_fin
) as DIPLOMA_NUMBER,
(SELECT SITUATION
FROM gerry.etu_inscr_adm eia,
gerry.ref_situation rs
WHERE rs.situation_$c = eia.situation_$c
AND eia.etu_id= vtp.etu_id
AND eia.diplome_$c=vtp.diplome_$c
AND eia.an_univ
eia.prd_univ=vtp.prd_fin
) as STUDENT_SITUATION,
NVL(
(SELECT DECODE(situation_$c,'DEMI','O','N')
FROM gerry.etu_inscr_adm
WHERE etu_id= vtp.etu_id
AND diplome_$c =vtp.diplome_
Re: fetching on joined table
You don't cast to EOEntites (correctly, EOEnterpriseObject, EOEntity is the
model), you fetch them from the database. You can't get them from a stored
procedure.
Chuck
On Apr 12, 2011, at 11:46 PM, naneon.raym...@neuf.fr naneon.raym...@neuf.fr
wrote:
Hi Chuck,
thanks for your reply. So I thought about stored procedures? But How I
include in my code and can I cast resultset in EOEntities Objects?
I want to use data in my WOPopUpButton.
Thanks
Ray
Message du : 12/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hi Ray,
WebObjects is not going to generate SQL like that for you (maybe it is
possible, but it would be very difficult). I think that you have two
choices:
1. Use views
2. Think and fetch in terms of Objects not tables and rows and joins. This
is going to be VERY different than how you are approaching the data now.
It is possible that WebObjects is just not a good thing to use with your
schema.
Chuck
On Apr 12, 2011, at 12:49 AM, naneon.raym...@neuf.fr
naneon.raym...@neuf.fr wrote:
Hi chuck,
I go to give you more informations about data. I work in IT Universtity in
France and we have huge database with lot of table. Here is an example of
PDM (5 tables)
person
students-degree--diploma--academic
year--period_param-admin_resgistration
(fk_person)(fk_student)
(fk_academic_year) (pk an_univ prd_univ)(fields an_univ_prd_univ)
(fk_diploma, fk_person, fk_student, fk_academic_year)
and I have lof view (view_student_course on a period ...),
Here is code of view vue_parcours_profil using to have student curse :
CREATE OR REPLACE FORCE VIEW GEST_UV.VUE_PROFIL_PARCOURS (ETU_ID,
DIPLOME_$C, REORIENT_$F, PRD_DEBUT, PRD_FIN)
AS
SELECT DISTINCT t3.e,
t3.d,
t3.reorient_$f,
MIN(p_deb) over (partition BY t3.e, t3.d, t3.p_fin) debut,
t3.p_fin fin
FROM
(SELECT DISTINCT t1.e,
t1.d,
t1.no,
t1.p p_deb,
NVL(MIN(t2.p) over (partition BY t1.e, t1.d, t1.no), MAX (t1.p) over
(partition BY t1.e, t1.d)) p_fin,
CASE
WHEN t1.no = 1
AND t2.r IS NULL
THEN t1.r
ELSE DECODE(t2.r,NULL,'N',t2.r)
END reorient_$f
--DECODE(t2.r,null,'N',t2.r) reorient_$f
FROM
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia1
) t1,
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia2
) t2
WHERE t1.e = t2.e(+)
AND t1.d = t2.d(+)
AND t1.no t2.no(+)
AND t1.r t2.r(+)
--and t1.r = 'N'
AND ((t1.no= 1
AND NVL(t1.r,'N') IN ('O','N'))
OR (t1.no 1
AND NVL(t1.r,'N') = 'N'))
) t3;
So when I want all students curse in sql developer this my request :
SELECT vtp.etu_id as STUDENT_ID,
vtp.diplome_$c as DIPLOMA_CODE,
DECODE (vtp.diplome_$c,'ING',d.diplome
' ancien règlement',d.diplome) as DIPLOMA_NAME,
vtp.prd_debut as PERIOD_BEGIN,
vtp.prd_fin as PERIOD_END,
gerry.fonc_trad_prd_lib(vtp.prd_debut,'anprd') as ACADEMIC_YEAR_BEGIN,
gerry.fonc_trad_prd_lib(vtp.prd_fin,'anprd') ACADEMIC_YEAR_END,
vtp.reorient_$f as ORIENTATION,
(SELECT DIPL_SPEC_ABR
' '
DIPL_OPT_ABR
' '
NIVEAU_ADM
FROM gerry.vue_eia_abr veia
WHERE veia.etu_id = vtp.etu_id
AND veia.diplome_$c=vtp.diplome_$c
AND veia.an_univ
veia.prd_univ=vtp.prd_fin
) as DIPLOMA_SPECIFICITY,
(SELECT diplome_num
FROM gest_uv.etu_diplome
WHERE etu_id = vtp.etu_id
AND diplome_$c = vtp.diplome_$c
AND an_univ
prd_univ BETWEEN vtp.prd_debut AND vtp.prd_fin
) as DIPLOMA_NUMBER,
(SELECT SITUATION
FROM gerry.etu_inscr_adm eia,
gerry.ref_situation rs
WHERE rs.situation_$c = eia.situation_$c
AND eia.etu_id= vtp.etu_id
AND eia.diplome_$c=vtp.diplome_$c
AND eia.an_univ
eia.prd_univ=vtp.prd_fin
) as STUDENT_SITUATION,
NVL(
(SELECT DECODE(situation_$c,'DEMI','O','N')
FROM gerry.etu_inscr_adm
WHERE etu_id= vtp.etu_id
AND diplome_$c =vtp.diplome_$c
AND situation_$c='DEMI'
AND an_univ
prd_univ=vtp.prd_fin
),'N
Re: fetching on joined table
You will notice that Joe works at a rather well known university too. :-) I'd
listen to his advice or decide to not use WO.
Chuck
On Apr 13, 2011, at 8:00 AM, Joe Little wrote:
Others will say similarly, but as someone who once used views, etc, you'll
want to avoid these and stored procedures and build out all of this logic in
EOF/Modeler and let WebObjects do it's thing. If you are executing SQL for
results lists, you are likely trying too hard to outthink WO
On Apr 12, 2011, at 11:46 PM, naneon.raym...@neuf.fr wrote:
Hi Chuck,
thanks for your reply. So I thought about stored procedures? But How I
include in my code and can I cast resultset in EOEntities Objects?
I want to use data in my WOPopUpButton.
Thanks
Ray
Message du : 12/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hi Ray,
WebObjects is not going to generate SQL like that for you (maybe it is
possible, but it would be very difficult). I think that you have two
choices:
1. Use views
2. Think and fetch in terms of Objects not tables and rows and joins. This
is going to be VERY different than how you are approaching the data now.
It is possible that WebObjects is just not a good thing to use with your
schema.
Chuck
On Apr 12, 2011, at 12:49 AM, naneon.raym...@neuf.fr
naneon.raym...@neuf.fr wrote:
Hi chuck,
I go to give you more informations about data. I work in IT Universtity in
France and we have huge database with lot of table. Here is an example of
PDM (5 tables)
person
students-degree--diploma--academic
year--period_param-admin_resgistration
(fk_person)(fk_student)
(fk_academic_year) (pk an_univ prd_univ)(fields an_univ_prd_univ)
(fk_diploma, fk_person, fk_student, fk_academic_year)
and I have lof view (view_student_course on a period ...),
Here is code of view vue_parcours_profil using to have student curse :
CREATE OR REPLACE FORCE VIEW GEST_UV.VUE_PROFIL_PARCOURS (ETU_ID,
DIPLOME_$C, REORIENT_$F, PRD_DEBUT, PRD_FIN)
AS
SELECT DISTINCT t3.e,
t3.d,
t3.reorient_$f,
MIN(p_deb) over (partition BY t3.e, t3.d, t3.p_fin) debut,
t3.p_fin fin
FROM
(SELECT DISTINCT t1.e,
t1.d,
t1.no,
t1.p p_deb,
NVL(MIN(t2.p) over (partition BY t1.e, t1.d, t1.no), MAX (t1.p) over
(partition BY t1.e, t1.d)) p_fin,
CASE
WHEN t1.no = 1
AND t2.r IS NULL
THEN t1.r
ELSE DECODE(t2.r,NULL,'N',t2.r)
END reorient_$f
--DECODE(t2.r,null,'N',t2.r) reorient_$f
FROM
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia1
) t1,
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia2
) t2
WHERE t1.e = t2.e(+)
AND t1.d = t2.d(+)
AND t1.no t2.no(+)
AND t1.r t2.r(+)
--and t1.r = 'N'
AND ((t1.no= 1
AND NVL(t1.r,'N') IN ('O','N'))
OR (t1.no 1
AND NVL(t1.r,'N') = 'N'))
) t3;
So when I want all students curse in sql developer this my request :
SELECT vtp.etu_id as STUDENT_ID,
vtp.diplome_$c as DIPLOMA_CODE,
DECODE (vtp.diplome_$c,'ING',d.diplome
' ancien règlement',d.diplome) as DIPLOMA_NAME,
vtp.prd_debut as PERIOD_BEGIN,
vtp.prd_fin as PERIOD_END,
gerry.fonc_trad_prd_lib(vtp.prd_debut,'anprd') as ACADEMIC_YEAR_BEGIN,
gerry.fonc_trad_prd_lib(vtp.prd_fin,'anprd') ACADEMIC_YEAR_END,
vtp.reorient_$f as ORIENTATION,
(SELECT DIPL_SPEC_ABR
' '
DIPL_OPT_ABR
' '
NIVEAU_ADM
FROM gerry.vue_eia_abr veia
WHERE veia.etu_id = vtp.etu_id
AND veia.diplome_$c=vtp.diplome_$c
AND veia.an_univ
veia.prd_univ=vtp.prd_fin
) as DIPLOMA_SPECIFICITY,
(SELECT diplome_num
FROM gest_uv.etu_diplome
WHERE etu_id = vtp.etu_id
AND diplome_$c = vtp.diplome_$c
AND an_univ
prd_univ BETWEEN vtp.prd_debut AND vtp.prd_fin
) as DIPLOMA_NUMBER,
(SELECT SITUATION
FROM gerry.etu_inscr_adm eia,
gerry.ref_situation rs
WHERE rs.situation_$c = eia.situation_$c
AND eia.etu_id= vtp.etu_id
AND eia.diplome_$c=vtp.diplome_$c
AND eia.an_univ
eia.prd_univ
Re: fetching on joined table
IF your data is read-only and you really, really want to use a custom SQL statement, then it's ok to fetch raw rows and set the SQL using a hint: String sqlQuery = ... NSMutableDictionary hints = new NSMutableDictionary(); hints.setObjectForKey( sqlQuery, EODatabaseContext. CustomQueryExpressionHintKey); fetchSpec.setHints( hints ); fetchSpec.setRawRowKeyPaths( ... ) return editingContext.objectsWithFetchSpecification( fetchSpec ); If this is editable data then this is definitely NOT the way to do it. John On Wed, Apr 13, 2011 at 10:18 AM, Chuck Hill ch...@global-village.netwrote: You will notice that Joe works at a rather well known university too. :-) I'd listen to his advice or decide to not use WO. Chuck On Apr 13, 2011, at 8:00 AM, Joe Little wrote: Others will say similarly, but as someone who once used views, etc, you'll want to avoid these and stored procedures and build out all of this logic in EOF/Modeler and let WebObjects do it's thing. If you are executing SQL for results lists, you are likely trying too hard to outthink WO ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Re: fetching on joined table
Hi chuck,
I go to give you more informations about data. I work in IT Universtity in
France and we have huge database with lot of table. Here is an example of PDM
(5 tables)
person
students-degree--diploma--academic
year--period_param-admin_resgistration
(fk_person)(fk_student)
(fk_academic_year) (pk an_univ prd_univ)(fields an_univ_prd_univ)
(fk_diploma, fk_person, fk_student, fk_academic_year)
and I have lof view (view_student_course on a period ...),
Here is code of view vue_parcours_profil using to have student curse :
CREATE OR REPLACE FORCE VIEW GEST_UV.VUE_PROFIL_PARCOURS (ETU_ID,
DIPLOME_$C, REORIENT_$F, PRD_DEBUT, PRD_FIN)
AS
SELECT DISTINCT t3.e,
t3.d,
t3.reorient_$f,
MIN(p_deb) over (partition BY t3.e, t3.d, t3.p_fin) debut,
t3.p_fin fin
FROM
(SELECT DISTINCT t1.e,
t1.d,
t1.no,
t1.p p_deb,
NVL(MIN(t2.p) over (partition BY t1.e, t1.d, t1.no), MAX (t1.p) over
(partition BY t1.e, t1.d)) p_fin,
CASE
WHEN t1.no = 1
AND t2.r IS NULL
THEN t1.r
ELSE DECODE(t2.r,NULL,'N',t2.r)
END reorient_$f
--DECODE(t2.r,null,'N',t2.r) reorient_$f
FROM
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia1
) t1,
(SELECT DISTINCT etu_id e,
diplome_$c d,
an_univ
prd_univ p,
ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ
prd_univ NULLS LAST) no,
DECODE(res_pedag_$c,'RE','O','DR','O','N') r
FROM gerry.etu_inscr_adm eia2
) t2
WHERE t1.e = t2.e(+)
AND t1.d = t2.d(+)
AND t1.no t2.no(+)
AND t1.r t2.r(+)
--and t1.r = 'N'
AND ((t1.no= 1
AND NVL(t1.r,'N') IN ('O','N'))
OR (t1.no 1
AND NVL(t1.r,'N') = 'N'))
) t3;
So when I want all students curse in sql developer this my request :
SELECT vtp.etu_id as STUDENT_ID,
vtp.diplome_$c as DIPLOMA_CODE,
DECODE (vtp.diplome_$c,'ING',d.diplome
' ancien règlement',d.diplome) as DIPLOMA_NAME,
vtp.prd_debut as PERIOD_BEGIN,
vtp.prd_fin as PERIOD_END,
gerry.fonc_trad_prd_lib(vtp.prd_debut,'anprd') as ACADEMIC_YEAR_BEGIN,
gerry.fonc_trad_prd_lib(vtp.prd_fin,'anprd') ACADEMIC_YEAR_END,
vtp.reorient_$f as ORIENTATION,
(SELECT DIPL_SPEC_ABR
' '
DIPL_OPT_ABR
' '
NIVEAU_ADM
FROM gerry.vue_eia_abr veia
WHERE veia.etu_id = vtp.etu_id
AND veia.diplome_$c=vtp.diplome_$c
AND veia.an_univ
veia.prd_univ=vtp.prd_fin
) as DIPLOMA_SPECIFICITY,
(SELECT diplome_num
FROM gest_uv.etu_diplome
WHERE etu_id = vtp.etu_id
AND diplome_$c = vtp.diplome_$c
AND an_univ
prd_univ BETWEEN vtp.prd_debut AND vtp.prd_fin
) as DIPLOMA_NUMBER,
(SELECT SITUATION
FROM gerry.etu_inscr_adm eia,
gerry.ref_situation rs
WHERE rs.situation_$c = eia.situation_$c
AND eia.etu_id= vtp.etu_id
AND eia.diplome_$c=vtp.diplome_$c
AND eia.an_univ
eia.prd_univ=vtp.prd_fin
) as STUDENT_SITUATION,
NVL(
(SELECT DECODE(situation_$c,'DEMI','O','N')
FROM gerry.etu_inscr_adm
WHERE etu_id= vtp.etu_id
AND diplome_$c =vtp.diplome_$c
AND situation_$c='DEMI'
AND an_univ
prd_univ=vtp.prd_fin
),'N') as IS_STUDENT_RESIGNED,
NVL(
(SELECT DECODE(situation_$c,'UTX','O','N')
FROM gerry.etu_inscr_adm
WHERE etu_id= vtp.etu_id
AND diplome_$c =vtp.diplome_$c
AND situation_$c='UTX'
AND an_univ
prd_univ=vtp.prd_fin
),'N') as IS_STUDENT_TRANSFERED
FROM gest_uv.VUE_PROFIL_PARCOURS vtp,
gerry.diplome d
WHERE vtp.diplome_$c = d.diplome_$c
order by prd_debut, prd_fin;
So I must fetch on view vue_profil_parcours, diploma to display all students
curse in a WOO table using batch. Here a little example and I have lof of
fecthing such as this to do in my apps to display information about, sutdent,
teacher so on...
I want a example code How to translate this sql request or what the best
practice in WebObjects in this situation? To fast forward in my apps, I create
view every time when I want to display this kind of information. This allows me
to browse my data in an accurate in WebObjects.
Thanks for your help
Ray,
Message du : 11/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
On Apr 11, 2011, at 3:12 AM, naneon.raym...@neuf.fr wrote:
Hi Chuck,
Tables A, B, C, D haven't relationship but same fileds name.
If they don't have
Re: fetching on joined table
this sql request or what the best
practice in WebObjects in this situation? To fast forward in my apps, I
create view every time when I want to display this kind of information. This
allows me to browse my data in an accurate in WebObjects.
Thanks for your help
Ray,
Message du : 11/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
On Apr 11, 2011, at 3:12 AM, naneon.raym...@neuf.fr wrote:
Hi Chuck,
Tables A, B, C, D haven't relationship but same fileds name.
If they don't have modeled relationships, then you can't use qualifiers.
When i'am using raw rows to translate my sql in webobjects and get my data,
I don't have EOF entities but dictionnaries.
So I would like to know what the best practice on my case? create view with
my sql request on database and fetching on view in my model?
I think the best practice would be to change the model (and the schema too,
if needed) so that there are relationships and you are fetching entities. I
very rarely need to do a raw row fetch. If you give us more information on
the data, maybe we can suggest a better model.
Chuck
Message du : 11/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hello Ray,
On Apr 8, 2011, at 5:16 PM, naneon.raym...@neuf.fr wrote:
Hi,
I'm newbie on WebObjects and I have big problem to fetching on joined table.
Example :
I have my SQL like that :
A good rule with WebObjects is If you are thinking about SQL, you are doing
it wrong. There are times (i.e. when optimizing) when you do need to think
about the SQL, but thinking in terms of objects is usually the better way.
select
a.id b.id as ID,
c.option as option,
decode(d.no, 1, 'one', 2, 'two', 'unknow') as order
WebObjects (EOF) does not do mixed table selects like this (unless you are
selecting Raw Rows). It selects whole rows of tables and makes them into
related Java objects.
from
ta a, tb b, tc c, td d
where
c.option in ('1', '2','3')
and c.option = b.option
How to translate this SQL request in EOF using qualifier or spec qualifier?
Thanks for your help.
What are the relationships in the model between A, B, C, and D? What are
you trying to fetch?
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
smime.p7s
Description: S/MIME cryptographic signature
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Re: fetching on joined table
Hi Chuck,
Tables A, B, C, D haven't relationship but same fileds name. When i'am using
raw rows to translate my sql in webobjects and get my data, I don't have EOF
entities but dictionnaries.
So I would like to know what the best practice on my case? create view with my
sql request on database and fetching on view in my model?
Thanks for your help.
Ray
Message du : 11/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hello Ray,
On Apr 8, 2011, at 5:16 PM, naneon.raym...@neuf.fr wrote:
Hi,
I'm newbie on WebObjects and I have big problem to fetching on joined table.
Example :
I have my SQL like that :
A good rule with WebObjects is If you are thinking about SQL, you are doing it
wrong. There are times (i.e. when optimizing) when you do need to think about
the SQL, but thinking in terms of objects is usually the better way.
select
a.id b.id as ID,
c.option as option,
decode(d.no, 1, 'one', 2, 'two', 'unknow') as order
WebObjects (EOF) does not do mixed table selects like this (unless you are
selecting Raw Rows). It selects whole rows of tables and makes them into
related Java objects.
from
ta a, tb b, tc c, td d
where
c.option in ('1', '2','3')
and c.option = b.option
How to translate this SQL request in EOF using qualifier or spec qualifier?
Thanks for your help.
What are the relationships in the model between A, B, C, and D? What are you
trying to fetch?
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Tr: Re: Re: fetching on joined table
Hi Chuck,
Tables A, B, C, D haven't relationship but same fileds name. When i'am using
raw rows to translate my sql in webobjects and get my data, I don't have EOF
entities but dictionnaries.
So I would like to know what the best practice on my case? create view with my
sql request on database and fetching on view in my model?
Thanks for your help.
Ray
Message du : 11/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hello Ray,
On Apr 8, 2011, at 5:16 PM, naneon.raym...@neuf.fr wrote:
Hi,
I'm newbie on WebObjects and I have big problem to fetching on joined table.
Example :
I have my SQL like that :
A good rule with WebObjects is If you are thinking about SQL, you are doing it
wrong. There are times (i.e. when optimizing) when you do need to think about
the SQL, but thinking in terms of objects is usually the better way.
select
a.id b.id as ID,
c.option as option,
decode(d.no, 1, 'one', 2, 'two', 'unknow') as order
WebObjects (EOF) does not do mixed table selects like this (unless you are
selecting Raw Rows). It selects whole rows of tables and makes them into
related Java objects.
from
ta a, tb b, tc c, td d
where
c.option in ('1', '2','3')
and c.option = b.option
How to translate this SQL request in EOF using qualifier or spec qualifier?
Thanks for your help.
What are the relationships in the model between A, B, C, and D? What are you
trying to fetch?
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: fetching on joined table
On Apr 11, 2011, at 3:12 AM, naneon.raym...@neuf.fr wrote:
Hi Chuck,
Tables A, B, C, D haven't relationship but same fileds name.
If they don't have modeled relationships, then you can't use qualifiers.
When i'am using raw rows to translate my sql in webobjects and get my data, I
don't have EOF entities but dictionnaries.
So I would like to know what the best practice on my case? create view with
my sql request on database and fetching on view in my model?
I think the best practice would be to change the model (and the schema too, if
needed) so that there are relationships and you are fetching entities. I very
rarely need to do a raw row fetch. If you give us more information on the
data, maybe we can suggest a better model.
Chuck
Message du : 11/04/2011
De : Chuck Hill ch...@global-village.net
A : naneon.raym...@neuf.fr
Copie à : webobjects-dev@lists.apple.com
Sujet : Re: fetching on joined table
Hello Ray,
On Apr 8, 2011, at 5:16 PM, naneon.raym...@neuf.fr wrote:
Hi,
I'm newbie on WebObjects and I have big problem to fetching on joined table.
Example :
I have my SQL like that :
A good rule with WebObjects is If you are thinking about SQL, you are doing
it wrong. There are times (i.e. when optimizing) when you do need to think
about the SQL, but thinking in terms of objects is usually the better way.
select
a.id b.id as ID,
c.option as option,
decode(d.no, 1, 'one', 2, 'two', 'unknow') as order
WebObjects (EOF) does not do mixed table selects like this (unless you are
selecting Raw Rows). It selects whole rows of tables and makes them into
related Java objects.
from
ta a, tb b, tc c, td d
where
c.option in ('1', '2','3')
and c.option = b.option
How to translate this SQL request in EOF using qualifier or spec qualifier?
Thanks for your help.
What are the relationships in the model between A, B, C, and D? What are you
trying to fetch?
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
smime.p7s
Description: S/MIME cryptographic signature
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: fetching on joined table
Hello Ray,
On Apr 8, 2011, at 5:16 PM, naneon.raym...@neuf.fr wrote:
Hi,
I'm newbie on WebObjects and I have big problem to fetching on joined table.
Example :
I have my SQL like that :
A good rule with WebObjects is If you are thinking about SQL, you are doing it
wrong. There are times (i.e. when optimizing) when you do need to think about
the SQL, but thinking in terms of objects is usually the better way.
select
a.id b.id as ID,
c.option as option,
decode(d.no, 1, 'one', 2, 'two', 'unknow') as order
WebObjects (EOF) does not do mixed table selects like this (unless you are
selecting Raw Rows). It selects whole rows of tables and makes them into
related Java objects.
from
ta a, tb b, tc c, td d
where
c.option in ('1', '2','3')
and c.option = b.option
How to translate this SQL request in EOF using qualifier or spec qualifier?
Thanks for your help.
What are the relationships in the model between A, B, C, and D? What are you
trying to fetch?
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
smime.p7s
Description: S/MIME cryptographic signature
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
fetching on joined table
Hi,
I'm newbie on WebObjects and I have big problem to fetching on joined table.
Example :
I have my SQL like that :
select
a.id b.id as ID,
c.option as option,
decode(d.no, 1, 'one', 2, 'two', 'unknow') as order
from
ta a, tb b, tc c, td d
where
c.option in ('1', '2','3')
and c.option = b.option
How to translate this SQL request in EOF using qualifier or spec qualifier?
Thanks for your help.
Ray
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
