Tough sql statement conversion

  • Hi there,

    I am having trouble to converse a complex Oracle statement into SQL Server. The main problem is how do I bind TWO or more columns in the where clause using IN to filter out return records from the subquery? Here is a simplified statement:

    SELECT T1.*, T11.PROPERTY_ID

    FROM PSSL_T_REVISION_HISTORY T1,

    PSSL_RESERVE_ENTITY T11

    WHERE T1.PROJECT_ID = T11.PROJECT_ID

    AND T1.ENTITY_ID = T11.ENTITY_ID

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND (T1.STATUS_FLAG, T1.TIME_STAMP) IN (SELECT ST1.STATUS_FLAG, ST1.TIME_STAMP

    FROM PSSL_T_REVISION_HISTORY ST1,

    PSSL_T_RESERVE_ENTITY ST11

    WHERE ST1.PROJECT_ID = ST11.PROJECT_ID

    AND ST1.ENTITY_ID = ST11.ENTITY_ID

    AND ST1.BOOK_YEAR = 2002

    AND ST1.BOOK_MONTH = 12)

    Can anyone could help?

    Many thanks,

    AC

  • 
    
    --...
    AND EXISTS
    (SELECT *
    FROM PSSL_T_REVISION_HISTORY ST1,
    PSSL_T_RESERVE_ENTITY ST11
    WHERE ST1.PROJECT_ID = ST11.PROJECT_ID
    AND ST1.ENTITY_ID = ST11.ENTITY_ID
    AND ST1.BOOK_YEAR = 2002
    AND ST1.BOOK_MONTH = 12
    AND ST1.STATUS_FLAG = T1.STATUS_FLAG
    AND ST1.TIME_STAMP = T1.TIME_STAMP)

    Edited by - jpipes on 08/01/2003 11:25:21 AM

  • Thanks jpipes but it is not returning the correct result. May be I should include the exact Oracle statement for a reference.

    -- Oracle:

    SELECT T1.*,T11.PROPERTY_ID,T11.FACILITY_ID,T11.GROUP_ID,T11.WELL_UWI,T11.RESERVECAT_ID

    FROM PSSL_T_REVISION_HISTORY T1,

    PSSL_RESERVE_ENTITY T11,

    PSSL_REVISION_CATEGORY T2

    WHERE T1.PROJECT_ID = T11.PROJECT_ID

    AND T1.ENTITY_ID = T11.ENTITY_ID

    AND T1.REVISIONCAT_ID = T2.REVISIONCAT_ID

    AND T1.PROJECT_ID = '0'

    AND T11.PROPERTY_ID = '1002296'

    AND (T11.GROUP_ID = '0' OR T11.GROUP_ID IS NULL)

    AND (T11.WELL_UWI = '00000000000025364' OR T11.WELL_UWI IS NULL)

    AND T11.RESERVECAT_ID = '3'

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND T1.STATUS_FLAG <> '86'

    AND T2.REVISIONCAT_TYPE_ID IN ('14')

    AND (T1.STATUS_FLAG,T1.TIME_STAMP)

    IN (SELECT STATUS_FLAG, TIME_STAMP

    FROM (SELECT ST1.STATUS_FLAG,ST1.TIME_STAMP

    FROM PSSL_T_REVISION_HISTORY ST1,

    PSSL_T_RESERVE_ENTITY ST11,

    PSSL_REVISION_CATEGORY ST2

    WHERE ST1.PROJECT_ID = ST11.PROJECT_ID

    AND ST1.ENTITY_ID = ST11.ENTITY_ID

    AND ST1.REVISIONCAT_ID = ST2.REVISIONCAT_ID

    AND ST1.PROJECT_ID = '0'

    AND ST11.PROPERTY_ID = '1002296'

    AND (ST11.GROUP_ID = '0' OR ST11.GROUP_ID IS NULL)

    AND (ST11.WELL_UWI = '00000000000025364' OR ST11.WELL_UWI IS NULL)

    AND ST11.RESERVECAT_ID = '3'

    AND ST1.BOOK_YEAR = 2002

    AND ST1.BOOK_MONTH = 12

    AND ST1.STATUS_FLAG <> '86'

    AND ST2.REVISIONCAT_TYPE_ID IN ('14')

    order by ST1.STATUS_FLAG desc, ST1.TIME_STAMP desc)

    WHERE rownum<2)

    I came up with the SQL statement like this but not returning exactly what is expected:

    -- SQL:

    SELECT T1.*, T11.property_id, T11.FACILITY_ID,T11.GROUP_ID,T11.WELL_UWI,T11.RESERVECAT_ID

    FROM PSSL_T_REVISION_HISTORY T1

    INNER JOIN PSSL_RESERVE_ENTITY T11

    ON T1.PROJECT_ID = T11.PROJECT_ID AND T1.ENTITY_ID = T11.ENTITY_ID

    INNER JOIN PSSL_REVISION_CATEGORY T2

    ON T1.REVISIONCAT_ID = T2.REVISIONCAT_ID

    WHERE T1.PROJECT_ID = '0'

    AND T11.PROPERTY_ID = '1002296'

    AND (T11.GROUP_ID = '0' OR T11.GROUP_ID IS NULL)

    AND (T11.WELL_UWI = '00000000000025364' OR T11.WELL_UWI IS NULL)

    AND T11.RESERVECAT_ID = '3'

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND T1.STATUS_FLAG <> '86'

    AND T2.REVISIONCAT_TYPE_ID IN ('14')

    AND EXISTS (SELECT *

    FROM PSSL_T_REVISION_HISTORY ST1

    INNER JOIN PSSL_RESERVE_ENTITY T11

    ON T1.PROJECT_ID = T11.PROJECT_ID AND T1.ENTITY_ID = T11.ENTITY_ID

    INNER JOIN PSSL_REVISION_CATEGORY T2

    ON T1.REVISIONCAT_ID = T2.REVISIONCAT_ID

    WHERE T1.PROJECT_ID = '0'

    AND T11.PROPERTY_ID = '1002296'

    AND (T11.GROUP_ID = '0' OR T11.GROUP_ID IS NULL)

    AND (T11.WELL_UWI = '00000000000025364' OR T11.WELL_UWI IS NULL)

    AND T11.RESERVECAT_ID = '3'

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND T1.STATUS_FLAG <> '86'

    AND T2.REVISIONCAT_TYPE_ID IN ('14')

    AND T1.STATUS_FLAG = ST1.STATUS_FLAG AND T1.TIME_STAMP = ST1.TIME_STAMP)

    Anyone could help for this challenging problem?

    AC

  • quote:


    I came up with the SQL statement like this but not returning exactly what is expected:


    What were you expecting vs. what were your results?

  • For this particular keys, the expected result should be 2 rows (NOT 4 rows). The Oracle sql statement will further filter out by picking the max time_stamp. Therefore, it finally returned 2 rows. Does this make sense?

    AC

  • But you have a WHERE rownum < 2 in your Oracle statement and no such similar filter in your SQL statement. Either put a JOIN to a MAX(som_time_stamp) derived dataset, or use TOP 2 with an ORDER BY desc...

  • Yes, you are right. However, I cannot binding the return value using TOP 2. This statement expected to return 2 rows because of the current data set and keys provided to the database engine. If keys or dataset changed, the return values could be more or less. I just tried to use Max (time_stamp) but it is not working either. Oh boy!!!

    AC

  • AND (T1.STATUS_FLAG + T1.TIME_STAMP) IN (SELECT ST1.STATUS_FLAG + ST1.TIME_STAMP

    With correct convert functions around the columns to convert them to varchar.

  • Yo, ArthurC. You can use this...it's a derived table with a "Group by" on it. The derived table only returns groups of data having a row count >= 2, which I think is what you wanted?

    SELECT T1.*,T11.PROPERTY_ID,T11.FACILITY_ID,T11.GROUP_ID,T11.WELL_UWI,T11.RESERVECAT_ID

    FROM PSSL_T_REVISION_HISTORY T1

    JOIN PSSL_RESERVE_ENTITY T11 on T1.PROJECT_ID = T11.PROJECT_ID

    and T1.ENTITY_ID = T11.ENTITY_ID

    JOIN PSSL_REVISION_CATEGORY T2 on T1.REVISIONCAT_ID = T2.REVISIONCAT_ID

    JOIN

    (

    SELECT ST1.STATUS_FLAG,ST1.TIME_STAMP

    FROM PSSL_T_REVISION_HISTORY ST1

    JOIN PSSL_T_RESERVE_ENTITY ST11 on ST1.PROJECT_ID = ST11.PROJECT_ID

    AND ST1.ENTITY_ID = ST11.ENTITY_ID

    JOINPSSL_REVISION_CATEGORY ST2 on ST1.REVISIONCAT_ID = ST2.REVISIONCAT_ID

    WHERE

    ST1.PROJECT_ID = '0'

    AND ST11.PROPERTY_ID = '1002296'

    AND (ST11.GROUP_ID = '0' OR ST11.GROUP_ID IS NULL)

    AND (ST11.WELL_UWI = '00000000000025364' OR ST11.WELL_UWI IS NULL)

    AND ST11.RESERVECAT_ID = '3'

    AND ST1.BOOK_YEAR = 2002

    AND ST1.BOOK_MONTH = 12

    AND ST1.STATUS_FLAG <> '86'

    AND ST2.REVISIONCAT_TYPE_ID IN ('14')

    Group by ST1.STATUS_FLAG,ST1.TIME_STAMP--This groups the date by these columns

    Having count(*) >= 2--This counts the grouped data and only returns groups with two or more rows

    ) ST20 on T1.STATUS_FLAG = ST20.STATUS_FLAG and T1.TIME_STAMP = ST20.TIME_STAMP

    WHERE

    T1.PROJECT_ID = '0'

    AND T11.PROPERTY_ID = '1002296'

    AND (T11.GROUP_ID = '0' OR T11.GROUP_ID IS NULL)

    AND (T11.WELL_UWI = '00000000000025364' OR T11.WELL_UWI IS NULL)

    AND T11.RESERVECAT_ID = '3'

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND T1.STATUS_FLAG <> '86'

    AND T2.REVISIONCAT_TYPE_ID IN ('14')

    Signature is NULL

  • Many thanks for you guys response. I tried your suggestions but they are not returning what I am looking for either; they all returning 4 rows but I am expecting 2 rows for each group. However, those suggestion gave me a good lesson.

    Many many thanks again,

    AC

  • Can you try the following and see if it returns the expected result set

    SELECT T1.*, T11.PROPERTY_ID

    FROM PSSL_T_REVISION_HISTORY T1

    , PSSL_RESERVE_ENTITY T11

    WHERE T1.PROJECT_ID = T11.PROJECT_ID

    AND T1.ENTITY_ID = T11.ENTITY_ID

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND EXISTS (SELECT 'x'

    FROM PSSL_T_REVISION_HISTORY ST1

    , PSSL_T_RESERVE_ENTITY ST11

    WHERE ST1.PROJECT_ID = ST11.PROJECT_ID

    AND ST1.ENTITY_ID = ST11.ENTITY_ID

    AND ST1.BOOK_YEAR = 2002

    AND ST1.BOOK_MONTH = 12

    AND ST1.STATUS_FLAG = T1.STATUS_FLAG

    AND ST1.TIME_STAMP = T1.TIME_STAMP)

  • Can you try the following and see if it returns the expected result set

    SELECT T1.*, T11.PROPERTY_ID

    FROM PSSL_T_REVISION_HISTORY T1

    , PSSL_RESERVE_ENTITY T11

    WHERE T1.PROJECT_ID = T11.PROJECT_ID

    AND T1.ENTITY_ID = T11.ENTITY_ID

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND EXISTS (SELECT 'x'

    FROM PSSL_T_REVISION_HISTORY ST1

    , PSSL_T_RESERVE_ENTITY ST11

    WHERE ST1.PROJECT_ID = ST11.PROJECT_ID

    AND ST1.ENTITY_ID = ST11.ENTITY_ID

    AND ST1.BOOK_YEAR = 2002

    AND ST1.BOOK_MONTH = 12

    AND ST1.STATUS_FLAG = T1.STATUS_FLAG

    AND ST1.TIME_STAMP = T1.TIME_STAMP)

  • Sorry Mathuar, your suggestion returned more than 49 thousands rows of records. Thanks for your response anyhow. I am still trying to be creative for finding a solution.

  • Thank you for all of you who response to me or trying to help me for a solution. I applied the suggestion by Tom Brattin and reconstruct my sql statement. Now it is returning 2 rows. Here is the final sql statement that I would like to share with you guys.

    -- SET CONCAT_NULL_YIELDS_NULL OFF

    -- GO

    SELECT T1.*,T11.PROPERTY_ID,T11.FACILITY_ID,T11.GROUP_ID,T11.WELL_UWI,T11.RESERVECAT_ID

    FROM PSSL_T_REVISION_HISTORY T1,

    PSSL_RESERVE_ENTITY T11,

    PSSL_REVISION_CATEGORY T2

    WHERE T1.PROJECT_ID = T11.PROJECT_ID

    AND T1.ENTITY_ID = T11.ENTITY_ID

    AND T1.REVISIONCAT_ID = T2.REVISIONCAT_ID

    AND T1.PROJECT_ID = '0'

    AND T11.PROPERTY_ID = '1002296'

    AND (T11.GROUP_ID = '0' OR T11.GROUP_ID IS NULL)

    AND (T11.WELL_UWI = '00000000000025364' OR T11.WELL_UWI IS NULL)

    AND T11.RESERVECAT_ID = '3'

    AND T1.BOOK_YEAR = 2002

    AND T1.BOOK_MONTH = 12

    AND T1.STATUS_FLAG <> '86'

    AND T2.REVISIONCAT_TYPE_ID IN ('14')

    AND (T1.PROJECT_ID + CAST(T1.ENTITY_ID AS VARCHAR(10)) + CAST(T1.SERIES_NAME_ID AS VARCHAR(10)) + T1.INTEREST_ID + T1.REVISIONCAT_ID + CAST(T1.BOOK_YEAR AS VARCHAR(4)) + CAST(T1.BOOK_MONTH AS VARCHAR(2)) + T1.TRANSACTION_ID + T1.PARTICIPANT_ID + CAST(T1.TIME_STAMP AS VARCHAR(30)))

    IN (SELECT S1.PROJECT_ID + CAST(S1.ENTITY_ID AS VARCHAR(10)) + CAST(S1.SERIES_NAME_ID AS VARCHAR(10)) + S1.INTEREST_ID + S1.REVISIONCAT_ID + CAST(S1.BOOK_YEAR AS VARCHAR(4)) + CAST(S1.BOOK_MONTH AS VARCHAR(2)) + S1.TRANSACTION_ID + S1.PARTICIPANT_ID + CAST(MAX(S1.TIME_STAMP) AS VARCHAR(30))

    FROM PSSL_T_REVISION_HISTORY S1,

    PSSL_T_RESERVE_ENTITY S11,

    PSSL_REVISION_CATEGORY S12

    WHERE S1.PROJECT_ID = S11.PROJECT_ID

    AND S1.ENTITY_ID = S11.ENTITY_ID

    AND S1.REVISIONCAT_ID = S12.REVISIONCAT_ID

    AND S1.PROJECT_ID = '0'

    AND S11.PROPERTY_ID = '1002296'

    AND (S11.GROUP_ID = '0' OR S11.GROUP_ID IS NULL)

    AND (S11.WELL_UWI = '00000000000025364' OR S11.WELL_UWI IS NULL)

    AND S11.RESERVECAT_ID = '3'

    AND S1.BOOK_YEAR = 2002

    AND S1.BOOK_MONTH = 12

    AND S1.STATUS_FLAG <> '86'

    AND S12.REVISIONCAT_TYPE_ID IN ('14')

    AND (S1.PROJECT_ID + CAST(S1.ENTITY_ID AS VARCHAR(10)) + CAST(S1.SERIES_NAME_ID AS VARCHAR(10)) + S1.INTEREST_ID + S1.REVISIONCAT_ID + CAST(S1.BOOK_YEAR AS VARCHAR(4)) + CAST(S1.BOOK_MONTH AS VARCHAR(2)) + S1.TRANSACTION_ID + S1.PARTICIPANT_ID + S1.STATUS_FLAG)

    IN (SELECT S2.PROJECT_ID + CAST(S2.ENTITY_ID AS VARCHAR(10)) + CAST(S2.SERIES_NAME_ID AS VARCHAR(10)) + S2.INTEREST_ID + S2.REVISIONCAT_ID + CAST(S2.BOOK_YEAR AS VARCHAR(4)) + CAST(S2.BOOK_MONTH AS VARCHAR(2)) + S2.TRANSACTION_ID + S2.PARTICIPANT_ID + MAX(S2.STATUS_FLAG)

    FROM PSSL_T_REVISION_HISTORY S2,

    PSSL_T_RESERVE_ENTITY S21,

    PSSL_REVISION_CATEGORY S22

    WHERE S2.PROJECT_ID = S21.PROJECT_ID

    AND S2.ENTITY_ID = S21.ENTITY_ID

    AND S2.REVISIONCAT_ID = S22.REVISIONCAT_ID

    AND S2.PROJECT_ID = '0'

    AND S21.PROPERTY_ID = '1002296'

    AND (S21.GROUP_ID = '0' OR S21.GROUP_ID IS NULL)

    AND (S21.WELL_UWI = '00000000000025364' OR S21.WELL_UWI IS NULL)

    AND S21.RESERVECAT_ID = '3'

    AND S2.BOOK_YEAR = 2002

    AND S2.BOOK_MONTH = 12

    AND S2.STATUS_FLAG <> '86'

    AND S22.REVISIONCAT_TYPE_ID IN ('14')

    GROUP BY S2.PROJECT_ID,S2.ENTITY_ID,S2.SERIES_NAME_ID,S2.INTEREST_ID,S2.REVISIONCAT_ID,S2.BOOK_YEAR,S2.BOOK_MONTH,S2.TRANSACTION_ID,S2.PARTICIPANT_ID)

    GROUP BY S1.PROJECT_ID,S1.ENTITY_ID,S1.SERIES_NAME_ID,S1.INTEREST_ID,S1.REVISIONCAT_ID,S1.BOOK_YEAR,S1.BOOK_MONTH,S1.TRANSACTION_ID,S1.PARTICIPANT_ID)

    Many thanks for your contribution.

    AC

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply