Re: Can somebody explain this strange sql statement

From: Tony Johansson <johansson.andersson_at_telia.com>
Date: Mon, 18 Jul 2011 17:35:33 +0200
Message-ID: <j01jrs$181$1_at_dont-email.me>

"Walt" <walt_askier_at_yahoo.com> skrev i meddelandet news: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.

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 Received on Mon Jul 18 2011 - 10:35:33 CDT