SQL Expression field
Hi experts !
i have CR8.5 for reports on SalesLogix and i don't know how to have an SQL field ... I try to put an SQL statement but it always returns no row. I have two table ACCOUNT and ACCOUNTSUMMARY. Account contains data on many organization and account summary I use for the hierarchy between companies (Mother->daughter relation).
Now, Account summary only has all the company and their parentID wich can be NULL.
My report uses ACCOUNT to display some info, and I want to have the Name of the mother company when there is one. How can I do that with a SQL statement? or should I use a function ?
I am really lost, and and don't have a extensive knowledge of crystal report ... thank god it is the english version at least ...
Here is the SQL statement i tried, but i think CR interprets it as if i was naming the fields of the report instead of column names in the database.
It returns no error when i save it ... but when i preview my report, it return no rows and says something about an error detected by the database DLL.
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT WHERE ACCOUNTSUMMARY."PARENTID"=
Thanks a lot !
Regards
David
i have CR8.5 for reports on SalesLogix and i don't know how to have an SQL field ... I try to put an SQL statement but it always returns no row. I have two table ACCOUNT and ACCOUNTSUMMARY. Account contains data on many organization and account summary I use for the hierarchy between companies (Mother->daughter relation).
Now, Account summary only has all the company and their parentID wich can be NULL.
My report uses ACCOUNT to display some info, and I want to have the Name of the mother company when there is one. How can I do that with a SQL statement? or should I use a function ?
I am really lost, and and don't have a extensive knowledge of crystal report ... thank god it is the english version at least ...
Here is the SQL statement i tried, but i think CR interprets it as if i was naming the fields of the report instead of column names in the database.
It returns no error when i save it ... but when i preview my report, it return no rows and says something about an error detected by the database DLL.
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT WHERE ACCOUNTSUMMARY."PARENTID"=
Thanks a lot !
Regards
David
I believe you need to include both tables in the FROM clause
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT, ACCOUNTSUMMARY WHERE ACCOUNTSUMMARY."PARENTID"=
If that doesn't work try
SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT
or
SELECT ACCOUNT.* FROM ACCOUNT
mlmcc
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT, ACCOUNTSUMMARY WHERE ACCOUNTSUMMARY."PARENTID"=
If that doesn't work try
SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT
or
SELECT ACCOUNT.* FROM ACCOUNT
mlmcc
try this
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT
INNER JOIN AccountSummary on ACCOUNTSUMMARY."PARENTID"=
if fails
try this
SELECT ACCOUNT.ACCOUNTID FROM ACCOUNT
INNER JOIN AccountSummary on ACCOUNTSUMMARY.PARENTID=AC
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT
INNER JOIN AccountSummary on ACCOUNTSUMMARY."PARENTID"=
if fails
try this
SELECT ACCOUNT.ACCOUNTID FROM ACCOUNT
INNER JOIN AccountSummary on ACCOUNTSUMMARY.PARENTID=AC
ASKER
According to the docs ebolek referred me too, the SQL expression fields are not fit to support SQL queries ... i did'nt know that...
So i have to edit the SQL statement of my report query ? is it ?
So i have to edit the SQL statement of my report query ? is it ?
ASKER
Here is my main sql query :
SELECT
ACCOUNT."ACCOUNT",
IQ_ORGAN."NO_GESPR",
IQ_ORGAN."DE_REGN_ADMIN",
IQ_ORGAN."DE_MRC",
IQ_ORGAN."CO_SCIAN_PRINC",
IQ_ORGAN."DE_SCIAN_PRINC",
IQ_ORGAN."CO_STAT_ORGAN",
IQ_ORGAN."NM_DEMAR",
IQ_ORGAN."NM_DIRCT_PROJT"
FROM
SYSDBA.ACCOUNT ACCOUNT INNER JOIN SYSDBA.IQ_ORGAN IQ_ORGAN ON
ACCOUNT."ACCOUNTID" = IQ_ORGAN."ACCOUNTID"
ORDER BY
ACCOUNT."ACCOUNT"
That is without the mother-daughter relationship ... I tried to have a sql field that fetched the ACCOUNT."ACCOUNT" (company name) based on the ACCOUNTSUMMARY."PARENTID".
SELECT
ACCOUNT."ACCOUNT",
IQ_ORGAN."NO_GESPR",
IQ_ORGAN."DE_REGN_ADMIN",
IQ_ORGAN."DE_MRC",
IQ_ORGAN."CO_SCIAN_PRINC",
IQ_ORGAN."DE_SCIAN_PRINC",
IQ_ORGAN."CO_STAT_ORGAN",
IQ_ORGAN."NM_DEMAR",
IQ_ORGAN."NM_DIRCT_PROJT"
FROM
SYSDBA.ACCOUNT ACCOUNT INNER JOIN SYSDBA.IQ_ORGAN IQ_ORGAN ON
ACCOUNT."ACCOUNTID" = IQ_ORGAN."ACCOUNTID"
ORDER BY
ACCOUNT."ACCOUNT"
That is without the mother-daughter relationship ... I tried to have a sql field that fetched the ACCOUNT."ACCOUNT" (company name) based on the ACCOUNTSUMMARY."PARENTID".
ASKER
If i understand correctly, i should make the necessary ajustement to have the name of the mother company in my main query ... because i cause obtain in a sql field...
1) That document is worthless:-(. Instead, please refer to the following FAQ I wrote on another Technical Website. Its a little outdated, but has more detail.
http://www.tek-tips.com/faqs.cfm?fid=1377
2) SQL Expressions do support SQL Queries - you just need to encapsulate them within parentheses. The SQL must be supported by both your database and your database connection (native drivers, odbc, etc...).
3) When you say that this is your main SQL Query, do you mean that this is the SQL Generated by Crystal Reports (Database|Show SQL Query)? If so, then you should be able to create a SQL Expression as follows:
(
SELECT DISTINCT
ACCOUNT."ACCOUNT"
FROM
ACCOUNTSUMMARY
WHERE
"ACCOUNT"."ACCOUNTID" =ACCOUNTSUMMARY."PARENTID"
)
Basically, a SQL Expression will be added to the SELECT Clause of the SQL generated by Crystal Reports. It's essentially the same as a Function/User Defined Function (Oracle/SQL Server) or a subquery. In this case, I'm passing in the ACCOUNTID from your main report to the SQL Expression. This will ensure that you return only the Parent Account for the accountid that is included on the detail line. In order for this to work, however, you need to return the Account ID as a field in the report.
You should be able to copy this code directly into the SQL Expression builder. Next, remove the "ACCOUNT"."ACCOUNTID", look for the Account ID field in your database fields list and insert the field in place of the field you just removed. I'm recommending that you do this just to be safe, so that you have the appropriate syntax for the field.
~Kurt
http://www.tek-tips.com/faqs.cfm?fid=1377
2) SQL Expressions do support SQL Queries - you just need to encapsulate them within parentheses. The SQL must be supported by both your database and your database connection (native drivers, odbc, etc...).
3) When you say that this is your main SQL Query, do you mean that this is the SQL Generated by Crystal Reports (Database|Show SQL Query)? If so, then you should be able to create a SQL Expression as follows:
(
SELECT DISTINCT
ACCOUNT."ACCOUNT"
FROM
ACCOUNTSUMMARY
WHERE
"ACCOUNT"."ACCOUNTID" =ACCOUNTSUMMARY."PARENTID"
)
Basically, a SQL Expression will be added to the SELECT Clause of the SQL generated by Crystal Reports. It's essentially the same as a Function/User Defined Function (Oracle/SQL Server) or a subquery. In this case, I'm passing in the ACCOUNTID from your main report to the SQL Expression. This will ensure that you return only the Parent Account for the accountid that is included on the detail line. In order for this to work, however, you need to return the Account ID as a field in the report.
You should be able to copy this code directly into the SQL Expression builder. Next, remove the "ACCOUNT"."ACCOUNTID", look for the Account ID field in your database fields list and insert the field in place of the field you just removed. I'm recommending that you do this just to be safe, so that you have the appropriate syntax for the field.
~Kurt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is there really a difference between "ACCOUNT"."ACCOUNTID" and ACCOUNT."ACCOUNTID" ?
Is "ACCOUNT"."ACCOUNTID" a reference to the report field ?
Forgive me for being slow ... it's my forst time with CR and without any adequate formation .. :-(
Is "ACCOUNT"."ACCOUNTID" a reference to the report field ?
Forgive me for being slow ... it's my forst time with CR and without any adequate formation .. :-(
This isn't the only difference in the SQL Expression, it has changed significantly ;-).
You need to pass in the ACCOUNTID from your report into the SQL Expression. Crystal Reports will sometimes format fields in the "ACCOUNT"."ACCOUNTID" format, which is why I posted it that way. That's also why I recommend you delete the field from the WHERE clause of the SQL Expression and insert the field from the database fields list (to be safe and allow Crystal to insert its own syntax for the field).
~Kurt
You need to pass in the ACCOUNTID from your report into the SQL Expression. Crystal Reports will sometimes format fields in the "ACCOUNT"."ACCOUNTID" format, which is why I posted it that way. That's also why I recommend you delete the field from the WHERE clause of the SQL Expression and insert the field from the database fields list (to be safe and allow Crystal to insert its own syntax for the field).
~Kurt
ASKER
Hi Kurt,
i finally get it ... here is the final query :
(
SELECT DISTINCT accsum.ACCOUNT
FROM ACCOUNTSUMMARY accsum
WHERE accsum.ACCOUNTID = ACCOUNT."PARENTID"
)
Thank you for guiding me trough this !
i finally get it ... here is the final query :
(
SELECT DISTINCT accsum.ACCOUNT
FROM ACCOUNTSUMMARY accsum
WHERE accsum.ACCOUNTID = ACCOUNT."PARENTID"
)
Thank you for guiding me trough this !
I'm glad I could help!
~Kurt
~Kurt
http://support.businessobjects.com/communityCS/TechnicalPapers/sqlexpressions.pdf.asp
Regards
Emre