You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Byrne, Ailish M" <ab...@indiana.edu> on 2005/02/01 05:31:45 UTC
setAlias, less queries, and logging with parameters plugged in
We are very new to OJB and have some pretty complicated SQL (due to
PeopleSoft effective dating and setid logic) that we are trying to
accommodate.
Here is the example that we're working through to determine if OJB will
work for us, as we would write it...
SELECT
a.position_nbr,
a.jobcode,
b.descr
FROM
sysadm.ps_position_data a,
sysadm.ps_jobcode_tbl b
WHERE
a.position_nbr = '00000001' AND
a.effdt = (SELECT MAX(c.effdt) FROM ps_position_data c WHERE
a.position_nbr = c.position_nbr AND
c.effdt <= '10-NOV-2004') AND
a.eff_status = 'A' AND
b.jobcode = a.jobcode AND
b.setid = (SELECT g.setid FROM sysadm.ps_set_cntrl_group g,
sysadm.ps_rec_group_rec r WHERE r.recname = 'JOBCODE_TBL' AND
g.rec_group_id = r.rec_group_id AND
g.setcntrlvalue = a.business_unit) AND
b.effdt = (SELECT MAX(d.effdt) FROM sysadm.ps_jobcode_tbl d WHERE
b.setid = d.setid AND
b.jobcode = d.jobcode AND
d.effdt <= a.effdt)
Here's what OJB generates...
SELECT
A0.POSITION_NBR,
A0.DESCR,
A0.BUSINESS_UNIT,
A0.DEPTID,
A0.EFFDT,
A0.EFF_STATUS,
A0.JOBCODE
FROM
SYSADM.PS_POSITION_DATA A0
WHERE
((POSITION_NBR = ?) AND
EFFDT = (SELECT MAX(B0.EFFDT) FROM SYSADM.PS_POSITION_DATA B0 WHERE
(B0.POSITION_NBR = A0.POSITION_NBR) AND
B0.EFFDT <= ?) ) AND
EFF_STATUS = ?
SELECT
A0.REC_GROUP_ID,
A0.SETID,
A0.SETCNTRLVALUE
FROM
SYSADM.PS_SET_CNTRL_GROUP A0
WHERE
(SETCNTRLVALUE = ?) AND
A0.REC_GROUP_ID = (SELECT B0.REC_GROUP_ID FROM
SYSADM.PS_REC_GROUP_REC B0 WHERE (B0.REC_GROUP_ID = A0.REC_GROUP_ID) AND
RECNAME = ?)
SELECT
A0.REC_GROUP_ID,
A0.RECNAME
FROM
SYSADM.PS_REC_GROUP_REC A0
WHERE
A0.REC_GROUP_ID = ?
SELECT A0.GRADE,A0.DESCR,A0.SETID,A0.EFFDT,A0.EFF_STATUS,A0.JOBCODE FROM
SYSADM.PS_JOBCODE_TBL A0 WHERE (((JOBCODE = ?) AND SETID = ?) AND EFFDT
= (
SELECT
MAX(B0.EFFDT)
FROM
SYSADM.PS_JOBCODE_TBL B0
WHERE
(B0.JOBCODE = A0.JOBCODE) AND
B0.EFFDT <= A0.EFFDT) ) AND
EFF_STATUS = ?
We've had a lot of success using a combination of custom queries and
criteria in 1.0.1, where we did not have success with prior versions of
OJB. However, one thing we're a bit uncomfortable with is that the SQL
generated is less clear in terms of aliasing, than we are used to. So,
we tried using the setAlias method, but did not receive the expected
result. We found that we were indeed receiving the results that we
should have expected...
http://db.apache.org/ojb/docu/guides/query.html#user+defined+alias
<http://db.apache.org/ojb/docu/guides/query.html#user+defined+alias>
"The generated query will be as follows. Note that the alias name
'company' does not show up in the SQL."
I'm not clear on why OJB doesn't use the alias specified in the SQL.
Can anyone clarify / provide other suggestions? We're also interested
in any advice on how to get this to happen in less queries without hard
coding the sql and getting OJB to log the SQL with the parameters
plugged in, if anyone has advice on those topics.
Thanks,
Ailish
Re: setAlias, less queries, and logging with parameters plugged in
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi ailish,
what do you mean by 'aliasing' ?
you cannot control the alias (A0, A1, etc.), this is done internally.
the setAlias method is only use to force OJB to use another join for
the same relationship.
jakob
Byrne, Ailish M schrieb:
> We are very new to OJB and have some pretty complicated SQL (due to
> PeopleSoft effective dating and setid logic) that we are trying to
> accommodate.
>
>
>
> Here is the example that we're working through to determine if OJB will
> work for us, as we would write it...
>
>
>
> SELECT
>
> a.position_nbr,
>
> a.jobcode,
>
> b.descr
>
> FROM
>
> sysadm.ps_position_data a,
>
> sysadm.ps_jobcode_tbl b
>
> WHERE
>
> a.position_nbr = '00000001' AND
>
> a.effdt = (SELECT MAX(c.effdt) FROM ps_position_data c WHERE
> a.position_nbr = c.position_nbr AND
>
> c.effdt <= '10-NOV-2004') AND
>
> a.eff_status = 'A' AND
>
> b.jobcode = a.jobcode AND
>
> b.setid = (SELECT g.setid FROM sysadm.ps_set_cntrl_group g,
> sysadm.ps_rec_group_rec r WHERE r.recname = 'JOBCODE_TBL' AND
>
> g.rec_group_id = r.rec_group_id AND
>
> g.setcntrlvalue = a.business_unit) AND
>
> b.effdt = (SELECT MAX(d.effdt) FROM sysadm.ps_jobcode_tbl d WHERE
> b.setid = d.setid AND
>
> b.jobcode = d.jobcode AND
>
> d.effdt <= a.effdt)
>
>
>
> Here's what OJB generates...
>
>
>
> SELECT
>
> A0.POSITION_NBR,
>
> A0.DESCR,
>
> A0.BUSINESS_UNIT,
>
> A0.DEPTID,
>
> A0.EFFDT,
>
> A0.EFF_STATUS,
>
> A0.JOBCODE
>
> FROM
>
> SYSADM.PS_POSITION_DATA A0
>
> WHERE
>
> ((POSITION_NBR = ?) AND
>
> EFFDT = (SELECT MAX(B0.EFFDT) FROM SYSADM.PS_POSITION_DATA B0 WHERE
> (B0.POSITION_NBR = A0.POSITION_NBR) AND
>
> B0.EFFDT <= ?) ) AND
>
> EFF_STATUS = ?
>
>
>
> SELECT
>
> A0.REC_GROUP_ID,
>
> A0.SETID,
>
> A0.SETCNTRLVALUE
>
> FROM
>
> SYSADM.PS_SET_CNTRL_GROUP A0
>
> WHERE
>
> (SETCNTRLVALUE = ?) AND
>
> A0.REC_GROUP_ID = (SELECT B0.REC_GROUP_ID FROM
> SYSADM.PS_REC_GROUP_REC B0 WHERE (B0.REC_GROUP_ID = A0.REC_GROUP_ID) AND
>
> RECNAME = ?)
>
>
>
> SELECT
>
> A0.REC_GROUP_ID,
>
> A0.RECNAME
>
> FROM
>
> SYSADM.PS_REC_GROUP_REC A0
>
> WHERE
>
> A0.REC_GROUP_ID = ?
>
>
>
> SELECT A0.GRADE,A0.DESCR,A0.SETID,A0.EFFDT,A0.EFF_STATUS,A0.JOBCODE FROM
> SYSADM.PS_JOBCODE_TBL A0 WHERE (((JOBCODE = ?) AND SETID = ?) AND EFFDT
> = (
>
> SELECT
>
> MAX(B0.EFFDT)
>
> FROM
>
> SYSADM.PS_JOBCODE_TBL B0
>
> WHERE
>
> (B0.JOBCODE = A0.JOBCODE) AND
>
> B0.EFFDT <= A0.EFFDT) ) AND
>
> EFF_STATUS = ?
>
>
>
> We've had a lot of success using a combination of custom queries and
> criteria in 1.0.1, where we did not have success with prior versions of
> OJB. However, one thing we're a bit uncomfortable with is that the SQL
> generated is less clear in terms of aliasing, than we are used to. So,
> we tried using the setAlias method, but did not receive the expected
> result. We found that we were indeed receiving the results that we
> should have expected...
>
>
>
> http://db.apache.org/ojb/docu/guides/query.html#user+defined+alias
> <http://db.apache.org/ojb/docu/guides/query.html#user+defined+alias>
>
> "The generated query will be as follows. Note that the alias name
> 'company' does not show up in the SQL."
>
>
>
> I'm not clear on why OJB doesn't use the alias specified in the SQL.
> Can anyone clarify / provide other suggestions? We're also interested
> in any advice on how to get this to happen in less queries without hard
> coding the sql and getting OJB to log the SQL with the parameters
> plugged in, if anyone has advice on those topics.
>
>
>
> Thanks,
>
> Ailish
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org