Missing expression - 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 2005 Forums

Transact-SQL (2005)

Missing expression

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 03:10:35

Please help me Thanks.

SELECT * FROM OPENQUERY(ServerLinkEODWMain,
'SELECT DISTINCT ABBR,ATB, ATU from DB WHERE ( ATB >= convert(varchar,''2007-05-01'') AND ATB < convert(varchar,''2007-06-01'') AND GATEWAY IN (''T'')
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8 ')

The error is

OLE DB provider "MSDAORA" for linked server "ServerLinkEODWMain" returned message "ORA-00936: missing expression
".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 04:04:15

since its oracle query try using text instead of varchar in convert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 04:15:21

I have edited the codes to look like this

SELECT * FROM OPENQUERY(ServerLinkEODWMain,
'select DISTINCT ABBR, ATB, ATU
from DB
WHERE (ATB >= convert(text,''2007-05-01'') AND ATB < convert(text,''2007-06-01'')) AND GATEWAY = ''T''')

the error it gave me was:

OLE DB provider "MSDAORA" for linked server "ServerLinkEODWMain" returned message "ORA-00904: "TEXT": invalid identifier
".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 05:03:10

for date isnt it enough to use to_date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 05:22:58

ohh i see. i have solved it... Thanks

But now i have another problem,

select * from
openquery(serverlinkeodwmain,
'select distinct left(convert(varchar,activity_dt,112),6) as year_mth
,right(convert(varchar,activity_dt,106),8) as year_mth_label
from OPS_CNTR order by year_mth desc')

i don't understand this code that was given to me.

this code is suppose to filter the month. but when i paste this codes to the management studio to try, the error returned is:

OLE DB provider "MSDAORA" for linked server "serverlinkeodwmain" returned message "ORA-00936: missing expression

please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 05:36:29

can you post it in some oracle forum as its an error thrown from oracle end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 05:41:08

this is oracle?? im sorry i didnt know. but i need to use these codes to create an SSRS in microsoft visual studio. how do i convert it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 05:46:10

using OPENQUERY you're connecting to oracle server here and so query given inside will be executed in oracle server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 20:43:59

i have posted this code:
select * from openquery(serverlinkeodwmain,'select distinct left(convert(varchar,atb,112),6) as year_mth
,right(convert(varchar,atb,106),8) as year_mth_label
from db order by year_mth desc')

on an oracle forum. but they say it is sql server related.

when i run the code, it says

OLE DB provider "MSDAORA" for linked server "serverlinkeodwmain" returned message "ORA-00936: missing expression

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 00:40:55

ORA-00936: missing expression
hence its a Oracle related error.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources