Error in compiling SQL Expression

[Former Member]

In the [; thread, Kurt Reinhardt wrote "Please note, a SQL Expression can only return a single, distinct value per each record in the main recordset.", I tried this where my main Record Selection's SQL query is:

{GLF_LDG_ACC_TRANS.ldg_name} = {@&CH_LDG_GL_ACT_CURR} and
{GLF_LDG_ACC_TRANS.period} = {@&CH_PERIOD_GL_CURR} and
{GLF_LDG_ACC_TRANS.ACCNBRI}  "99991103" and
{GLF_LDG_ACC_TRANS.DOC_REF1}  "{%SundryCreditors}" and
{GLF_LDG_ACC_TRANS.DOC_TYPE} = "$APINVCE"

My SQL Expression %SundryCreditors is:

select doc_ref1
from glf_ldg_acc_trans
where ldg_name = 'APLED09'
  and period = "GLF_LDG_ACC_TRANS"."PERIOD"
  and doc_ref1 = "GLF_LDG_ACC_TRANS"."DOC_REF1"
  and accnbri like 'SUNDRY%'

which essentially queries the same table as the main recordset using the same key fields, and returns one row only. However, when I click on the Check button in the Formula Workshop window, I get the error message "Error in compiling SQL Expression : Database Connection Error: '42000:MicrosoftODBC SQL Server DriverSQL ServerIncorrect syntax near the keyword 'select'. Database Vendor Code: 156 '".

Do you know what is my issue?

[Former Member]

If you wrote your SQl statement in the crystal reports formula workshop then the problem is that it is not crystal reports syntax. SQL cannot be writen there.

[Former Member]

In the thread started by "maas maas", Kurt wrote SQL Expressions are fantastic for SQL SELECT statement. Can you please clarify your statement.

[Former Member]

You must do it in a command object-go to your database expert and select ADD COMMAND. It took me a bit to find it the first time and I am just learning to use it.

[former_member11696]

Kurt is wrong, SQL Expressions was not designed to run SELECT statements. When you are in the Formula editor expand the Function list and you will not see a SELECT function. They are to be used as functions for filtering only, not as data stores.

Thank you

Don

[Former Member]

And Don is right. You can not use SQL expressions, but you can use the ADD COMMAND and write a SQL statement.