Solved

SQL Expression field

Posted on 2004-10-08
12
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 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