September 2, 2014 at 9:50 am
I'm trying to create a Character string so that I can execute dynamic SQL.
The date is going to change.
DECLARE @Select VARCHAR (50)
DECLARE @SQLQuery VARCHAR (500)
DECLARE @PreSelect CHAR (1)
DECLARE @CurrentDate Date
SET @SQLQuery = 'SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH,'
SET @PreSelect = '''
SELECT @Preselect AS PreSelect
If I try this statement which what I really want. I would like to include the Quote with the Select.:
SET @Select = ''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE >''
I get the following error:
Invalid object name 'RDB_DWH_ASSOCIATE_ENTITY'.
Any input would be greatly appreciated.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 9:57 am
Welsh Corgi (9/2/2014)
I'm trying to create a Character string so that I can execute dynamic SQL.The date is going to change.
DECLARE @Select VARCHAR (50)
DECLARE @SQLQuery VARCHAR (500)
DECLARE @PreSelect CHAR (1)
DECLARE @CurrentDate Date
SET @SQLQuery = 'SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH,'
SET @PreSelect = '''
SELECT @Preselect AS PreSelect
If I try this statement which what I really want. I would like to include the Quote with the Select.:
SET @Select = ''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE >''
I get the following error:
Invalid object name 'RDB_DWH_ASSOCIATE_ENTITY'.
Any input would be greatly appreciated.:-)
Quick though, use the char function, character code for apostrope is 39
😎
SELECT CHAR(39) + 'my string' + CHAR(39)
Returns
'my string'
September 2, 2014 at 10:03 am
Thanks the final result that I'm looking for is to return the record count. I did this before:
SET @SQLQuery = ''SELECT @cnt = COUNT(*) FROM '' + @SQLSchemaName + ''.''
+ @SQLTableName
SET @ParmDefinition = N''@cnt int OUTPUT''
EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;
SET @LinkedServerPre = ''LS_''
SET @OpenQueryPrefix = ''SELECT @cnt = OracleTableCount FROM OPENQUERY('' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = @OpenQueryPrefix
+ '', ''''SELECT COUNT(*) OracleTableCount FROM '' -- << and here
SET @OpenQuerySuffix = '''''')''
SET @OpenQuery = @OpenQueryPrefix + @OracleSchemaName + ''.'' + @OracleTableName + @OpenQuerySuffix
SET @ParmDefinition = N''@cnt int OUTPUT''
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
The Date Should be a variable.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 10:19 am
Try this, much easier to use replace than concatenation
😎
USE tempdb;
GO
DECLARE @PARAM_DATE_TOKEN NVARCHAR(10) = N'{{@PARAM_DATE}}';
DECLARE @PARAM_DATE_VALUE NVARCHAR(10) = N'2014-08-08';
DECLARE @STR_TEMPLATE NVARCHAR(MAX) = N'''SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH, ''''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE > = TO_DATE(''''{{@PARAM_DATE}}'''',''''YYYY.MM.DD'''') ORDER BY 1'''')''';
SELECT REPLACE(@STR_TEMPLATE,@PARAM_DATE_TOKEN,@PARAM_DATE_VALUE)
Returnsl
'SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH, ''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE > = TO_DATE(''2014-08-08ATE}}'',''YYYY.MM.DD'') ORDER BY 1'')'
September 2, 2014 at 10:35 am
EDIT: The following is wrong!
I did it this way:
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''',''YYYY.MM.DD'') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
September 2, 2014 at 10:38 am
If you're building your SQL statement into a variable, you can simply print it out to make sure you have it right. Like Erikur suggested, you'll be safer if you do a replace in your string variable. With dates in string variables, you have to be sure. Oh, and watch the format string in your TO_DATE function. Just like SQL Server, Oracle treats dates as dates, not as specially-formatted strings.
September 2, 2014 at 10:46 am
Lynn Pettis (9/2/2014)
I did it this way:
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''',''YYYY.MM.DD'') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
Sorry, the above is wrong, try this:
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
September 2, 2014 at 11:05 am
Lynn,
Sorry I missed your post. I have everything but the date.
This is the exact code that I have.
Thank you.
DECLARE @SQLQuery NVARCHAR(255)
DECLARE @OpenQuery NVARCHAR(4000)
DECLARE @OpenQueryPrefix NVARCHAR(4000)
DECLARE @OpenQuerySuffix NVARCHAR(200)
DECLARE @OracleRowCount INT
DECLARE @LinkedServerPre VARCHAR(200)
DECLARE @LinkedServerPost VARCHAR(200)
DECLARE @MyCnt INT;
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @OracleSchemaName NVARCHAR(60)
DECLARE @OracleTableName NVARCHAR (200)
DECLARE @CurrentDate Date
SET @CurrentDate= CONVERT(DATE, GETDATE());
SET @OracleSchemaName = 'RDB_DWH_POLICY'
SET @OracleTableName = 'COVERAGE_ASSOCIATE_ENTITY'
SET @LinkedServerPre = 'LS_'
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = @OpenQueryPrefix +'52'
SET @OpenQueryPrefix = @OpenQueryPrefix
+ ', ''SELECT COUNT(*) OracleTableCount FROM ' -- << and here
DECLARE @Where VARCHAR(200)
-- I need to add the following to the string.
-- WHERE CAE_RDB_ENTRY_DATE > = TO_DATE(''2014-08-08'',''YYYY.MM.DD'') ORDER BY 1')
-- I need to use the @CurrentDate variable as opposed to hard coding it
-- That is all that I need but the quotes are not comming out right.
SET @OpenQueryPrefix = @OpenQueryPrefix
+ @Where
SELECT @OpenQueryPrefix AS OpenQueryPrefix
SET @OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @OracleSchemaName + '.' + @OracleTableName + @OpenQuerySuffix
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
SELECT @OracleRowCount AS OracleRowCount;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 11:07 am
Look at Lynn's corrected query. The REPLACE you're looking for is right there.
September 2, 2014 at 11:09 am
Welsh Corgi (9/2/2014)
Lynn,Sorry I missed your post. I have everything but the date.
This is the exact code that I have.
Thank you.
DECLARE @SQLQuery NVARCHAR(255)
DECLARE @OpenQuery NVARCHAR(4000)
DECLARE @OpenQueryPrefix NVARCHAR(4000)
DECLARE @OpenQuerySuffix NVARCHAR(200)
DECLARE @OracleRowCount INT
DECLARE @LinkedServerPre VARCHAR(200)
DECLARE @LinkedServerPost VARCHAR(200)
DECLARE @MyCnt INT;
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @OracleSchemaName NVARCHAR(60)
DECLARE @OracleTableName NVARCHAR (200)
DECLARE @CurrentDate Date
SET @CurrentDate= CONVERT(DATE, GETDATE());
SET @OracleSchemaName = 'RDB_DWH_POLICY'
SET @OracleTableName = 'COVERAGE_ASSOCIATE_ENTITY'
SET @LinkedServerPre = 'LS_'
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = @OpenQueryPrefix +'52'
SET @OpenQueryPrefix = @OpenQueryPrefix
+ ', ''SELECT COUNT(*) OracleTableCount FROM ' -- << and here
DECLARE @Where VARCHAR(200)
-- I need to add the following to the string.
-- WHERE CAE_RDB_ENTRY_DATE > = TO_DATE(''2014-08-08'',''YYYY.MM.DD'') ORDER BY 1')
-- I need to use the @CurrentDate variable as opposed to hard coding it
-- That is all that I need but the quotes are not comming out right.
SET @OpenQueryPrefix = @OpenQueryPrefix
+ @Where
SELECT @OpenQueryPrefix AS OpenQueryPrefix
SET @OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @OracleSchemaName + '.' + @OracleTableName + @OpenQuerySuffix
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
SELECT @OracleRowCount AS OracleRowCount;
Well, if I comment the last two lines and insert a PRINT @OpenQuery, all I get is null.
Care to populate ALL your variables (where appropriate) with some test data??
Edit: Fix due to getting bit by the quote bug.
September 2, 2014 at 11:44 am
Sorry Lynn.
I had added the @Where Variable and it caused the variable to go to null.
DECLARE @SQLQuery NVARCHAR(255)
DECLARE @OpenQuery NVARCHAR(4000)
DECLARE @OpenQueryPrefix NVARCHAR(4000)
DECLARE @OpenQuerySuffix NVARCHAR(200)
DECLARE @OracleRowCount INT
DECLARE @LinkedServerPre VARCHAR(200)
DECLARE @LinkedServerPost VARCHAR(200)
DECLARE @MyCnt INT;
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @OracleSchemaName NVARCHAR(60)
DECLARE @OracleTableName NVARCHAR (200)
DECLARE @CurrentDate Date
SET @CurrentDate= CONVERT(DATE, GETDATE());
SET @OracleSchemaName = 'RDB_DWH_POLICY'
SET @OracleTableName = 'COVERAGE_ASSOCIATE_ENTITY'
SET @LinkedServerPre = 'LS_'
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SELECT @OpenQueryPrefix AS OpenQueryPrefix1
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SELECT @OpenQueryPrefix AS OpenQueryPrefix2
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SELECT @OpenQueryPrefix AS OpenQueryPrefix3
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = @OpenQueryPrefix +'52'
SELECT @OpenQueryPrefix AS OpenQueryPrefix4
SET @OpenQueryPrefix = @OpenQueryPrefix
+ ', ''SELECT COUNT(*) OracleTableCount FROM ' -- << and here
SELECT @OpenQueryPrefix AS OpenQueryPrefix4
DECLARE @Where VARCHAR(200)
-- I need to add the following to the string.
-- WHERE CAE_RDB_ENTRY_DATE > = TO_DATE(''2014-08-08'',''YYYY.MM.DD'') ORDER BY 1')
-- I need to use the @CurrentDate variable as opposed to hard coding it
-- That is all that I need but the quotes are not comming out right.
--SET @OpenQueryPrefix = @OpenQueryPrefix
-- + @Where
-- SELECT @OpenQueryPrefix AS OpenQueryPrefix5
SELECT @OpenQueryPrefix AS OpenQueryPrefix6
SET @OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @OracleSchemaName + '.' + @OracleTableName + @OpenQuerySuffix
SELECT @OpenQuery AS OpenQuery1
SET @ParmDefinition = N'@cnt int OUTPUT'
SELECT @OpenQueryPrefix AS OpenQueryPrefix7
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
SELECT @OracleRowCount AS OracleRowCount;
-- End Result
-- Use @CurrentDate
SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH_POLICY52, 'SELECT * FROM RDB_DWH_POLICY.COVERAGE_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE > TO_DATE(''2014-09-02'',''YYYY.MM.DD'') ORDER BY 1')
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 11:49 am
Good. Now all you have to do is look at the format mask of your TO_DATE function.
TO_DATE(''2014-09-02'',''YYYY.MM.DD'')
The date string doesn't match the format string.
September 2, 2014 at 11:57 am
Ed Wagner (9/2/2014)
Good. Now all you have to do is look at the format mask of your TO_DATE function.TO_DATE(''2014-09-02'',''YYYY.MM.DD'')
The date string doesn't match the format string.
Thanks.
I need TO_DATE(@CurrentDate,''YYYY.MM.DD'') to use a variable as opposed to a hard coded value.
I'm having trouble with the syntax.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 12:02 pm
Welsh Corgi (9/2/2014)
Ed Wagner (9/2/2014)
Good. Now all you have to do is look at the format mask of your TO_DATE function.TO_DATE(''2014-09-02'',''YYYY.MM.DD'')
The date string doesn't match the format string.
Thanks.
I need TO_DATE(@CurrentDate,''YYYY.MM.DD'') to use a variable as opposed to a hard coded value.
I'm having trouble with the syntax.
So this (what I had posted further up above doesn't help? If not why not?
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
September 2, 2014 at 12:13 pm
Lynn Pettis (9/2/2014)
Welsh Corgi (9/2/2014)
Ed Wagner (9/2/2014)
Good. Now all you have to do is look at the format mask of your TO_DATE function.TO_DATE(''2014-09-02'',''YYYY.MM.DD'')
The date string doesn't match the format string.
Thanks.
I need TO_DATE(@CurrentDate,''YYYY.MM.DD'') to use a variable as opposed to a hard coded value.
I'm having trouble with the syntax.
So this (what I had posted further up above doesn't help? If not why not?
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
Yes, Lynn, that's exactly right. It results in the format of the date matching the format mask.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply