JDBC receiver SELECT Structure

[Former Member]

Hi All,

I faced this kind situation when i create the my JDBC SELECT structure statement.

source structure coming from Database.

My source XML structure:

<?xml version="1.0" encoding="utf-8" ?> 
- <ns:POCA0013_SCMDB_REQUESTTOKANLOG_MT xmlns:ns="urn:pg-com:POCA0013:sample">
   <row>
     <DATE>14 Mar 2010</DATE> 
     <PROJECT_ID>IND</PROJECT_ID> 
   </row>
   <row>
      <DATE>14 Mar 2010</DATE> 
      <PROJECT_ID>USA</PROJECT_ID> 
   </row>
   <row>
      <DATE>14 Mar 2010</DATE> 
      <PROJECT_ID>UK</PROJECT_ID> 
   </row>
   <row>
      <DATE>14 Mar 2010</DATE> 
      <PROJECT_ID>GER</PROJECT_ID> 
   </row>
</ns:POCA0013_SCMDB_REQUESTTOKANLOG_MT>

in above structure DATE is same for every row,that means DATE having only one record,in second field having PROJECT_ID is

different for every row,That means i have 4 project's ID.

my target system is also Databases system, my requirement is XI Select the records from target database system based upon condition

the 'Date' filed check to the 3 coloumns in the target table (my condition is Date1>=Date or Date2>=Date or Date3>=Date) and PROJECT_ID is check to the only one coloumn in the same table (conditon is PROJECTIONID = PROJECT_ID)

But PROJECT_ID have four values like IND,USA,UK,GER

Based upon this Source Data,How i can create the SELECT target structure?

i allready created my SELECT using normal condition.But in this situation i am not aware.

Kindly provide your valuble points about this structure.

Thank you very much.

Sateesh

Thank you very much.

Edited by: sateesh kumar .N on May 19, 2010 4:55 PM

[Senthilprakash1]

Hi Sathish,

Correct me if i am wrong. basically what you want to do is based on some conditions you want to fetch values from the table.

ideal way to achieve this is using stored procedure as mentioned in the thread.

but it can be done with receiver structure as well.

your req is something like this:

*SELECT * from table where Date1>=Date and Date2>=Date and Date3>=Date and PROJECTIONID = PROJECT_ID*

well above is not the exact syntax. i typed just to make my point. :).

well this kind of conditions can be specified in the structure itself by Using COMPARE OPERATORS in your structure (in key elements).

Structure you have to use will be something like below:

<StatementName1>

<dbTableName action=u201DSELECTu201D>

<table>realDbTableName</table>

<access>

<DATE/>

<PROJECTIONID/>

</access>

<key1>

<DATE compareOperation=u201DGETEQu201D>DATE1 field value</DATE>

<DATE compareOperation=u201DGETEQu201D>DATE2 field value</DATE>

<DATE compareOperation=u201DGETEQu201D>DATE3 field value</DATE>

<PROJECTIONID compareOperation=u201DEQu201D>Projectionid Value</PROJECTIONID>

</key1>

</dbTableName>

</StatementName1>

above statement is as equivalent to below SQL statement

SELCT DATE,PROJECTIONID from tablename where DATE>=DATE1Value and DATE>=DATE2 Value and DATE>=DATE3 Value and PROJECTIONID = PROJECTIONIDVALUE.

I am giving list of other operates which also can be used for selecting records from a table.

EQ :Equals (default value)

NEQ :Does not equal

LT :Less than

LTEQ :Less than or equal to

GT :Greater than

GTEQ :Greater than or equal to

LIKE :Like (strings). In the corresponding value, the SQL placeholders u201C%u201D or u201C_u201D can then also be used.

Regards,

Senthilprakash.

[Former Member]

Hello Senthil,

Thanks for your input.

My query is like this:

for example DATE value :20 MAY 2010

PROJECT_ID having 4 values like IND,USA,UK,GER

select * from  Table_name where 
 Date1>=to_date ('20 MAY 2010','DD MON YYYY') AND (PROJECTIONID='IND' OR PROJECTIONID='USA' OR PROJECTIONID='UK' OR PROJECTIONID='GER'  )  or 
 Date2 >= to_date ('20 MAY 2010','DD MON YYYY')AND (PROJECTIONID='IND' OR PROJECTIONID='USA' OR PROJECTIONID='UK' OR PROJECTIONID='GER'  )   or 
 Date3 >= to_date ('20 MAY 2010','DD MON YYYY') AND (PROJECTIONID='IND' OR PROJECTIONID='USA' OR PROJECTIONID='UK' OR PROJECTIONID='GER'  )

Generally if have one DATE value and one PROJECT_ID value then i am getting result in XI. But in case of Multiple values in PROJECT_ID how i can create the SELECT structure with using KEY element?

Please guide me.

Thank you so much.

Edited by: sateesh kumar .N on May 20, 2010 10:31 AM

[Senthilprakash1]

Hi Sateesh,

If i got it right, the structure for your above query should be something likethis.

<Key1>
<Date1 compareOperation=u201DGETEQu201D>to_date value</Date1>
<PROJECTIONID compareOperation=u201DNEQu201D>'IND'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'USA'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'UK'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'GER'</PROJECTIONID>
</key1>

<Key2>
<Date2 compareOperation=u201DGETEQu201D>to_date value</Date2>
<PROJECTIONID compareOperation=u201DNEQu201D>'IND'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'USA'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'UK'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'GER'</PROJECTIONID>
</key2>

<Key3>
<Date3 compareOperation=u201DGETEQu201D>to_date value</Date3>
<PROJECTIONID compareOperation=u201DNEQu201D>'IND'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'USA'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'UK'</PROJECTIONID>
<PROJECTIONID compareOperation=u201DNEQu201D>'GER'</PROJECTIONID>
</key3>

the output of the above structure should be like below.

select * from table where 

((date1 >= 'to_date' and PROJECTIONID >< IND and PROJECTIONID >< USA and PROJECTIONID >< UK and PROJECTIONID >< GER) OR 
(PROJECTIONID =IND) OR (PROJECTIONID =USA) OR (PROJECTIONID =UK) OR (PROJECTIONID =GER))

OR
((date2 >= 'to_date' and PROJECTIONID >< IND and PROJECTIONID >< USA and PROJECTIONID >< UK and PROJECTIONID >< GER) OR 
(PROJECTIONID =IND) OR (PROJECTIONID =USA) OR (PROJECTIONID =UK) OR (PROJECTIONID =GER))

OR
((date3 >= 'to_date' and PROJECTIONID >< IND and PROJECTIONID >< USA and PROJECTIONID >< UK and PROJECTIONID >< GER) OR 
(PROJECTIONID =IND) OR (PROJECTIONID =USA) OR (PROJECTIONID =UK) OR (PROJECTIONID =GER))

note: the above parameter in >< i used it for greater than or less than symbol. i was not able to use the exact notation for greater than or less than symbol here.

Also IND , USA, UK and GER should be inside quotes.

It took me more time in figuring out how to display the query error free than it took for me to write it :). few special notions are not visible and formatting is going for toss.

well at-least i am not able to figure it out in SDN :).

but above also might lead to performance hit. best is to use stored procedures.

Regards,

senthilprakash.

Edited by: senthilprakash selvaraj on May 20, 2010 4:28 PM

[Former Member]

Thank you for inputs,

I am going to follow the stored procedure in the case above condition.

Thank you so much.

Sateesh.

[VijayKonam]

You might have to depend on SPs for such requirements.

VJ

[Former Member]

Thank you Vijay for your Input,

Kindly provide me some more your valuble inputs about my conditon

Thank you very much.