SQL Expression Fields in C.R.9

[Former Member]

I'm having trouble getting a SQL Expression to work. As an example, say there are two tables: Cust, which has the columns ID, TotalPurchases, and Name; and Outstanding which has columns ID, InvoiceNum and Amount. I want a report that would look like:

ID     Name       Purchases  Outstanding
0001   Joe Smith $15,232.35      $956.34
0002   Ann Who    $1,200.33    $1,000.12

I try adding a SQL Expression like this:

( SELECT SUM(Amount) FROM Outstanding o WHERE o.ID=Cust.ID )

But I get an error: "Unknown column Cust.ID in where clause".

I've tried everything I can think of. Unfortunately, SQL Expressions seem to be virtually undocumented, and I can't find much that's helpful on the web.

Thanks,

John

[former_member11696 · Accepted solution]

Hi John,

SQL Expressions should not be used to SELECT fields. Use a Command Object.

Thank you

Don

[Former Member]

I marked the question as solved. Not sure it is, but since I can't get it to work any other way, Don's answer seems to be appropriate.

Thanks all for your help!

John

[abhilash_kumar]

Hi John,

That's a fairly simple SQL Expression that returns 1 column.

Try placing double-quotes around the Cust.Id field or the best thing to do would be to double-click the field name from the report fields tree in the Formula Workshop.

Hope this helps!

-Abhilash

[JWiseman]

John,

you will want to ensure that the non-aliased tables are referenced in the where clause of the sql expression field.

for example, using the xtreme sample access database...

(SELECT Sum(`B1`.`Customer ID`) FROM `Customer` `B1` WHERE `B1`.`Customer ID` = `Customer`.`Customer ID`)

note that the above has both the aliased table B1 and the normal customer table from the main report referenced in the Where clause.

cheers,

jamie

[Former Member]

Sorry for the delay getting back to you all.

I set up a report against the xtreme database sample. Added the Customer table, and put the columns "Customer ID" and "Customer Name" on the report. I then created a new SQL Expression and entered the following:

( SELECT SUM(`Orders`.`Order Amount`) FROM `Orders`
  WHERE `Orders`.`Customer ID`=`Customer`.`Customer ID`)

I could save the new SQL Expression, which is farther than I got in the real report. I added the SQL Expression field to the report. But when I try to preview the report, I get an error:

Error in compiling SQL Expression :
Failed to open a rowset.
Details: ADO Error Code: 0x800004005
Source: Microsoft JET Database Engine
Description: Unspecified Error
Native Error: 62411

I suspect that's the MS Access version of the error I was getting with MySQL.

[JWiseman]

you should try using an alias in the sql expression for the table used in the FROM clause...i.e.

(

SELECT Sum(`o1`.`Order Amount`) FROM `Orders` `o1`

WHERE `o1`.`Customer ID` = `Customer`.`Customer ID`

)

also ensure that the Orders table is not used in the main report or the sql above will have to be changed with more aliasing etc.

jw