ja-rek
asked on
query conversion T-SQL -> Jet
Dear Experts
this query works perfectly well in Ms SQL Server:
select *
from cdn.prjstruktura
where exists (select * from cdn.konta
join cdn.dekrety on KKS_GIDNumer = DT_KKSNumer
where (kks_konto like '5%-' + right('0000' + cast(prj_id as varchar), 5) + '-%' or kks_konto like '7%-' + right('0000' + cast(prj_id as varchar), 5)))
However, it doesn't work in Jet (e.g. when I try to execute this statement from Excel VBA or Access).
The problem is in this line:
where (kks_konto like '5%-' + right('0000' + cast(prj_id as varchar), 5) + '-%' or kks_konto like '7%-' + right('0000' + cast(prj_id as varchar), 5)))
Can somebody translate it to Jet SQL please?
thanks
Jarek
this query works perfectly well in Ms SQL Server:
select *
from cdn.prjstruktura
where exists (select * from cdn.konta
join cdn.dekrety on KKS_GIDNumer = DT_KKSNumer
where (kks_konto like '5%-' + right('0000' + cast(prj_id as varchar), 5) + '-%' or kks_konto like '7%-' + right('0000' + cast(prj_id as varchar), 5)))
However, it doesn't work in Jet (e.g. when I try to execute this statement from Excel VBA or Access).
The problem is in this line:
where (kks_konto like '5%-' + right('0000' + cast(prj_id as varchar), 5) + '-%' or kks_konto like '7%-' + right('0000' + cast(prj_id as varchar), 5)))
Can somebody translate it to Jet SQL please?
thanks
Jarek
ASKER
boag2000: unfortunately it doesn't work
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you define exactly what "doesn't work" means?
In any event try, it seems like matthewspatrick has your syntax corrected.
In any event try, it seems like matthewspatrick has your syntax corrected.
ASKER
I have changed also cast to convert to make it work.
ASKER
thanks to all Experts for interest in my question!
I am confused. Your question was about taking a T-SQL query, and rewriting it for Jet SQL.
What does the CONVERT function have to do with anything? There is no CONVERT function in Jet. There are various type conversion functions, such as CStr, CDate, etc., but Jet is perfectly capable of doing an implicit conversion when you concatenate a numeric value with a string.
What does the CONVERT function have to do with anything? There is no CONVERT function in Jet. There are various type conversion functions, such as CStr, CDate, etc., but Jet is perfectly capable of doing an implicit conversion when you concatenate a numeric value with a string.
I suspect the author realized after they posted that they were not using JET after all as they are using Pass-Through queries or ADO or something else, but I would not be surprised if we did not hear back from them.
select *
from cdn.prjstruktura
where exists (select * from cdn.konta
join cdn.dekrety on KKS_GIDNumer = DT_KKSNumer
where (kks_konto like "5*-" + right("0000" + cast(prj_id as varchar), 5) + "*" or kks_konto like "7*-" + right("0000" + cast(prj_id as varchar), 5)))