Solved

SQL Expression field

Posted on 2004-10-08
12
569 Views
Last Modified: 2008-02-01
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"=ACCOUNT."ACCOUNTID")


Thanks a lot !

Regards

David
0
Comment
Question by:David MacDonald
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 10

Expert Comment

by:ebolek
ID: 12258884
check this document out. It explains in details how to do it

http://support.businessobjects.com/communityCS/TechnicalPapers/sqlexpressions.pdf.asp

Regards
Emre
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12258894
I believe you need to include both tables in the FROM clause
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT, ACCOUNTSUMMARY WHERE ACCOUNTSUMMARY."PARENTID"=ACCOUNT."ACCOUNTID")

If that doesn't work  try

SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT  
or
SELECT ACCOUNT.* FROM ACCOUNT

mlmcc


0
 
LVL 10

Expert Comment

by:ebolek
ID: 12258963
try this
(SELECT ACCOUNT."ACCOUNTID" FROM ACCOUNT
INNER JOIN AccountSummary on ACCOUNTSUMMARY."PARENTID"=ACCOUNT."ACCOUNTID")
if fails
try this
SELECT ACCOUNT.ACCOUNTID FROM ACCOUNT
INNER JOIN AccountSummary on ACCOUNTSUMMARY.PARENTID=ACCOUNT.ACCOUNTID)
0
 
LVL 1

Author Comment

by:David MacDonald
ID: 12259354
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 ?
0
 
LVL 1

Author Comment

by:David MacDonald
ID: 12259419
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".
0
 
LVL 1

Author Comment

by:David MacDonald
ID: 12259439
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...
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12260434
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

0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 75 total points
ID: 12260499
Correction, I didn't proof the SQL Expression enough:

(
SELECT DISTINCT
  ACCOUNTSUMMARY."ACCOUNT"
FROM
  SYSDBA.ACCOUNTSUMMARY
WHERE
  "ACCOUNT"."ACCOUNTID" =ACCOUNTSUMMARY."PARENTID"
)

~Kurt
0
 
LVL 1

Author Comment

by:David MacDonald
ID: 12261031
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 .. :-(
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12262272
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
0
 
LVL 1

Author Comment

by:David MacDonald
ID: 12287695
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 !
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12287945
I'm glad I could help!

~Kurt
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now