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

//Tony

On 7/18/2011 4:56 AM, Tony Johansson wrote:
1) See Correlated subquery

2) See column alias

//Walt

"Walt" <walt_askier (AT) yahoo (DOT) com> skrev i meddelandet
news:RhXUp.108347$GN5.67916 (AT) en-nntp-14 (DOT) dc1.easynews.com...
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.


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

On Jul 18, 11:35*am, "Tony Johansson" <johansson.anders... (AT) telia (DOT) com>
wrote:

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.

"Charles Hooper" <hooperc2001 (AT) gmail (DOT) com> skrev i meddelandet
news:8cc8133c-b171-444c-b0f3-f50e8d8153df (AT) n28g2000vbs (DOT) googlegroups.com...
On Jul 18, 11:35 am, "Tony Johansson" <johansson.anders... (AT) telia (DOT) com>
wrote:

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

On Jul 18, 5:31*pm, "Tony Johansson" <johansson.anders... (AT) telia (DOT) com>
wrote:
Simply changing the Oracle release version *should not* have an
impact. That said, there are release version specific bugs for ANSI
style joins - the query might have worked by coincidence in the past,
and when an ANSI bug was fixed, the join order changed. See the
following two articles, which list several of the ANSI specific bugs
found in Metalink (MOS) and the release versions affected:
http://hoopercharles.wordpress.com/2...e-join-syntax/
http://hoopercharles.wordpress.com/2...-ready-or-not/

The best bet at this point is probably to try to completely rewrite
the SQL statement once it is understood what the SQL statement is
supposed to accomplish. I suggest avoiding the ANSI sytle joins if
possible and also eliminating the scalar subquery.

Another thought is that some form of new bug in the PL/SQL functions
has surfaced. For example, maybe one of those PL/SQL functions
referenced in the SQL statement was performing a GROUP BY operation
and relying on an implicit ORDER BY, and that implicit ORDER BY no
longer happens in the new Oracle Database release version.

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.

On Jul 18, 6:42*pm, Charles Hooper <hooperc2... (AT) gmail (DOT) com> wrote:
Looks like this guy posted this in quite a few NG and forums... Could
be wrong, but to me it looks like he is trying to reverse-engineer
something (some web-based app??) without a clue as to what it needs to
do...

On Jul 18, 8:37*pm, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:
I performed a web search and found that the OP's SQL statement
appeared on a number of websites. That said, it appears that those
websites are simply copying Usenet threads onto their pages in the
hope of obtaining advertisement revenue. The OP's post appears in the
following Usenet threads:
http://groups.google.com/group/comp....bc83657dcc5cb1
http://groups.google.com/group/micro...365a3bb375d171
http://groups.google.com/group/micro...91b722acc10931

It appears that the OP tried unsuccessfully to obtain help previously
for this problem, and was refered back to the
comp.databases.oracle.server group.

Quoting from the first of those threads, with a date of July 4, 2011:
"This sql statement is just a piece of the whole statement.
If I try to write out the form_slag just before statement
dataadapter.fill(datatable) the compiler will complain about not
knowing
about the form_slag.
This form_slag is either defined before the sql statement is executed
or
defined in the database in some way.
I far as I understand it seems to me that this form_slag is not known
before
this dataadapter.fill(datatable)
so the definition must be within the database in some way.

Is it possible that the definition of this form_slag is in the
database in
some way.
The .NET seems to not knowing anything about this form_slag"
--

If the OP is trying to execute the posted query in C# using ADO.Net,
it is possible that ADO.Net is attempting to re-write the SQL
statement into a SQL Server-like format and is confused by the ANSI
and non-ANSI joins combined with the scalar sub-query and the inline
views. The OP might be able tell ADO.Net to handle the query as a
passthrough type query to avoid an automatic rewrite (not tested).
The following might help - of course there are probably readers on
this group that know the C# syntax off the top of their heads:
http://msdn.microsoft.com/en-us/libr...(v=vs.71).aspx
http://msdn.microsoft.com/en-us/library/zy8cz1kk.aspx
http://download.oracle.com/docs/cd/B...apterClass.htm

There of course is a significant difference between not being able to
execute a SQL statement and retrieving two different answers for a SQL
statement based on the Oracle Database release version.

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.