Skip to Content
avatar image
Former Member

Select query problem in JDBC sender adapter

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 05, 2011 at 10:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Jan 05, 2011 at 11:07 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded