Alex


Normally I'd suggest adding a WHERE clause for Pecuniam. but I'm not sure what may be appropriate.

As a first step split out the sub-select and run as a separate query, enable production of the execution plan and see what it says. My bet would be that you're missing indices.

If the data isn't confidential or to large post the database to the binaries and I'll have a look. If it is to large then is there a site I can download from?

Roy Lambert

Roy

Thank you for your help. The whole database is around 178 MB. I could send it with Wetransfer . I would need an email to send you the link.

Alex

Roy

You were right. It was an indexing issue. The field ClosingID wasn't indexed. Now it works just wonderful. Thanks again for the pressure relief

Alex


No problem. I know how I felt when after beating myself up to learn DBISAM's SQL I then switched to ElevateDB and found things were different

The good news part is you now have an idea on how to address problems - break the sql down to the smallest parts and run and produce an execution plan - it is a brilliant utility.

Roy Lambert

Roy

That is what I did, tried the single parts by themselves and they were ok. Now I tried it on the whole table and the problem is that now it calculates perfectly the single row but sets the Euro field of all the remaining rows to NULL.

Alex


send the database, config & catalog files to roy at lybster dot me dot uk and I'll have a look

Roy Lambert

Alex wrote:

> Hello
>
> I am trying to make the following sql work :
>
> UPDATE Pecuniam
>   SET Pecuniam.Euro = OpLedger.Dare
>   FROM Pecuniam
>   INNER JOIN OpLedger
>   ON Pecuniam.ClosingID = OpLedger.CosingID
>   WHERE OpLedger.Verified = True
>
> but get the following error message:
> ElevateDB Error #700 An error was found in the statement at line 3
> and column 3 (Expected end of expression but instead found FROM)
>
> What is wrong?
>
> Thank you

ElevateDB is not capable of doing a join with a Update-statement. Would
be wonderfull if but at the moment you have to use something like this:

Update Pecuniam
set Pecuniam.Euro = (select OpLedger.Dare from OpLedger where
OpLedger.CosingID=Pecuniam.ClosingId)

where Pecuniam.ClosingId in
(select OpLedger.CosingId from OpLedger where OpLedger.Verified=true)


--
--
Yusuf Zorlu | MicrotronX

Yusuf

Thank you a lot  Yusuf, that nailed it.