Is my query possible without using Custom SQL?

[rodp]

Please can you let me know if it is possible to achieve the following without using Custom SQL and if so how to achieve it?

I want to produce a ListGrid showing the following columns. The brackets show how to derive the data. The main data table in the query (crest_txn) has over 2 million records so we would like to make use of SmartGWT’s automatic data paging if possible. I haven't mentioned every column, for example if I want several columns from a table I'll only mention one below.

Business (crest_txn.business_code)
Txn Ref (crest_party.user_txn_ref where crest_party.sequence =1 and crest_txn.oid = crest_party.txn_id)
Type (transactions.type where crest_txn.oid = transactions.oid)
1st Party (participants.short_name where participants.oid = crest_party.participant and crest_party.sequence =1 and crest_txn.oid = crest_party.txn_id)
1st Party Account (member_accounts.crest_id where crest_party.account = member_accounts.oid and crest_party.sequence =1 and crest_txn.oid = crest_party.txn_id)
2nd Party (participants.short_name where participants.oid = crest_party.participant and crest_party.sequence =2 and crest_txn.oid = crest_party.txn_id)
ISIN (securities.isin where securities.oid = sec_movements.stock and sec_movements.txn_id = crest_txn.oid and sec_movements.sequence = 0)
Buy/Sell (set to B when sec_movements.sequence = 0 and sec_movements.txn_id = crest_txn.oid and sec_movements.credit_party = crest_party.oid and crest_party.sequence =1 and crest_txn.oid = crest_party.txn_id, set to S if sec_movements.debit_party = crest_party.oid, instead of sec_movements.credit_party = crest_party.oid, rest of query is the same, otherwise set to nothing)

We require inner joins between crest_txn and transactions and crest_txn and crest_party for 1st party (i.e. where crest_party.sequence = 1). All other crest_txn joins are outer.

The problems I have encountered when trying to construct the relevant ds.xml files are :
a) I can’t get the joins to take note of sequence when relevant e.g. 1st party is crest_party.sequence = 1 and 2nd party is crest_party sequence = 2.
b) The resulting SQL, maybe because I got the ds.xml wrong, can’t distinguish between the 2 joins to crest_party so the join from crest_party.account to member_accounts.oid gives an ambiguous column error.
c) Haven't attempted to set the Buy/Sell flag, I would use a CASE statement in SQL.

I can achieve the desired result via coding SQL in the DMI but obviously that bypasses data paging. Also I can code the ds.xml for joining crest_txn and transactions, it's the rest that's proving tricky.

Please let me know if anything is unclear or you’d like me to provide further information. Any suggestions of how to achieve the Listgrid with data paging will be greatly appreciated.

Thank you.

SmartClient Version: v8.3p_2013-04-23/PowerEdition Deployment (built 2013-04-23)

[Isomorphic]

Using <customSQL> doesn't mean paging is entirely disabled - see operationBinding.sqlPaging and the (linked) SQLPagingStrategy doc.

"sqlLimit" cannot be used for <customSQL> because we don't know enough about the structure of the query to safely add DB-specific SQL to ask for a specific row range.

However, with complex queries like the one you've got here, it's unclear whether "sqlLimit" will necessarily perform any better than "jdbcScroll" - letting the DB know that only a certain row range is required is unlikely to allow the DB to do less work, and a competently written JDBC driver will only stream the requested rows to the application server anyway.

So we'd recommend not spending any more effort on trying to make this into a clause-by-clause override unless and until you find out there's a performance issue with this query related to paging.

And even then, the better approach might be to go ahead and write <customSQL> and do the equivalent of "sqlLimit" yourself - after all $dsRequest.startRow and $dsRequest.endRow are available to you in the <customSQL> block, so if you know how to limit rows in SQL in this DB, you can add that SQL yourself.

[rodp]

Thanks for the response.
For information, in case anyone reads this thread, I've revisited my ds.xmls and realised that I'd forgotten that if I want the resulting SQL query to take note of criteria for a "includeFrom" ds.xml I must include the criteria field in the primary ds.xml (i.e. the ds.xml defining the ListGrid's datasource). Example - my primary ds.xml includes from table CREST_PARTY and I only want records where CREST_PARTY.SEQUENCE = 1 - the primary ds.xml must includeFrom crestParty.SEQUENCE or the criteria for SEQUENCE =1 is ignored.

Another question - is there any way to define a table alias in a ds.xml? Our CREST_PARTY table holds data for first parties (SEQUENCE = 1) and second parties (SEQUENCE = 2). So in pseudo-SQL I might SELECT CP1.NAME, CP2.NAME etc. FROM CREST_PARTY CP1, CREST_PARTY CP2 WHERE CP1.SEQUENCE = 1 AND CP2.SEQUENCE = 2. If in ds.xml I could refer to the CREST_PARTY table via an alias I could get data for both parties.

[Isomorphic]

No, right now there is no way to request that a table alias be generated. You have to replace the tableClause to do that, but, that's quite a simply clause to replace generally.