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 · Accepted solution]

If you know what you're doing, you can add a full select statement into a SQL Expression... And Kurt is correct when he says that it may only return a single row of info. Don is also correct when he says that this not what SQL commands are designed for.

Here's what's happening...

When you link your tables in the Database Expert and drop fields onto the design surface (and optionally, add criteria to the Select Expert), CR builds out a SQL statement that can is to be sent out to the db server so that the data can be returned. You can view this SQL by going to Database > Show SQL Query...

When you add a SQL Expression, CR inserts it into the SQL Query as another data column. This is typically done to take advantage of functions that are available in the data base but not in CR.

A few crafty individuals, like Kurt, have figured out that you can in fact inset a full SELECT query into a SQL Expression and it will be inserted into the CR generated querie's SELECT list as a sub-query.

Bear in mind 2 things... #1) You have to be familiar with SQL syntax, namely you have to know the proper syntax for using sub-queries in a SELECT list in your data base. #2) CR doesn't officially support is behavior and they won't offer you any help if the current functionality is removed in later versions.

HTH,

Jason

[Former Member · Accepted solution]

And Don is right... You can not use Crystal Reports SQL Expressions fields workshop to write a SQL select statement.

(if you remember,Jas, I tried too ; D)

[Former Member · Accepted solution]

I can create a SQL Expression with the SQL statement below, except the values are hardcoded and not joined to the main record set's table. I was hoping that it can "return a single, distinct value per each record in the main recordset" per Kurt's thread.

(
select x.doc_ref1
from glf_ldg_acc_trans x
where x.ldg_name = 'APLED09'
 and x.period = 10
 and x.doc_ref1 = '200980528'
 and x.accnbri like 'SUNDRY%'
)

Note that the SQL statement must be enclosed within brackets to satisfy CR.

I am using a Command to join tables and include the above as a subquery, except I cannot get CR to recognise a Formula Field in here. I know I can create a Parameter List, but this will come up as a prompt every time the report is run which does not work well with my ERP application - Finance One.

[JWiseman · Accepted solution]

hi HSung,

you are very close...you need an alias that is related to the rest of the query, using a where clause, to get a single value for each record that the command returns.

for example, using the xtreme sample database that ships with crystal, the subselect creates a running total on last year's sales...

SELECT

`Customer`.`Last Year's Sales`,

`Customer`.`Customer Name`,

(SELECT Sum(`B1`.`Last Year's Sales`) FROM `Customer` `B1` WHERE `B1`.`Customer ID` <= `Customer`.`Customer ID`) AS RunningTotalSales

FROM `Customer` `Customer`

the above uses the alias B1 in the subselect and the where clause establishes the relationship between the aliased b1 customer table and the customer table used by the rest of the command.

hope this helps,

jamie

[Former Member · Accepted solution]

Hi Jamie,

In your example, it looks like you have included both your main record set and sub-query in the SQL expression.

However, I want to keep my main record set separate from the sub-query which I have created as a SQL expression as below.

(
select x.doc_ref1
from glf_ldg_acc_trans x
where x.ldg_name = 'APLED09'
  and x.period = GLF_LDG_ACC_TRANS.PERIOD
  and x.doc_ref1 = GLF_LDG_ACC_TRANS.DOC_REF1
  and x.accnbri like 'SUNDRY%'
) as GLAT

I have given the sub-query an alias as GLAT in the SQL expression per your suggestion, but am getting an error during compiling "The multi-part identifier "GLF_LDG_ACC_TRANS.PERIOD" could not be bound". Both this and the GLF_LDG_ACC_TRANS.DOC_REF1 fields are in my main record set - i.e., both my main record set and sub-query selects from the same database table. I don't know why CR cannot bind these fields despite selecting them from the Field Tree in Formula Workshop.

If I put single quotes around the alias and field names, CR does not like these.

Cheers, Harry

Edited by: H Sung on Nov 8, 2010 11:45 PM

[JWiseman · Accepted solution]

hi Harry,

sorry, what i gave you before was the entire syntax for a Command object which is much easier and much more powerful to use than a sql expression for sub selects etc.

however, the syntax for the standalone sql expression would be something like

(SELECT Sum(`B1`.`Last Year's Sales`) FROM `Customer` `B1` WHERE `B1`.`Customer ID` <= `Customer`.`Customer ID`)

note that this syntax is for ms access using a native driver connection...syntax will vary from database to database and even differ for a different connection...i.e. odbc vs. wire protocol etc.

the reason why i would suggest a command with a sub-select built into the command is that you can get an error in a sql expression for an identifier not being bound like you are experiencing. i find this often happens when you are dealing with multiple tables, a complex where statement, etc.

jw

[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.