Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Expression field

Posted on 2004-10-08
12
Medium Priority
?
593 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 101

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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 300 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

972 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