Hey Dima,
Thanks for replying.
But i don't think i understood your answer totally. As i said, it is a
native SQL with multiple joins and "if exists" clauses.
SELECT* et.** FROM external_trade et, exch_tools_trade
ett,external_trade_state ets WHERE (et.external_trade_system_oid IN (1))
AND (et.external_trade_source_oid in (1)) AND (et.external_trade_status_oid
IN (1, 2, 3, 4)) AND (et.external_trade_state_oid IN (1, 2, 3, 4)) AND
(ett.buyer_account NOT IN ('TC123')) AND (ett.creation_date >=
('2016-01-01')) AND (ett.creation_date <= ('2016-12-21')) AND NOT EXISTS
(SELECT 1 FROM exch_tools_trade ett1 JOIN external_trade et1 ON et1.oid =
ett1.external_trade_oid JOIN external_trade_state ets1 ON
et1.external_trade_state_oid = ets1.oid WHERE ett.commodity =
ett1.commodity AND ett.exch_tools_trade_num = ett1.exch_tools_trade_num
AND ett.trading_period = ett1.trading_period AND ett.buyer_account =
ett1.buyer_account AND
convert(datetime,convert(varchar,ett.creation_date,109)) =
convert(datetime,convert(varchar,ett1.creation_date,109)) AND
ISNULL(ett.call_put,'NULL') = ISNULL(ett1.call_put,'NULL') AND
ISNULL(ett.strike_price,0) = ISNULL(ett1.strike_price,0) AND
(((ets1.external_trade_state_name = 'Update' or
ets1.external_trade_state_name = 'Delete') AND
(ets.external_trade_state_name = 'Add')) OR (ets1.external_trade_state_name
= 'Delete' AND ets.external_trade_state_name = 'Update'))) AND
ets.external_trade_state_name != 'Delete' AND et.oid =
ett.external_trade_oid AND et.external_trade_state_oid = ets.oid
So to fire that i think i need SQLTemplate. If i try what you suggessted
i'm getting the below problem
*Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
[v.4.0.M4.ce40fc0 Oct 26 2016 22:22:58] Set up Object entity or use
rowFetchingQuery*
Thanks,
Saravana Kumar.M
On Wed, Dec 21, 2016 at 7:06 PM, Dzmitry Kazimirchyk <dkazimirc...@gmail.com
> wrote:
> Hello Kumar,
>
> Currently it is not possible to extract a query defined in the datamap as
> an SQLTemplate query instance.
>
> MappedSelect is a separate type of query which represents all the query
> types that can be defined in the datamap and the idea behind it is that
> after defining your query in the Modeler, you then don't have to worry
> about which type it is when calling it and just specify the necessary
> parameters for every select you make. E.g.:
>
> List<Artist> artists = MappedSelect.query("SelectArtists", Artist.class)
> .param("name", "artist1")
> .select(context);
>
> So in essence you can use MappedSelect instance you get the way you would
> use any other Cayenne query instance.
>
> Hope this helps.
>
>
> Dima
>
>
>
> On 12/21/16 1:09 PM, Kumar wrote:
>
>> Hey,
>>
>> I have defined few queries in the datamap.xml file and and accessing it
>> using MappedSelect.query("queryName")
>>
>> My quick question is, I want to define a native sql query in the
>> datamap.xml file and to access in the code. Currently MappedSelect.query()
>> is returning a MappedSelect which i cannot use with SQLTemplate.
>>
>> Thanks in Advance
>> Saravana Kumar.M
>>
>>
>
