• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

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"=ACCOUNT."ACCOUNTID")


Thanks a lot !

Regards

David
0
David MacDonald
Asked:
David MacDonald
  • 5
  • 4
  • 2
  • +1
1 Solution
 
ebolekCommented:
check this document out. It explains in details how to do it

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

Regards
Emre
0
 
mlmccCommented:
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
 
ebolekCommented:
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.

 
David MacDonaldChargé de projet processus d’affaires et systèmes TIAuthor Commented:
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
 
David MacDonaldChargé de projet processus d’affaires et systèmes TIAuthor Commented:
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
 
David MacDonaldChargé de projet processus d’affaires et systèmes TIAuthor Commented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Correction, I didn't proof the SQL Expression enough:

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

~Kurt
0
 
David MacDonaldChargé de projet processus d’affaires et systèmes TIAuthor Commented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
David MacDonaldChargé de projet processus d’affaires et systèmes TIAuthor Commented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
I'm glad I could help!

~Kurt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now