Can somebody explain this strange sql statement

  • Thread starter Thread starter Tony Johansson
  • Start date Start date
T

Tony Johansson

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
 
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,
Click to expand...

I can answer this second part (although you typed it wrong in the first
version and put the semicolon outside the quotes): This is selecting a
constant value of "2;" and putting it in a new column called
"send_ro_roles". This column only exists in the result set from the SELECT
statement.
 
Jeff Johnson said:
I can answer this second part (although you typed it wrong in the first
version and put the semicolon outside the quotes): This is selecting a
constant value of "2;" and putting it in a new column called
"send_ro_roles". This column only exists in the result set from the SELECT
statement.
Click to expand...

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
 
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.
Click to expand...

<SQL snipped>

The only answer one can possibly give to this is : "Ask a colleague".

If this is not possible for whatever reason, there's only one way to go:
Format, dissect, and analyse the query yourself. Extracting the
subqueries and analysing their results might be beneficial, and give you
more insight into:

a) The database structure
b) (The possibilities of) SQL

Although a complex query, a brief glance didn't reveal any strange or
unusual or hackish use of SQL.
 
Willem van Rumpt said:
<SQL snipped>

The only answer one can possibly give to this is : "Ask a colleague".

If this is not possible for whatever reason, there's only one way to go:
Format, dissect, and analyse the query yourself. Extracting the subqueries
and analysing their results might be beneficial, and give you more insight
into:

a) The database structure
b) (The possibilities of) SQL

Although a complex query, a brief glance didn't reveal any strange or
unusual or hackish use of SQL.
Click to expand...

Have you any idea about how form_slag can be used in the way that it is
used ? It is used like a variable.

//Tony
 
Have you any idea about how form_slag can be used in the way that it is
used ? It is used like a variable.

//Tony
Click to expand...

Most likely it's a column in one the tables that are involved in the
query, nothing special about.

The point is:
No one here will be able to tell you more about it. As Peter suggested,
a forum specializing in SQL would be more appropriate, but given the
vagenues of the question, I doubt you'll get an answer there as well.

Dissect the query in chunks that you can deal with, and find out where
form_slag comes from, that's all the advice I can give you (apart from
the most appropriate one: "Ask a colleague or co-worker").
 
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
Click to expand...

Your question is off topic, however, here is a shot-in-the-dark at the
first question:

A reasonable, but perhaps incorrect guess for 'sweac' could be a
Swedish branch of the Danish company Østasiatisk Kompagni (ØK), which
does, among other things, international trade. But commodities don't
have risk grades, so the app is likely to be an aid in deciding which
businesses are worthy of investment or venture capital. Its parent
form is likely to have something like a listbox that shows the
available business categories. Once the business category is chosen,
it appears, perhaps oddly, that separate forms handle each separate
business category, possibly to tailor the appropriate information to
show for that category. The KEY_CATEGORY value appears to store the
category number of the business category form. A KEY_CATEGORY of '1'
or '2' should limit the form_slag (Form Category) values to '1' or '2'
respectively. The business category form probably allows the user to
select various levels of acceptable risk (via combobox or listbox), to
select the group of business service area(s) (maybe by using a
multiselect listbox) under consideration, and to select the Client.
One of the service areas of the business must match the service
area(s) chosen on the form or be in a particular business sector in
order to be returned. A boolean return field is added to the results
to show if the approval date is before 30 days from today. So when
the query is run, vy_eacform_sweac20 is checked for criteria based on
the most recent entry satisfying those criteria. Next, the business
rules are applied to filter the results further. It looks like a
Status of 4 is a no-no :-). The latest 'registrated' date further
filters the data. The left joins after that are not used to filter
results. They are there to allow null/empty field values (rather than
exclude the record) when a search for a match in SWEAC or winture to
get those values turns up empty. As I said, this is a shot in the
dark, but perhaps it will help get you started. Reminder: Your
question is off topic. I might ignore any followup questions.

James A. Fortune
(e-mail address removed)

When evaluating a start-up company for prospective investment, I do
extensive surveys to determine their likely profit, including a
complete statistical analysis of the survey results. -- J. Knapp
 
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 ?
Click to expand...

Don't know.
2. What does it mean when we have this in the select statement '2'; as
send_ro_roles ?
Click to expand...

You can select constant expressions in SQL.

Example:

1> SELECT * FROM t1
2> GO
f1 f2
----------- --------------------------------------------------
1 A
2 BB
3 CCC
4 DDDD
5 EEEEE

(5 rows affected)
1> SELECT f1,f2,'hello world' AS f2 FROM t1
2> GO
f1 f2 f2
----------- -------------------------------------------------- -----------
1 A hello world
2 BB hello world
3 CCC hello world
4 DDDD hello world
5 EEEEE hello world

(5 rows affected)

select distinct form_nr,
'2;' as send_ro_roles,
'2;' || k.kli_klia as roles_and persons,
Click to expand...

If I remember correctly then || is Oracle string concatanation.
k.klia_namn as klient_ansvarig,
(
select distinct bizref_check_freq
Click to expand...

This is a query as a value.
from bizrulereference
where bizref_date =
(
select MAX(bizref_date)
from bizrulereference
where BIZREF_FORM_TYPE = form_slag
Click to expand...

Arne
 
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
Click to expand...

Code without documentation is not good. Not in C# and not in SQL.

It is a pretty big query and it also used a few user defined functions.

Send the author an email with a ref to the famous quote:

"Always write code as though it will be maintained by a homicidal,
axe-wielding maniac who knows where you live."

Arne

PS: Base on this it looks as if form_slag is field in a table.
 
Back
Top