Home » SQL & PL/SQL » SQL & PL/SQL » SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') (merged)
SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') (merged) [message #239003] Mon, 21 May 2007 03:32 Go to next message
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
get value SQL and Oracle [message #239023 is a reply to message #239003] Mon, 21 May 2007 05:15 Go to previous messageGo to next message
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')


Re: get value SQL and Oracle [message #239035 is a reply to message #239023] Mon, 21 May 2007 05:38 Go to previous messageGo to next message
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
Re: get value SQL and Oracle [message #239042 is a reply to message #239035] Mon, 21 May 2007 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think he just want we translate his code into PL/SQL.

Regards
Michel
SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #239406 is a reply to message #239003] Tue, 22 May 2007 05:01 Go to previous messageGo to next message
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
Re: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #239410 is a reply to message #239406] Tue, 22 May 2007 05:05 Go to previous messageGo to next message
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?
Re: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #605357 is a reply to message #239406] Wed, 08 January 2014 16:33 Go to previous messageGo to next message
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)
Re: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') [message #605361 is a reply to message #605357] Wed, 08 January 2014 18:33 Go to previous message
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.

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


Current Time: Thu Mar 28 11:24:34 CDT 2024