Problem with SQL-Statement

T

Thomas Kehl

Hi!

I use ADO - OleDb and a Microsoft Access DB.
Now, I try to execute the fallowing sql-statement:

select *, (select ADNR from AVE_AD a where A.RID = ADRID) as ADNR, (select
NAME1 + ' ' + NAME2 from AVE_AD a where A.RID = ADRID) as NAME, (select CURR
from SYS_CURR c where C.RID = CURRRID) as CURR, (select KPNR from FIBU_KP k
where K.RID = FIBU_BUCH.KPRID) as KPNR, (select BCODE + ' ' + BEZ from
FIBU_BCODE b where B.RID = BCODERID) as BCODE, (select TAXCODE from SYS_TAX
t where T.RID = TAXRID) as TAXCODE, FIBU_BUCH.RID as RID from FIBU_BUCH
where (CLTRID = '19cba318-338c-4098-ba54-c126dcdf8869' AND GJAHRRID =
'7E089F7D-9492-41d3-8C17-1CD64DC0ABC3' AND JCODERID =
'4812DA72-0999-4a60-9F67-CC31E1D4C259') AND [NAME] Like 'sc%' order by DATUM
Desc,LFNR Desc,SUBLFNR


and get an exception:
for at least as one requestet paramter would no value be set
(translatet from german to english)


when i call the statement directly in access, access will request for a
parameter for the field NAME, but i don't know why :-(

can anyone help me?

thanks!

Thomas
 
Z

Ziga Jakhel

Here goes...

I first reformatted your SQL statement statement for clarity:

---
select *
, (select ADNR from AVE_AD a where A.RID = ADRID) as ADNR
, (select NAME1 + ' ' + NAME2 from AVE_AD a where A.RID = ADRID) as NAME
, (select CURR from SYS_CURR c where C.RID = CURRRID) as CURR
, (select KPNR from FIBU_KP kwhere K.RID = FIBU_BUCH.KPRID) as KPNR
, (select BCODE + ' ' + BEZ from FIBU_BCODE b where B.RID = BCODERID) as
BCODE
, (select TAXCODE from SYS_TAX t where T.RID = TAXRID) as TAXCODE
, FIBU_BUCH.RID as RID
from FIBU_BUCH
where (CLTRID = '19cba318-338c-4098-ba54-c126dcdf8869'
AND GJAHRRID = '7E089F7D-9492-41d3-8C17-1CD64DC0ABC3'
AND JCODERID = '4812DA72-0999-4a60-9F67-CC31E1D4C259')
AND [NAME] Like 'sc%' -- ******* this is the line that is giving you
trouble
order by DATUM Desc,LFNR Desc,SUBLFNR
---

My guess it that your problem lies in the last line of the WHERE clause,
where you reference a parameter named NAME.
This is because NAME is a calculated column you create *in* your query, so
there is no way you can use it in the criteria expression.

For a solution, I don't really see a way out of this without using some
temporary tables, or replacing the subqueries with multiple joins.

Something like this (SQL Server 2000 syntax, although should also work in
access) should work:

select f.*
, a.ADNR
, a.NAME1 + ' ' + a.NAME2 as "NAME"
, c.CURR
, k.KPNR
, b.BCODE
, t.TAXCODE
, FIBU_BUCH.RID as RID
from FIBU_BUCH f
inner join AVE_AD a
on f.RID = a.RID
inner join SYS_CURR c
on f.CURRID= c.RID
inner join FIBU_KP k
on f.KPRID = k.RID
inner join FIBU_BCODE b
on f.BCODERID = b.RID
inner join SYS_TAX
on f.TAXID = t.RID
where (f.CLTRID = '19cba318-338c-4098-ba54-c126dcdf8869'
AND f.GJAHRRID = '7E089F7D-9492-41d3-8C17-1CD64DC0ABC3'
AND f.JCODERID = '4812DA72-0999-4a60-9F67-CC31E1D4C259')
AND (a.NAME1 + ' ' + a.NAME2) Like 'sc%' -- ******* this is the line that
is giving you trouble

Regards & Tschuss,

Ziga Jakhel, MCAD.NET



Thomas Kehl said:
Hi!

I use ADO - OleDb and a Microsoft Access DB.
Now, I try to execute the fallowing sql-statement:

select *, (select ADNR from AVE_AD a where A.RID = ADRID) as ADNR, (select
NAME1 + ' ' + NAME2 from AVE_AD a where A.RID = ADRID) as NAME, (select CURR
from SYS_CURR c where C.RID = CURRRID) as CURR, (select KPNR from FIBU_KP k
where K.RID = FIBU_BUCH.KPRID) as KPNR, (select BCODE + ' ' + BEZ from
FIBU_BCODE b where B.RID = BCODERID) as BCODE, (select TAXCODE from SYS_TAX
t where T.RID = TAXRID) as TAXCODE, FIBU_BUCH.RID as RID from FIBU_BUCH
where (CLTRID = '19cba318-338c-4098-ba54-c126dcdf8869' AND GJAHRRID =
'7E089F7D-9492-41d3-8C17-1CD64DC0ABC3' AND JCODERID =
'4812DA72-0999-4a60-9F67-CC31E1D4C259') AND [NAME] Like 'sc%' order by DATUM
Desc,LFNR Desc,SUBLFNR


and get an exception:
for at least as one requestet paramter would no value be set
(translatet from german to english)


when i call the statement directly in access, access will request for a
parameter for the field NAME, but i don't know why :-(

can anyone help me?

thanks!

Thomas
Click to expand...
 
Top