[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

query conversion T-SQL -> Jet

Posted on 2013-01-08
8
Medium Priority
?
531 Views
Last Modified: 2013-02-24
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
0
Comment
Question by:ja-rek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38756889
Try this Perhaps:


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)))
0
 
LVL 1

Author Comment

by:ja-rek
ID: 38756911
boag2000: unfortunately it doesn't work
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38756993
select *
from cdn.prjstruktura
where exists (select * from cdn.konta
inner join cdn.dekrety on KKS_GIDNumer = DT_KKSNumer
where kks_konto like '5*-' & right('0000' & prj_id, 5) & '-*' or 
kks_konto like '7*-' & right('0000' & prj_id, 5)

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38757655
Can you define exactly what "doesn't work" means?

In any event try, it seems like matthewspatrick has your syntax corrected.
0
 
LVL 1

Author Comment

by:ja-rek
ID: 38759064
I have changed also cast to convert to make it work.
0
 
LVL 1

Author Comment

by:ja-rek
ID: 38759076
thanks to all Experts for interest in my question!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38759287
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38761389
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question