OraFAQ Forum: SQL & PL/SQL » SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') (merged) [message #239003]

Mon, 21 May 2007 03:32

phuong
Messages: 5
Registered: May 2007

Junior Member

hello all,
I have a problem for addlinkserver from SQL server to Oracle
if i'm to foist value into StartDate --> run OK
but declare in SQL analyzer then does'st StartDate get value

Declare @ReportDate datetime, @StartDate datetime, @EndDate datetime, @DateCurr datetime, @DateCurr1 datetime
Declare @YYYY smallint,@MM smallint,@DD smallint
Set @DateCurr=getdate()-1
Set @StartDate=cast(datepart(yy,@DateCurr) as varchar)+'-'+cast(datepart(mm,@DateCurr) as varchar)+'-'+cast(datepart(dd,@DateCurr) as varchar)+cast(' 00:59:59' as varchar)

Set @DateCurr1=getdate()
Set @EndDate=cast(datepart(yy,@DateCurr1) as varchar)+'-'+cast(datepart(mm,@DateCurr1) as varchar)+'-'+cast(datepart(dd,@DateCurr1) as varchar)+cast(' 00:59:59' as varchar)

SELECT ACCOUNTCODE, BRAWDURATION, CALLEDCOST FROM OPENQUERY([127.0.0.1],
'SELECT ACCOUNTCODE, sum(BRAWDURATION)/60 as BRAWDURATION, sum(CALLEDCOST) as CALLEDCOST
FROM BILL.CDR
WHERE TO_CHAR(STARTDATE,''YYYY-MM-DD HH24:MI:SS'')
BETWEEN ''@StartDate'' AND ''@EndDate''
AND ACCOUNTCODE =''562111'' AND BILLSEC > 0 GROUP BY ACCOUNTCODE')

Help me now !
Thank you

Phuong

Report message to a moderator

get value SQL and Oracle [message #239023 is a reply to message #239003]

Mon, 21 May 2007 05:15

phuong
Messages: 5
Registered: May 2007

Junior Member

I want @StartDate, @EndDate get value but can'nt it
help me !
Can see coding below

Declare @ReportDate datetime, @StartDate datetime, @EndDate datetime, @DateCurr datetime, @DateCurr1 datetime
Declare @YYYY smallint,@MM smallint,@DD smallint
Set @DateCurr=getdate()-1
Set @StartDate=cast(datepart(yy,@DateCurr) as varchar)+'-'+cast(datepart(mm,@DateCurr) as varchar)+'-'+cast(datepart(dd,@DateCurr) as varchar)+cast(' 00:59:59' as varchar)

Set @DateCurr1=getdate()
Set @EndDate=cast(datepart(yy,@DateCurr1) as varchar)+'-'+cast(datepart(mm,@DateCurr1) as varchar)+'-'+cast(datepart(dd,@DateCurr1) as varchar)+cast(' 00:59:59' as varchar)
--select @StartDate, @EndDate

SELECT ACCOUNTCODE,BRAWDURATION,CALLEDCOST FROM OPENQUERY([127.0.0.1],
'SELECT ACCOUNTCODE, sum(BRAWDURATION)/60 as BRAWDURATION, sum(CALLEDCOST) as CALLEDCOST
FROM BILL.CDR
WHERE TO_CHAR(STARTDATE,''YYYY-MM-DD HH24:MI:SS'')
BETWEEN '+@StartDate+' AND '+@EndDate+'
AND ACCOUNTCODE =''111'' AND BILLSEC > 0 GROUP BY ACCOUNTCODE')

Report message to a moderator

Re: get value SQL and Oracle [message #239035 is a reply to message #239023]

Mon, 21 May 2007 05:38

bonker
Messages: 402
Registered: July 2005

Senior Member

The code looks like T-SQL and you should try to post this in SQL Server Forum.

This forum is dedicated to Oracle

Report message to a moderator

Re: get value SQL and Oracle [message #239042 is a reply to message #239035]

Mon, 21 May 2007 05:50

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think he just want we translate his code into PL/SQL.

Regards
Michel

Report message to a moderator

SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #239406 is a reply to message #239003]

Tue, 22 May 2007 05:01

phuong
Messages: 5
Registered: May 2007

Junior Member

Dear all,
I have script ON SQL Analyzer below:

Declare @cmd as varchar(8000)
Declare @StartDate Datetime, @EndDate Datetime
set @StartDate =getdate()-1
set @EndDate =getdate()

set @cmd ='SELECT ACCOUNTCODE,sum(BRAWDURATION)/60 AS RATE,sum(CALLEDCOST) AS FUND FROM BILL.CDR
WHERE TO_CHAR(STARTDATE,''YYYY-MM-DD HH24:MI:SS'')
BETWEEN '''+convert(varchar,@StartDate,120)+''' AND '''+convert(varchar,@EndDate,120)+'''
AND ACCOUNTCODE=''562111'' AND BILLSEC>0 GROUP BY ACCOUNTCODE'

SELECT ACCOUNTCODE,BRAWDURATION,CALLEDCOST FROM OPENQUERY([127.0.0.1],'@cmd')

But i can not get content by @cmd ('+@cmd+' or ''+@cmd+'') --> NOT OK

Thanks
Phuong

Report message to a moderator

Re: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #239410 is a reply to message #239406]

Tue, 22 May 2007 05:05

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You're aware that you are using SQL Server, and that this is an Oracle forum?

Report message to a moderator

Re: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #605357 is a reply to message #239406]

Wed, 08 January 2014 16:33

grgandik
Messages: 1
Registered: January 2014

Junior Member

Try this sample to see if it works. you can update the query after that accordingly.
=======
Declare @cmd varchar(8000)
SET @cmd = 'Select * from Dual '
SET @cmd = 'select * from openquery([127.0.0.1],' + char(39) + @cmd + char(39)+ ')'
exec (@cmd)

Report message to a moderator

Re: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #605361 is a reply to message #605357]

Wed, 08 January 2014 18:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

grgandik wrote on Wed, 08 January 2014 14:33

Try this sample to see if it works. you can update the query after that accordingly.
=======
Declare @cmd varchar(8000)
SET @cmd = 'Select * from Dual '
SET @cmd = 'select * from openquery([127.0.0.1],' + char(39) + @cmd + char(39)+ ')'
exec (@cmd)

First post is SQL Server code written to an Oracle forum onto a thread that is more than 6 years old.
I am duly underwhelmed.

Report message to a moderator

Previous Topic:	UPDATE only rows which match another table
Next Topic:	How to read UNCOMMITED data in Oracle?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 00:30:56 CST 2024