Editing CR's base SQL query?

[Former Member]

Is there a way to edit the SQL query that Crystal Reports is using to pull its original data? Here's my problem:

I'm tasked with writing a report on transactions from the GL. Datatel's Colleague, our newly implemented system, stores transaction data in a table called "GLA_2008 (or whatever FY)". The beginning balance, however, is stored in another table called "GLS_2008". A for "activity", S for "summary", right? Now, the GLS table is keyed with the GL account numbers. The GLA table, however, is keyed with numbers that are the GL account number with the transaction number appended to that with an asterisk delimiter (11010102340000*198). Yes, this is crappy database design. Not my fault. More importantly, it's unchangeable as that's how this system needs it to be.

So, what I need to do is get into the query that CR is using to pull this data and join the GL account number from the GLS table with LEFT(GLA_2008.GLA_ID, 15) rather than the actual key field, as those won't join. Is there any way to do this? It seems like it would be rather simple, but the "Show SQL Query" thing is read-only.

It's worth pointing out that without that join, CR appears to pull in all records from both tables, and that crashes CR, so doing a formula field with LEFT() in it and making the join in the report rather than the original query doesn't seem to work.

[fritzfeltus · Accepted solution]

You could "Set database location" to a new command object, in which you copy and paste your SQL, plus the additional SQL that you need.

Cheers,

Fritz

[Former Member · Accepted solution]

Fritz,

Bingo.

Thanks!

[Former Member]

Just go to the "Select Expert" for Records and add:

LEFT(GLA_2008.GLA_ID, 15) = account number

It just tried it and it works, with the "LEFT" function and all (I used my own string field of course...).

It doesn't show up in "Show SQL", but it sure is filtering by it.

The Panda