How to write <operationBindings> for SQL with subquery

[shkk15]

Hello,

I am completely new to SmartGWT. For the following query,

SELECT COLUMN_A, SCORE FROM
(
SELECT A AS COLUMN_A,
UTL_MATCH.JARO_WINKLER_SIMILARITY('A', 'A') AS SCORE,
FNAME,
LNAME,
MI
FROM MY_CONTACTS
)
WHERE (LNAME||''||FNAME||''||MI) IS NULL OR
SCORE = 100
ORDER BY
FNAME DESC;

How can I write the <operationBinding> entry in my *ds.xml file ? Is there some documentation available to detail out this configuration steps?

[Isomorphic]

There are several sections in the QuickStart Guide on SQL Templating, which link to more information in the reference. Start there, and come back if you have specific questions.

[shkk15]

Ok.

I did go through the Quick Start at [http://www.smartclient.com/releases/...art_Guide.pdf] before posting here.

The guide only mentioned the tags `<selectClause><tableClause><whereClause><valueClause><groupClause><orderClause>` for specifying SQL queries. I do not want to use `<customSQL>` since I would loose out on pagination.

Are you saying I have to generate the DS using java file? Currently I am writing the DS xml file myself.

[Isomorphic]

No. Divide your query into the clauses it needs to customize and customize those clauses via the tags you just mentioned. If it's not clear what to do, there are several samples in the EE Showcase.

[shkk15]

Ok. It took me more time to find the sample and I still don't know which one has a subquery !!

Can anyone please review if this is what would be needed to specify the query in datasource:

<selectClause>
	COLUMN_A, SCORE
</selectClause>
<tableClause>
	<selectClause>
		A AS COLUMN_A,
		UTL_MATCH.JARO_WINKLER_SIMILARITY('A', 'A') AS SCORE,
		FNAME,
		LNAME,
		MI
	</selectClause>
	<tableClause>
		MY_CONTACTS
	</tableClause>
</tableClause>
<whereClause>
	(LNAME||''||FNAME||''||MI) IS NULL OR
	SCORE = 100
</whereClause>
<orderClause>
	FNAME DESC
</orderClause>

[shkk15]

Okie am running into

Non supported SQL92 token at position: 34: selectClause

error. Can anyone please help me? Isomorphic? Can you please guide me to the example you are referring to?

[damend]

What abount

<operation binding>
<customSQL>
select * from where ...
</customSQL>

</operation binding>

[shkk15]

As I said, I do not want to lose out on pagination and other features. Isomorphic does claim that subqueries work in Datasource using <select><table><whereClause>. But I did not find any such example.

[Isomorphic]

Put the entire subquery into your <tableClause>. Our SQL generator is not aware that you have a subquery or that you want to generate one.