cancel
Showing results for 
Search instead for 
Did you mean: 

Select query problem in JDBC sender adapter

Former Member
0 Kudos

Hello Experts,

We have a problem with PI sender adapter that PI has started to miss records in database some database records are missing and we are using the below selet query :

SELECT * FROM [database name].[dbo].[Material_Movement] WHERE [Process_Order_Number] = (Select TOP 1 [Process_Order_Number] FROM [database name].[dbo].[Material_Movement] WHERE ([PI_Read_Date] IS NULL AND [Movement_Type] = (SELECT TOP 1 [Movement_Type] FROM [database name].[dbo].[Material_Movement] WHERE [Transaction_Code] = 'xyz' AND [PI_Read_Date] IS NULL ORDER BY Created_Date ASC))) AND [Transaction_Code] = 'xyz' AND [Movement_Type] = (SELECT TOP 1 [Movement_Type] FROM [database name].[dbo].[Material_Movement] WHERE [Transaction_Code] = 'xyz' AND [PI_Read_Date] IS NULL ORDER BY Created_Date ASC) AND [PI_Read_Date] IS NULL ORDER BY [Transaction_ID] ASC

I am weak in select query could you please check and suggest how the query can be modified to avoid this issue .

Thanks,

Somenath

Accepted Solutions (1)

Accepted Solutions (1)

MichalKrawczyk
Active Contributor
0 Kudos

hi,

how can we help you if you just specified the sql query ?

descirbe what you want to get from which table

Regards,

Michal Krawczyk

Former Member
0 Kudos

Hi ,

After looking into These Query .. I found ...

Your Query Will run such Kind of scenario ..

1.) Movement Type will be fetched from from below Query

SELECT TOP 1 Movement_Type

FROM database name.dbo.Material_Movement

WHERE Transaction_Code = 'xyz'

AND PI_Read_Date IS NULL

ORDER BY Created_Date ASC

2.) on the basis of abovr fetched moment code . your Query will fetch 1 Process Order number

Select TOP 1 Process_Order_Number

FROM database name.dbo.Material_Movement

WHERE ( PI_Read_Date IS NULL

AND Movement_Type = Moment Type will be same as 1.

3.)

After Getting 1 and 2 . Query will fetch Data from table " dbo.Material_Movement "

On the basis of ..

Movement_Type = value from 1.

Process_order_type = value from 2.

Transaction_Code = 'xyz'

ORDER BY Transaction_ID ASC

So check Missed record Fullfill this Condition or not ....................

If not ... You will get why they are not picked by your given Query ...........

Hope it helps ..

regards

Prabhat Sharma.

Answers (1)

Answers (1)

stefan_grube
Active Contributor
0 Kudos

Test the query direct on database to see the results.

In my opinon you should not use nested select statements.

One select ... where field = ( select ...)

should be enough.