If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

when running the app I get an error message :Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(TRDAccSel="M")SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter) ' at line 1

You need to have a valid sql statement for this. The if... is not valid sql. That's why you get this error. Depending on your needs you need to use something to work-around. I.e. hide the field if it doesn't matches the criteria, change the filter of showing the correct value(s) or use a lookup (caption link).

Comment

In the single record form I have a number of fields inter alia TRDAccSel (This field has 3 select options M,S,G)
Depending on the option selected the field TRDAcc must display accounts from one of three tables

I have been trying for the past week to get this right to no avail

What work around will do the trick
I have tried ajax on change; sc_lookup

Comment

It needs a bit of work but with this you probably get more in the right direction.
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters1
WHERE AttCustCode=[global_attcode] and '1'=[dbselector]
union
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters2
WHERE AttCustCode=[global_attcode] and '2'=[dbselector]
union
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters3
WHERE AttCustCode=[global_attcode] and '3'=[dbselector]

Assuming matters1, matters2 and matters3 have similar columns that are checked.

I tested in oracle a similar one of two tables both having a NR and NAAM as columns.
select nr,naam from acc where '1'='2'
union
select nr,naam from loc where '2'='X';
-- no rows as result

select nr,naam from acc where '1'='1'
union
select nr,naam from loc where '2'='1';
--only the rows of acc

select nr,naam from acc where '1'='1'
union
select nr,naam from loc where '2'='2';
--the rows of acc and loc

So you may have to make something like that.
Be aware that updating will probably fail.
I havent tried it in scriptcase but it is a normal way to select data from different tables semi dynamically.