How SQL Server do that??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How SQL Server do that???

Author

Topic

ArthurC
Starting Member

7 Posts

Posted - 2003-08-01 : 12:23:20

Hi there,

I am having trouble to convert this Oracle syntax into SQL Server. The main problem is located in the IN clause where I have to bind TWO or more columns for the cascade select statement to filter out the return records. The following is a simplied version of the original statement. Is there anyone could help?

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)

Many thanks,
AC

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-01 : 13:06:37

SQL doesn't support that syntax. Use a correlated subquery.


select
    t1.*,
    t11.property_id
from
    pssl_t_revision_history t1
    inner join psl_reserve_entity t11
        on t1.project_id = t11.project_id and
           t1.entity_id = t11.entity_id
where
    t1.book_year = 2002 and
    t1.book_month = 12 and
    exists (
        select 1
        from
            psl_t_revision_history st1
            inner join pssl_t_reserver_entity st11
                on st1.project_id = st11.project_id and
                   st1.entity_id = st11.entity_id
        where
            st1.book_year = 2002 and
            st1.book_month = 12 and
            t1.status_flag = st1.status_flag and
            t1.time_stamp = st1.time_tamp)

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 15:10:16

Well that's true to an extent...

You could say

WHERE col1+col2 IN (SELECT col1+col2 FROM mytable)

But then you'd incurr a scan...

But why are you doing that in the first isn't what you have the same as:


    SELECT	  T1.*
		, T11.PROPERTY_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_T_RESERVE_ENTITY ST11
	ON	  ST1.PROJECT_ID = ST11.PROJECT_ID
       AND	  ST1.ENTITY_ID = ST11.ENTITY_ID
     WHERE	  T1.BOOK_YEAR = 2002
       AND 	  T1.BOOK_MONTH = 12

No?

Brett

8-)

SELECT POST=NewId()

ArthurC
Starting Member

7 Posts

Posted - 2003-08-01 : 15:22:54

Thanks guys 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 1
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

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 15:45:34

More likley that there is no such thing as rownum in SQL Server

I guess you could use TOP 2, but Idon't remember if it's the same

I think Oracle enumerates the data so you get consistent results with that..not sure though...gotta look it up..it's been a while.

Also, you've got an Order by in the subselect..why?

And A SELECT From SELECT

OOHHHH Hold on light bulb on...you need SELECT TOP 2

Give me a minute...

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 15:48:57

Be advised also TIMESTAMP has a VERY different meaning in SQL Server

No ther RDBMS I know of uses it the way SQL Server does..(It's not a time stamp at all...ok I hear Arnolds footsteps...)

Anyway try this:


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 TOP 2 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)
	) AS XXX

I still don't like the reundancy of it all though...

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 15:52:05

Doesn't this do the same thing?


SELECT TOP 2 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')
ORDER BY ST1.STATUS_FLAG desc, ST1.TIME_STAMP desc)

Brett

8-)

SELECT POST=NewId()

ArthurC
Starting Member

7 Posts

Posted - 2003-08-01 : 15:57:22

Yes, it returns the correct result. Million thanks, Brett

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 16:01:31

Your welcome...but which one?

Brett

8-)

SELECT POST=NewId()

ArthurC
Starting Member

7 Posts

Posted - 2003-08-01 : 16:15:35

Sorry, it will not work as I cannot binding the return value for TOP 2 only. 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. However, it is a good advise for me.

Thanks,
AC

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-01 : 16:18:46

OK Brett,

Fess up.

quote:

SELECT T1.*
, T11.PROPERTY_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_T_RESERVE_ENTITY ST11
ON ST1.PROJECT_ID = ST11.PROJECT_ID
AND ST1.ENTITY_ID = ST11.ENTITY_ID
WHERE T1.BOOK_YEAR = 2002
AND T1.BOOK_MONTH = 12

How are you formatting your queries with right justification? Is it careful spacing or a format command I havent seen?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 16:21:30

You just copy your code into this and put [ c o d e ] tags around it.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 16:21:30

Graz showed me...using code tages

with out the space in the tags

[ code ]
[ /code ]

Also edit my reply, and you should see them as well..

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 16:22:31

quote:
Originally posted by ArthurC

Sorry, it will not work as I cannot binding the return value for TOP 2 only. 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. However, it is a good advise for me.

Thanks,
AC

Sooooooooooooooooooooo true

[homer]doooooooooooooooooh[/homer]

Brett

8-)

SELECT POST=NewId()

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-01 : 18:44:42

[code]SELECT T1.*
, T11.PROPERTY_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_T_RESERVE_ENTITY ST11
ON ST1.PROJECT_ID = ST11.PROJECT_ID
AND ST1.ENTITY_ID = ST11.ENTITY_ID
WHERE T1.BOOK_YEAR = 2002
AND T1.BOOK_MONTH = 12[/code]

Looks the same to me.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 18:59:36

Sam,

Edit some code in Query Analyzer and put some tabs in it. Then put the code tags around it in a message here. Then preview it. You'll see the tabs are there. You probably just copied and pasted some code that didn't have the tabs in it.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 10:25:51

quote:

Originally posted by SamC

SELECT T1.*
, T11.PROPERTY_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_T_RESERVE_ENTITY ST11
ON ST1.PROJECT_ID = ST11.PROJECT_ID
AND ST1.ENTITY_ID = ST11.ENTITY_ID
WHERE T1.BOOK_YEAR = 2002
AND T1.BOOK_MONTH = 12

Looks the same to me.....

Sam, you still have to format the code...

In QA, format it with tabs or spaces, then wrap the code tags around it...

Go edit one of my responses to see...

Brett

8-)

SELECT POST=NewId()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources