SQL Query

[former_member185881]

Hello All

Please help in SQL query

I have a source SQL table with 3 fields Material_No, Region and FLAG.

Material_No    Region        FLAG

1111                 NEU            N

2222                 LATIN         N

3333                 NEU            N

4444                 LATIN         N

Now I want SQL query in JDBC sender channel which can pick records with respect to Region and FLAG = N.

for example:

in first call it should pick all records of Region = NEU and FLAG = N

and

in second call it should pick all records of Region = LATIN and FLAG = N

like NEU and LATIN other Regions are also there. This is just an example.

In result of SQL query only 1 kind of Region should come it should not combine any other value.

If NEU is coming then all records should be of NEU and not LATIN.

Is this possible using SELECT query?

Thanks

Dheeraj Kumar

[siddhardha_dnk]

Hi Dheeraj,

We can do this with the help of update query.

Before discussing on that, Let me know whether the no. of values of Region field is definite or not.

Or Else....

Just ask DB team to write a piece of code in SQL on your requirement. And you can pick the data based on the said SQL code by execute it through Sender JDBC Channel.

This will resolve your query.

Regards,

Siddhardha Datlla.

[former_member185881]

Hello Siddhardha

Region field is not definite. Records can be of any number.

How we can achieve this using update query kindly explain.

Or Else....

Just ask DB team to write a piece of code in SQL on your requirement. And you can pick the data based on the said SQL code by execute it through Sender JDBC Channel.

By above statement you mean to say that they can write stored procedure for this. Is this can be achieved by Stored Procedure?

Thanks

Dheeraj Kumar

[suman_saha]

Hi Dheeraj,

Have you tried order by Region,FLAG in your query??

Regards,

Suman

[Former Member]

Hi Dheeraj,

I can say this is not possible with single SELECT query, but this requirement can be done using ccBPM.  Write SELECT DISTINCT(Region) from Sql table where Region = NEU and FLAG = N in Sender communication channel and write update query with intermediate FLAG status. At mapping level count the distinct 'Regions'. Based on count & region call the each region data from SQL table using ccBPM. During Selection of each region data from SQL data base using ccBPM update FLAG with final status.

Regards,

Krupa

[Former Member]

Hi Dheeraj

I don't think you can write such select query in JDBC sender adapter. If you want to separate the data based on each region value, then you have to separate the JDBC sender channel as well.

So JDBC channel 1 will have select query like this

Select * from table where Region = NEU and FLAG = N

JDBC channel 2 will have select query

Select * from table where Region =  LATIN and FLAG = N

Unfortunately we can not form the dynamic select query in sender jdbc adapter.

You can also think about store procedure. Check this

Thanks,

Indrajit