Crystal Reports: Using SQL Expression Fields

This document discusses the syntax requirements for SQL Expression fields.

The contents of a SQL Expression field is any valid SQL statement that returns a scalar value. The resulting SQL is added to the report’s SELECT clause. For example:

SELECT PAT_NAME,HOSP_ADMSN_TIME,HOSP_DISCH_TIME,
--SQL Expression
(
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)
--/SQL Expression
FROM PAT_ENC_HSP

Limitations

SQL Expression fields may only return a scalar value (one column and one row).
Parameter fields may not be used in SQL Expressions.

Features

A database [Oracle|SQL Server|Sybase] scalar function may be used in a SQL Expression field.
Case statements may be used in SQL Expression fields. For example (Oracle syntax):

(
CASE
WHEN PAT_ENC_HSP.HOSP_DISCH_TIME IS NULL THEN
'F'
WHEN PAT_ENC_HSP.HOSP_DISCH_TIME >= PAT_ENC_HSP.HOSP_ADMSN_TIME-7 THEN
'T'
ELSE
'F'
END
)

Syntax

The SQL statement needs to be enclosed in parenthesis:

(
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)

Double-quotation marks aren’t required if tables in the main query aren’t aliased:

(
--will compile
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)

(
–will compile
SELECT MAX(“HOSP_DISCH_TIME”)
FROM “PAT_ENC_HSP” “PAT_ENC_HSP2″
WHERE “PAT_ENC_HSP2″.”PAT_ID”=”PAT_ENC_HSP”.”PAT_ID”
AND “PAT_ENC_HSP2″.”PAT_ENC_CSN_ID” < “PAT_ENC_HSP”.”PAT_ENC_CSN_ID”
)
Double-quotation marks are required if tables in the main query are aliased:

(
--will compile
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP_
WHERE PAT_ENC_HSP2.PAT_ID="PAT_ENC_HSP_alias".PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < "PAT_ENC_HSP_alias".PAT_ENC_CSN_ID
)

(
–will not compile
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP_
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP_alias.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP_alias.PAT_ENC_CSN_ID
)
When using aggregate functions in the SQL Expression, do not include the ‘local’ table’s alias:

