view problem - 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

SQL Server Development (2000)

view problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:23:31

Jetender Sambyal writes "I am sending you below mentioned query which works okey in oracle but giving error in sql server 7.0

Query:------------

CREATE VIEW PAY_VWYTDDETLHIST ( CMP_CODE,
FIN_YEAR, ECODE, YTDCODE, YTDTYPE,
YTDAMT ) AS
select CMP_CODE,FIN_YEAR,ECODE,YTDCODE,YTDTYPE,YTDAMT from pay_YTDDETL
UNION ALL
select CMP_CODE,FIN_YEAR,ECODE,RECSUBCODE,RECSUBTYPE,RECSUBAMT from pay_payrecsub where (cmp_code,fin_year,ecode,pmon,pyear) in ( select cmp_code,fin_year,ecode,pmon,pyear from pay_payhist where posted ='N')
UNION ALL
select CMP_CODE,FIN_YEAR,ECODE,ALLDEDCODE,ALLDEDTYPE,ALLDEDAMT+ALLDEDARR from pay_payALLDED where (cmp_code,fin_year,ecode,pmon,pyear) in ( select cmp_code,fin_year,ecode,pmon,pyear from pay_payhist where posted ='N')

------------------------------------------
error is :-----

Server: Msg 170, Level 15, State 1, Procedure PAY_VWYTDDETLHIST, Line 6
Line 6: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Procedure PAY_VWYTDDETLHIST, Line 8
Line 8: Incorrect syntax near ','.

----------------------

waiting for the positive reply."

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 00:11:26

the Codes in Bold are not supported in Sql Server
i mean
select * from table where
(column1,column2) in (select column1,column2 from anothertable)
will work in Oracle
but if you want to do similar thing in Sql server then you have to separetly check it

select * from table where
column1 in (select column1 from anothertable) or
column2 in (select column2 from anothertable)


CREATE VIEW PAY_VWYTDDETLHIST ( CMP_CODE, 
FIN_YEAR, ECODE, YTDCODE, YTDTYPE, 
YTDAMT ) AS 
select CMP_CODE,FIN_YEAR,ECODE,YTDCODE,YTDTYPE,YTDAMT from pay_YTDDETL
UNION ALL
select CMP_CODE,FIN_YEAR,ECODE,RECSUBCODE,RECSUBTYPE,RECSUBAMT from pay_payrecsub where (cmp_code,fin_year,ecode,pmon,pyear) in ( select cmp_code,fin_year,ecode,pmon,pyear from pay_payhist where posted ='N')
UNION ALL
select CMP_CODE,FIN_YEAR,ECODE,ALLDEDCODE,ALLDEDTYPE,ALLDEDAMT+ALLDEDARR from pay_payALLDED where (cmp_code,fin_year,ecode,pmon,pyear) in ( select cmp_code,fin_year,ecode,pmon,pyear from pay_payhist where posted ='N')

HTH

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-02 : 09:32:51

it would be (just guessing about what you want)
Also be careful about in clauses - if they contain a null then they never return anything.

select *
from table
where exists
(select *
from anothertable t2
where table.column1 = t2.column1
and table.column2 = t2.column2
and t2.field = 'N'
)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources