Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on SQL

Author  Topic 

satyen
Starting Member

21 Posts

Posted - 2013-02-05 : 10:33:30
Hello, I am getting a error message -

'EMSError
Incorrect systax error near ')'
Incorrect systax error near ')'
Incorrect systax error near ')'
at Line:40, Char:32.'

-------------------------------------

for the following syntax:


Var
casecode;

begin

CaseCode := CaseData.CaseCode;

GridForm.Query.SQL.Clear;

GridForm.Query.SQL.Add('select c.casecode,');
GridForm.Query.SQL.Add('c.name AS ''Case Name'',');
GridForm.Query.SQL.Add('co.name AS ''Creditor'',');
GridForm.Query.SQL.Add('co.cref AS ''Reference'',');

GridForm.Query.SQL.Add('(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode =' +Quotedstr(CaseData.CaseCode)+ ') AS ''Case Administrator'',');

GridForm.Query.SQL.Add('(select ct.nominee from ips_casestaff ct where ct.Role = 10 and ct.nomineetype = 1 and c.casecode = ' +QuotedStr(CaseData.CaseCode)+ ') AS ''Trustees Name'',');

GridForm.Query.SQL.Add('mee.meetingpurpose AS ''Meeting Purpose'',');

GridForm.Query.SQL.Add('c.appdate AS ''Date of appointment'',');

GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL PROGRESS'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ') order by moddatetime) AS ''Progress Report'',');

GridForm.Query.SQL.Add('(select top (1)no.shortnote from ips_notes no where no.ntype = ''BKYPROP'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ') order by moddatetime) AS ''Assets & Value'', ');

GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL DISTRIBUTION'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ') order by moddatetime) AS ''Estimated Time of Dividend'',');

GridForm.Query.SQL.Add('from ips_case c left join ips_contact co ON c.casecode = co.casecode '); //and inner join ips_casestaff ct ON ct.casecode = co.casecode and left join ips_meeting me ON mee.casecode = c.casecode');
GridForm.Query.SQL.Add('inner join ips_casestaff ct ON ct.casecode = co.casecode');
GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode');



GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ '');
GridForm.Query.SQL.Add('where co.name LIKE ''%Council%'', ');
GridForm.Query.SQL.Add('where ct.Role = 30 ');
GridForm.Query.SQL.Add('where ct.nomineetype = 1 ');

GridForm.Query.Active := TRUE;
GridForm.TableSource.DataSet := GridForm.Query;
GridForm.cxGrid1DBTableView1.DataController.DataSource := GridForm.TableSource;
GridForm.cxgrid1DBTableView1.DataController.CreateAllItems;
end;


I would appreciate if anyonne can help me. Much appreciated.

Thank you,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:36:29
i think

GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ '');
GridForm.Query.SQL.Add('where co.name LIKE ''%Council%'', ');
GridForm.Query.SQL.Add('where ct.Role = 30 ');
GridForm.Query.SQL.Add('where ct.nomineetype = 1 ');


should be

GridForm.Query.SQL.Add('where c.casecode =''' +Quotedstr(CaseData.CaseCode)+ '''');
GridForm.Query.SQL.Add('and co.name LIKE ''%Council%''');
GridForm.Query.SQL.Add('and ct.Role = 30 ');
GridForm.Query.SQL.Add('and ct.nomineetype = 1 ');

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 2013-02-05 : 10:43:55
I am still getting the same error :( --- any other ideas?

'EMSError
Incorrect systax error near ')'
Incorrect systax error near ')'
Incorrect systax error near ')'
at Line:40, Char:32.'
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 10:50:05
If you are able to add a print or debug statement immediately after you finish constructing the SQL query, do that, print out the statement and post it or look at it. Also, include a few spaces - for example, in the following two statements, put a space before the WHERE on the second line; otherwise, the query looks like "....c.casecodewhere c.casecode".
GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode');
GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ '');
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 2013-02-05 : 10:55:43
Still having no luck :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:57:27
do a print of gridform.query and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 10:59:58
Are you able to print out the value in GridForm.Query.SQL just before the statement "GridForm.Query.Active := TRUE;" ? That is the easiest way to figure out the changes you need to make. Post that string to the forum and someone would be able to tell you the exact changes you need to make. Without that, it is a painful and slow process.
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 2013-02-05 : 11:09:19
Nope I am not able to print out the string

I have changed the syntax But I am getting the following error now -

EMS Error
Incorrect sytax near the keyword 'from'. at Line: 40, Char 32

Syntax:

Var
casecode;

begin

CaseCode := CaseData.CaseCode;

GridForm.Query.SQL.Clear;

GridForm.Query.SQL.Add('select c.casecode,');
GridForm.Query.SQL.Add('c.name AS ''Case Name'',');
GridForm.Query.SQL.Add('co.name AS ''Creditor'',');
GridForm.Query.SQL.Add('co.cref AS ''Reference'',');

GridForm.Query.SQL.Add('(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode =' +Quotedstr(CaseData.CaseCode)+ ') AS ''Case Administrator'',');

GridForm.Query.SQL.Add('(select ct.nominee from ips_casestaff ct where ct.Role = 10 and ct.nomineetype = 1 and c.casecode = ' +QuotedStr(CaseData.CaseCode)+ ') AS ''Trustees Name'',');

GridForm.Query.SQL.Add('mee.meetingpurpose AS ''Meeting Purpose'',');

