Multiple Parameters in SQL Command

[mpegjunkie]

Hi,

I'm using CR 9 with an ODBC Connection to ORACLE 8.1.7 which works fine.
I added an SQL Command via "Add Command" that looks like follows:

(The lines with leading !!!! are most interesting)

select
va.va_nr VA_Nr,
va.txt_k VA_Kuerzel,
va.txt_l VA_Thema,
vat.datum_von VA_Beginn_Gesamt,
vat.datum_bis VA_Ende_Gesamt,
vt.txt_k VA_Typ_Kuerzel,
vt.txt_l VA_Typ_Thema,
s.txt_k VA_Status,
s.farbe VA_Statusfarbe,
nvl( va.ums_ist, 0 ) VA_Ums_Ist,
nvl( va.ums_plan, 0 ) VA_Ums_Plan,
va.wahr_level VA_Wahrscheinlichkeit,
ast.txt_wert VA_Waehrung,
nvl( va.pau_preis, 0 ) VA_Pau_Preis,
nvl( va.pausch_kz, 0 ) VA_Pausch_Kz,
o.txt_k Ort_Kuerzel,
knd.knd_name Knd_Name,
count(ppkt.ppkt_nr) PPKT_Anzahl
from
fo_ta_va va,
fo_tc_va vt,
fo_ta_va_termin vat,
fo_ty_va_status s,
fo_tc_astamm ast,
fo_tc_ort o,
fo_ta_knd knd,
fo_ta_knd_adr kndva,
fo_ta_ppkt ppkt
where
va.co_va = vt.co_va and
va.va_nr = vat.va_nr and
va.co_va_status = s.co_va_status and
(va.co_waehrung + 10100 = ast.co_astamm and
va.co_ort = ast.co_ort) and
va.co_ort = o.co_ort and
kndva.va_nr = va.va_nr and
kndva.knd_nr = knd.knd_nr and
ppkt.va_nr = va.va_nr and
vat.prio = 0 and
kndva.co_adr_kz = 41 and
(s.txt_k = 'Fest' or
s.txt_k = 'Rechnung' or
s.txt_k = 'Rechnung unerledigt' or
s.txt_k = 'Teilrechnung' or
s.txt_k = 'Storno Rechnung' or
s.txt_k = 'Storno Rechnung unerledigt' or
s.txt_k = 'Zahlung') and
!!!! to_char( vat.datum_von, 'YYYY' ) = {?P1_VA_Jahr} and
!!!! o.txt_k = {?P2_Ort}
group by
va.va_nr,
va.txt_k,
va.txt_l,
vat.datum_von,
vat.datum_bis,
vt.txt_k,
vt.txt_l,
s.txt_k,
s.farbe,
va.ums_ist,
va.ums_plan,
va.wahr_level,
ast.txt_wert,
va.pau_preis,
va.pausch_kz,
o.txt_k,
knd.knd_name


If I use only Parameter {P1..} it works fine. But when using both parameters I always get ORACLE Error Messages like "SQL command not ended properly" or "right parenthesis missing" and so on.

Both parameters are string type.
How to make it work correctly?

Thanks,
mpegjunkie

 

[Turkbear]

Hi,
have you confirmed what is actually being passed by the parameters when using 2 of them..
BTW, does using only {?P2_Ort} cause an error.

Place both parameters in the Report Header to see their actual contents.

 

[mpegjunkie]

Hi Turkbear,

thanks for your tip! It works when I do the following:

o.txt = '{P2_Ort}'

i.e. I have to enclose the parameter in single quotes.

Strange behaviour, but once you know, no problem...

mpegjunkie