Hello i have a question about Dynamic parameters.
i have a sql statement:

SELECT fcID, td.DossierID
FROM "TicketDossier" td

(Where fcID is a dynamic parameter)

After i execute this statement i get a #700 error:

ElevateDB Error #700 An error was found in the statement at line 1 and column 8 (Invalid expression ? found, dynamic parameter references not allowed)

i was wondering if there is a workaround to use a dynamic parameter in the SELECT section of the statement?

R.Timmermans

Ralf


ElevateDB doesn't support that. The only way currently is to build the statement at run time and substitute the column name that you want eg by using StringReplace. It doesn't matter much if you're using the statement once rather than in a loop. If its in a loop then you lose the prepared state on each iteration which will slow it down a bit.


Roy Lambert [Team Elevate]

Roy

I followed your instructions an came with this statement

SELECT 11 AS RfcID, td.DossierID
FROM "TicketDossier" td

Where 11 was a ReplaceString
So far everything goes as planned.

Altought when i executed the entire query:
(This is a copy to clipboard of the executed statement)

INSERT INTO "RfcDossier"
(
SELECT 11 AS RfcID, td.DossierID
FROM "TicketDossier" td
WHERE (td.TicketID = 16) AND
      NOT EXISTS
     (
       SELECT * FROM "RfcDossier" rd
       WHERE (rd.RfcID = 11) AND (rd.DossierID = td.DossierID)
     )
);

(Again the numbers here are a result of the ReplaceString function.)
I got a ElevateDB Error #700 An error was found in the statement at line 3 and column 8 (Expected ) but instead found ()

How come if i execute the statement with the Insert Into section above it, it doesn't recognize the ReplaceString value, while if i execute the statement without the insert into, everything seems fine.

R. Timmermans

Ralf

My eyeballs can't spot anything. The error looks as though its saying there should be a space and its finding nothing (or possibly a non printing character)


1. If you paste the sql you've posted here into EDBManager does it work in there?
2. You'll need to close (possibly unprepare but I don't think so) the query, do the StringReplace and then ExecSQL it.

I also just thought you could put it into a script. Not a lot different to doing a StringReplace though.

Roy Lambert [Team Elevate]

Roy

Posting the statement into the ElevateDB Manager didnt work for me.
Option 2 didnt work either.

I made a copy of my 2 tables for you, i was hoping that you could test it out.
And see if you can find a solution to the problem since i'm out of guesses


When i execute this statement:

INSERT INTO "RfcDossier"
(
SELECT 11 AS RfcID, td.DossierID
FROM "TicketDossier" td
WHERE (td.TicketID = 16) AND
      NOT EXISTS
     (
       SELECT * FROM "RfcDossier" rd
       WHERE (rd.RfcID = 11) AND (rd.DossierID = td.DossierID)
     )
);


I want to copy the result into the RFCDossier,

in the TicketDossier table there is 1 TicketID with 2 DossierID's
in the RFCDossier table there is 1 RFCID with 1 DossierID
.
when i execute its gonna check if the DossierID's found in the TicketDossier table do not exist in the RFCDossier already.

Then its gonna copy the ones that are not in the RFCDossier, to the table with the RFCDossierID as given.


I hope you can do something with the attachment.

"BR"

R. Timmermans

Ralf


I'll need the catalog as well

Roy Lambert [Team Elevate]

ps. You should really mpost attachments into the binaries ng

Roy

Apparently if i try to upload my attachment in the Binaries the website crashes.

if tried this 3 times now, and each time the website goes down after i hit the post button.
is there another way to supply you with the attachment?

R. Timmermans

Ralf


email them to me direct

Roy Lambert [Team Elevate]

support@elevatesoft.com is that the correct adres?

Ralf


nope - I don't work for ElevateSoft - the address is in the newsgroup headers

Roy Lambert [Team Elevate]