Re: Can somebody explain this strange sql statement
Date: Mon, 18 Jul 2011 23:31:42 +0200
Message-ID: <j028ni$sjg$1_at_dont-email.me>
"Charles Hooper" <hooperc2001_at_gmail.com> skrev i meddelandet
news:8cc8133c-b171-444c-b0f3-f50e8d8153df_at_n28g2000vbs.googlegroups.com...
On Jul 18, 11:35 am, "Tony Johansson" <johansson.anders..._at_telia.com>
wrote:
> "Walt" <walt_ask..._at_yahoo.com> skrev i
> meddelandetnews:RhXUp.108347$GN5.67916_at_en-nntp-14.dc1.easynews.com...
> > On 7/18/2011 4:56 AM, Tony Johansson wrote:
> >> Hello!
>
> >> It's a webbsite using C# and ado.net to access a oracle database.
> >> Below is just the beginning of the sql select statement that is used
> >> and
> >> here you
> >> can see form_slag and I wonder
> >> how this is possible. This form_slag looks like a variable.
> >> If I look at this sql select statement just before dataadapter.fill is
> >> executed I see this form_slag just as it is in the select statement
> >> below.
> >> What seems very strange to me is where is the actual value for this
> >> form_slag is given.
> >> This form_slag must have a value before the dataadapter.fill is
> >> executed
> >> but
> >> it has not or it must be fetched from the database in some way that I
> >> don't
> >> understand.
>
> >> Note as I wrote earlier this is just the beginning of a large sql
> >> statement.
>
> >> I have two questions.
> >> 1.Can anybody explain how this form_slag can be used in the way that it
> >> is
> >> used ?
>
> >> 2. What does it mean when we have this in the select statement '2'; as
> >> send_ro_roles ?
>
> >> select distinct form_nr,
> >> '2;' as send_ro_roles,
> >> '2;' || k.kli_klia as roles_and persons,
> >> k.klia_namn as klient_ansvarig,
> >> (
> >> select distinct bizref_check_freq
> >> from bizrulereference
> >> where bizref_date =
> >> (
> >> select MAX(bizref_date)
> >> from bizrulereference
> >> where BIZREF_FORM_TYPE = form_slag
>
> > 1) See Correlated subquery
>
> > 2) See column alias
>
> > //Walt
>
> Here is the complete sql statement. I have great problem to understand
> this
> sql statement because it doesn't exist any
> kind of comment or documentation.
> My opinion is that writing such sql statement without documenting is
> absolutely forbidden.
> I have a lot of questions but we can start with this one if
> anybody can explain how this form_slag can be used in the way that it is
> used ?
> Se more info in my earlier mail.
(snip)
I think that at this point the best course of direction is to throw
away the SQL statement and start from scratch if either of the
following is true for the SQL statement:
* Performs poorly
* Does not produce the expected results
The reasons for the above comment:
* Mixing ANSI style joins with Oracle specific joins; ANSI joins can
be problematic in different Oracle Database releases, and they will be
converted to Oracle style joins prior to execution which could cause
additional problems
* Likely excessive use of PL/SQL calls
* Scalar query with correlated subquery (form_slag should be a column
in the bizrulereference table, bit because it is not aliased, it is
not clear exactly where it comes from)
* Inline views in inline view in inline views with subqueries makes it
a little difficult to see what is happening
The "'2;'" found in the query is simply a case where the person writing the SQL statement wanted to select the constant value 2; from the database.
If want want any chance at unwinding the query to understand what it
does, you probably need to adjust the whitespace in the SQL statement
a bit, something like this (line wrapping will be a problem with what
I post below, so you need to fix that):
select distinct
form_nr,
'2;' as send_to_roles,
'2;' || k.kli_klia as roles_and_persons,
k.klia_namn as klient_ansvarig,
(select distinct
bizref_check_freq
from
bizrulereference
where
bizref_date =
(select
MAX(bizref_date)
from
bizrulereference
where
BIZREF_FORM_TYPE = form_slag
and (bizref_affars_omrade =
GETBUSINESSAREABYSERVICECODE(form_service)
or bizref_affars_omrade = -2)
and bizref_current_risk_grade =
GET_NEW_RISKVALUE(form_riskklass)
and bizref_former_risk_grade =
GET_NEW_RISKVALUE(FORM_FORMER_RISKGRADE)
and bizref_date <= form_registererad_datum
)
and bizref_form_type = form_slag
and (bizref_affars_omrade =
GETBUSINESSAREABYSERVICECODE(form:service)
or bizref_affars_omrade = -2)
and bizref_current_risk_grade =
GET_NEW_RISKVALUE(form_riskklass)
and bizref_former_risk_grade =
GET_NEW_RISKVALUE(FORM_FORMER_RISKGRADE) ) + form_godkand_datum giltig_tom,
FORM_SLAG_JOIN.KEY_NAME form_slag_text,
e2.form_klientnr, e2.form_klient_namn, e2.form_godkand_datum
from
(select
f.*
from
(select
e.*
from
vy_eacform_sweac20 e
where
((e.form_case_status = 4
and e.form_doc_status <> 0)
or e.form_status = 3)
and e.form_slag in ('1','2')
and form_klientnr = 93801
and e.form_godkand_datum =
(select
max(form_godkand_datum)
from
vy_eacform_sweac20
where
e.form_klientnr = form_klientnr
and ((form_case_status = 4
and form_doc_status <> 0)
or form_status = 3)
and form_slag in ('1','2')
)) f
where
(select
bizref_check_freq
from
bizrulereference
where
bizref_date =
(select
max(bizref_date)
from
bizrulereference
where
bizref_form_type = f.form_slag
and (bizref_affars_omrade =
GETBUSINESSAREABYSERVICECODE(f.form_service)
or bizref_affars_omrade = -2)
and bizref_current_risk_grade =
GET_NEW_RISKVALUE(f.form_riskklass)
and bizref_former_risk_grade =
GET_NEW_RISKVALUE(f.FORM_FORMER_RISKGRADE)
and bizref_date <= f.form_registererad_datum
)
and bizref_form_type = f.form_slag
and (bizref_affars_omrade =
GETBUSINESSAREABYSERVICECODE(f.form_service)
or bizref_affars_omrade = -2)
and bizref_current_risk_grade =
GET_NEW_RISKVALUE(f.form_riskklass)
and bizref_former_risk_grade =
GET_NEW_RISKVALUE(f.FORM_FORMER_RISKGRADE)
) + f.form_godkand_datum - 30 < current_date) e2
JOIN
vy_sweac_klient k ON k.klientnr = e2.form_klientnr and k.kli_sts <> 4
join
(select
ve2.form_klientnr,
max(ve2.form_registererad_datum) max_form_registererad_datum
from
(select
*
from
vy_eacform_sweac20 ve
where
((ve.form_case_status = 4 and ve.form_doc_status <> 0)
or ve.form_status = 3)
and ve.form_slag in ('1','2') ve2
JOIN
vy_sweac_klient kl ON kl.klientnr = ve2.form_klientnr
and kl.kli_sts <> 4
GROUP BY
ve2.form_klientnr) maxReg
on
maxReg.form_klientnr = e2.form_klientnr and maxReg.max_form_registererad_datum = e2.form_registererad_datum LEFT OUTER JOIN
winture.VY_ANST_NAMN klient_ansvarig ON klient_ansvarig.ANSTNR = e2.form_klient_ansvarig left join
SWEAC.EACLOOKUP FORM_SLAG_JOIN on
FORM_SLAG_JOIN.KEY_CATEGORY = 'FormSlag' and FORM_SLAG_JOIN.KEY_VALUE = e2.FORM_SLAG order by
form_nr ASC
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Hello
This sql statement has given the correct result earlier but according to the
customer will the result from this sql statement
give wrong result. They say that nothing has been changed the .NET code is
the same and the sql statement is the same
but they use a new Oracle version. When a match is supposed to occur for the
sql select statement we doesn't get any match ?
So do you think it might be possible that two different Oracle versions could give different result for the same sql select statement ?
//Tony
//Tony
Now to my question do you think that two different Oracle versions could give two different results Received on Mon Jul 18 2011 - 16:31:42 CDT