August 1, 2003 at 11:04 am
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
August 1, 2003 at 11:25 am
--...
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
August 1, 2003 at 1:26 pm
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
August 1, 2003 at 1:31 pm
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?
August 1, 2003 at 1:40 pm
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
August 1, 2003 at 1:53 pm
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...
August 1, 2003 at 2:34 pm
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
August 3, 2003 at 12:53 pm
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.
August 4, 2003 at 12:25 pm
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
August 5, 2003 at 8:26 am
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
August 5, 2003 at 9:24 am
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)
August 5, 2003 at 9:26 am
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)
August 5, 2003 at 9:30 am
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.
August 5, 2003 at 9:51 am
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