GridForm.Query.SQL.Add('c.appdate AS ''Date of appointment'',');

GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL PROGRESS'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS ''Progress Report'',');

GridForm.Query.SQL.Add('(select top (1)no.shortnote from ips_notes no where no.ntype = ''BKYPROP'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS ''Assets & Value'', ');

GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL DISTRIBUTION'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS ''Estimated Time of Dividend'',');

GridForm.Query.SQL.Add('from ips_case c left join ips_contact co ON c.casecode = co.casecode '); //and inner join ips_casestaff ct ON ct.casecode = co.casecode and left join ips_meeting me ON mee.casecode = c.casecode');
GridForm.Query.SQL.Add('inner join ips_casestaff ct ON ct.casecode = co.casecode');
GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode');



GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ '' );
GridForm.Query.SQL.Add('and co.name LIKE ''%Council%'', ' );
GridForm.Query.SQL.Add('and ct.Role = 30 ' );
GridForm.Query.SQL.Add('and ct.nomineetype = 1 ' );

GridForm.Query.Active := TRUE;
GridForm.TableSource.DataSet := GridForm.Query;
GridForm.cxGrid1DBTableView1.DataController.DataSource := GridForm.TableSource;
GridForm.cxgrid1DBTableView1.DataController.CreateAllItems;
end;


Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 12:40:22
I thought I will go through the code and make the corrections, but there are a number of things that I don't know about your environment such as,
are string literals created using single quotes,
does Quotedstr function return the result with single quotes etc.
Syntax errors aside, it is also hard to see the logic.
In any case, here is my attempt - it would most likely give you syntax errors when sent to the server.

What is the environment you are using to develop the code? Surprising that there are IDE's that do not let you print out the value of strings when developing:
begin

CaseCode := CaseData.CaseCode;

GridForm.Query.SQL.Clear;

GridForm.Query.SQL.Add('select c.casecode,');
GridForm.Query.SQL.Add('c.name AS "Case Name",');
GridForm.Query.SQL.Add('co.name AS "Creditor",');
GridForm.Query.SQL.Add('co.cref AS "Reference",');

GridForm.Query.SQL.Add('(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode =' +Quotedstr(CaseData.CaseCode)+ ') AS "Case Administrator",');

GridForm.Query.SQL.Add('(select ct.nominee from ips_casestaff ct where ct.Role = 10 and ct.nomineetype = 1 and c.casecode = ' +QuotedStr(CaseData.CaseCode)+ ') AS "Trustees Name",');

GridForm.Query.SQL.Add('mee.meetingpurpose AS "Meeting Purpose",');

GridForm.Query.SQL.Add('c.appdate AS "Date of appointment",');

GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL PROGRESS'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS "Progress Report",');

GridForm.Query.SQL.Add('(select top (1)no.shortnote from ips_notes no where no.ntype = ''BKYPROP'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS "Assets & Value", ');

GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL DISTRIBUTION'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS "Estimated Time of Dividend" ');

GridForm.Query.SQL.Add('from ips_case c left join ips_contact co ON c.casecode = co.casecode //and inner join ips_casestaff ct ON ct.casecode = co.casecode and left join ips_meeting me ON mee.casecode = c.casecode');
GridForm.Query.SQL.Add('inner join ips_casestaff ct ON ct.casecode = co.casecode ');
GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode ');



GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ ' ' );
GridForm.Query.SQL.Add('and co.name LIKE ''%Council%'' ' );
GridForm.Query.SQL.Add('and ct.Role = 30 ' );
GridForm.Query.SQL.Add('and ct.nomineetype = 1 ' );

GridForm.Query.Active := TRUE;
GridForm.TableSource.DataSet := GridForm.Query;
GridForm.cxGrid1DBTableView1.DataController.DataSource := GridForm.TableSource;
GridForm.cxgrid1DBTableView1.DataController.CreateAllItems;
end;
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 2013-02-06 : 05:33:22
I thought I will go through the code and make the corrections, but there are a number of things that I don't know about your environment such as,
are string literals created using single quotes = No with double quotes
does Quotedstr function return the result with single quotes etc = This script is run under a Database which contains a collections of records each having unique codes = casecode. So e.g. lets say I go into the record > casecode (PK) - 1234 - I run the script. It will read and understand the casecode and populate the fields from the record data in a set requirement.
Syntax errors aside, it is also hard to see the logic.
In any case, here is my attempt - it would most likely give you syntax errors when sent to the server.

What is the environment you are using to develop the code? Surprising that there are IDE's that do not let you print out the value of strings when developing: Notepad. There are no IDE's
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 2013-02-06 : 05:36:23
Answers

I thought I will go through the code and make the corrections, but there are a number of things that I don't know about your environment such as,

are string literals created using single quotes = No, with double quotes

does Quotedstr function return the result with single quotes etc =

This script is run under a Database which contains a collections of records each having unique codes = casecode. So e.g. lets say I go into the record > casecode (PK) - 1234 - I run the script. It will read and understand the casecode and populate the fields from the record data in a set requirement.

Syntax errors aside, it is also hard to see the logic.

In any case, here is my attempt - it would most likely give you syntax errors when sent to the server.

What is the environment you are using to develop the code? Surprising that there are IDE's that do not let you print out the value of strings when developing: Notepad. There are no IDE's
Go to Top of Page
   

- Advertisement -