(
--will not compile
SELECT MAX(PAT_ENC_HSP2.HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)

(
–will compile
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)
When using aggregate functions in the SQL Expression, you can include the ‘main’ query’s table’s alias:

(
--will compile
SELECT MAX(PAT_ENC_HSP.HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)

If you are using a SQL Expression as a subquery and wish to link it to the detail row of your main report, do not include the table you wish to link to in the FROM clause of the subquery. For example:

(
SELECT MAX("IP_FLWSHT_MEAS_SP"."MEAS_VALUE")
FROM IP_FLWSHT_MEAS IP_FLWSHT_MEAS_SP,
IP_FLWSHT_REC IP_FLWSHT_REC_SP
WHERE "IP_FLWSHT_MEAS_SP"."FLO_MEAS_ID" in ('11')
AND "IP_FLWSHT_MEAS_SP"."FSD_ID" = "IP_FLWSHT_REC_SP"."FSD_ID"
AND "IP_FLWSHT_REC_SP"."INPATIENT_DATA_ID" = "PAT_ENC_HSP"."INPATIENT_DATA_ID"
)

The above query links to the PAT_ENC_HSP table in the main report by linking to the INPATIENT_DATA_ID field. To accomplish this, PAT_ENC_HSP is omitted from the FROM clause in the query.

23 thoughts on “Crystal Reports: Using SQL Expression Fields

  1. David

    I can’t get the subquery linking to work. I get an invalid column name error from oracle when I try to use the table from my main report in the WHERE clause but not the FROM clause. Anyone have any thoughts on this? I can be reached at david.norman@btradianz.com

  2. Tatterdemalian

    Thanks for the information… however, even after taking all these measures, my subquery will not compile. I keep getting an error on the WHERE clause of the subquery, saying that the column prefix ‘MainQueryTable’ does not match with a table name or alias name in the query.

    This is using Crystal Reports version 11, so maybe they have added “features” to prevent the use of any references to the main query.

    Can I post my code here, or is that against protocol?

  3. System Administrator

    You may want to try a simple SQL Expression (e.g. like SELECT Count(*) FROM table_name), to ensure that functionality works as expected.

    Post your code, including database type, if you’d like.

  4. Tatterdemalian

    Here is the current code.

    (
    SELECT sum(“payment”)
    FROM “MainQueryTable1″ sqt1
    INNER JOIN “MainQueryTable2″ sqt2 ON sqt1.”id” = sqt2.”table1id”
    WHERE sqt1.”accountNumber” = “MainQueryTable1″.”accountNumber”
    )

    The error about the column prefix in this case is on “MainQueryTable1″.

    I suppose that inner join is the problem, but unfortunately the database structure of our business requires that the payments be kept in a separate table from the account numbers, with the two tables linked by an inner join.

    The goal is to produce a subquery that returns the total of all payments on a given account, which will then be used in the record selection criteria of the report (in other words, only select records from accounts that don’t have a certain payment total). In turn, this is needed because the report has to return only specific payments in each account meeting certain other criteria, but also exclude all payments from any account whose total payment matches another criteria.

    In short, I need Crystal Reports to use a WHERE statement like:

    WHERE MainQueryTable1.code1 = value AND
    MainQueryTable2.code2 = anothervalue AND
    (
    SELECT sum(sqt2.payment)
    FROM MainQueryTable1 sqt1
    INNER JOIN MainQueryTable2 sqt2 ON sqt.id = sqt2.table1id
    WHERE sqt1.accountNumber = MainQueryTable1.accountNumber
    ) yetanothervalue

    I’ve tried to produce this behavior using combinations of record and group selection criteria, with no success. Either the calculation of the total payment on the account takes place after the record selection criteria screens out the most of the payments on the account, resulting in the wrong total being calculated, or the entire account is excluded when any of the payment or account codes fail to meet the record-level selection criteria.

  5. Tatterdemalian

    Apparently the comment posting software is a bit overzealous about stripping HTML tags. Let’s try that WHERE clause again…

    WHERE MainQueryTable1.code1 = value AND
    MainQueryTable2.code2 = anothervalue AND
    (
    SELECT sum(sqt2.payment)
    FROM MainQueryTable1 sqt1
    INNER JOIN MainQueryTable2 sqt2 ON sqt.id = sqt2.table1id
    WHERE sqt1.accountNumber = MainQueryTable1.accountNumber
    ) <> yetanothervalue

  6. System Administrator

    If I understand your goal, you probably want to remove the Payment table from the main report, if you only need its summary values.

    With the Payment table removed from the main query, create a SQL Expression named ‘SumOfPayment’ and enter this text:

    (
    SELECT Sum(payment)
    FROM Payment
    –validate that this linkage is correct
    INNER JOIN MainQueryTable1 ON mqt.id=id
    AND mqt.accountNumber=accountNumber
    )

    To use this in the record selection formula, enter:

    AND {%SumOfPayment} <> yetAnotherValue

  7. Tatterdemalian

    Unfortunately, I don’t only need it’s summary values… I also need to use some code values from the table in the record selection criteria as well. In the above examples, MainQueryTable2 contains the payments, and MainQueryTable1 contains the account numbers. What’s more, ONLY MainQueryTable1 contains the account numbers; the id field from MainQueryTable1 is the only field that can be used to join MainQueryTable1 to MainQueryTable2.

    If I was going to rewrite the entire database, I would add a “TotalPayment” field to MainQueryTable1, and maintain it using triggers for whenever payments are added or removed from MainQueryTable2. Unfortunately, that is far beyond the scope of both Crystal Reports, and pretty much my entire department.

    I’m hoping some solution can be reached within Crystal Reports XI itself. If only I could directly edit Crystal’s SQL query, as was possible in earlier versions of Crystal Reports, it would be simple.

  8. Craig Buchanan

    Try this in your SQL Expression (essentially adding a new instance of the payment table to the report):

    (
    SELECT Sum(payment)
    FROM PaymentTable
    WHERE id=MainQueryTable1.id
    )

    Otherwise, you can use a Command Object to contain the desired SQL, then use the CO as the sole ‘table’ in the report.

  9. Tatterdemalian

    Nope, it still says “The column prefix ‘MainQueryTable1′ does not match with a table name or alias name used in the query.”

  10. LoanVi Tran

    Hi, I tried to write the following SQL expression for Crystal Report, but it didn’t work. Please help!

    (
    SELECT SUM(SALE_COST)
    FROM TEST_TBL
    WHERE (EA_SUBTYPE = “Hours”) OR
    (EA_TYPE = “Labor” AND EA_SUBTYPE = ” “)
    )

  11. Craig Buchanan

    You may need to substitute single quotes for the double quotes. Try:

    (
    SELECT SUM(SALE_COST)
    FROM TEST_TBL
    WHERE (EA_SUBTYPE = ‘Hours’)
    OR (EA_TYPE = ‘Labor’ AND EA_SUBTYPE = ‘ ‘)
    )

  12. Rosa Ramos

    Hi.
    Please, you know the syntax of expressions SQL in Crystal Reports 7.

    In Crystal 9 and 11 they work but not in Crystal 7, always it gives me syntax error in the first field of the SELECT

    Ej: (DB: SQL Server)
    (SELECT “TC_Value” FROM “Table_Conf”
    WHERE “TC_Parameter” = ‘Path’)

  13. Sylesh Soman

    HELLO,

    IS THERE ANY WAY TO SEND A PARAMETER INTO THIS QUERY THAT IS CREATED IN THE SQL EXPRESSION FIELD.I AM NEW TO CRYSTAL REPORT…I CREATED A SQL EXPRESSION FOR ALL THE PRODUCTS…BUT I NEED IT TO BE FILTERED BY THE PARAMETER(PRODUCTS) THAT IS BEING PASSED INTO THE MAIN QUERY…

    ie, i have select profit from product_table—-working
    i need select profit from product_table where product={?product}

    is there anyway to do this..

    Thnks a Lot

  14. Craig Buchanan

    No, you can’t use a parameter in SQL Expression fields. You’ll need another approach. You may want to rewrite the report using a Command object. You will be able to use parameters and the scalar query in the SELECT clause.

  15. Pingback: Crystal Reporting in SalesLogix Using SQL Expressions - The Reporting Blog

  16. Carmen

    I have been using SQL expressions in various reports to pull out min/max date time values from a particular table but need to do something a little more complex. I am trying to display in a SQL expression field IP_FLWSHT_MEAS.MEAS_VALUE where IP_FLWSHT_MEAS.RECORDED_TIME = min(IP_FLWSHT_MEAS.RECORDED_TIME)for the corresponding hospital visit. I am hoping this makes sense as the examples above look to be from Epic’s Clarity data model. This type of request has been coming up frequently from users requesting reports and it would be great to be able to do so without creating a view every time. Is this possible?

  17. Jeff W

    8 years later and this article is still helping people. Finally figured out why my SQL Expression wasn’t working…didn’t enclose everything in parenthesis!

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>