Я пытаюсь выделить две даты во второй последней строке приведенного ниже кода с помощью SQL-запроса в R. В любом случае это возможно?
myConn <- odbcDriverConnect(connection = "driver = {SQL Server};server=SLSQL398_AGLIS\\SLSQL398;database=PortfolioAnalytics;trusted_connection=true")
data<- sqlFetch(myConn, "Data_Factset.FundChar", colnames = TRUE)
SQLCommand<- data.frame(sqlQuery(myConn, "SELECT fc.[FactSet Fund Code], fc.FactsetDate, fc.[Asset Class], fc.[Fund Manager], d.Id AS DeskID, d.[Name] AS DeskName, c.ClosingDate, m.NumericGrossPerformanceTarget, fc.[% Factor Risk], fc.[Predicted Tracking Error], fc.[Portfolio Predicted Beta], fc.[Port. Ending Active Share]
FROM [PortfolioAnalytics].[Data_Factset].[FundChar] fc
LEFT OUTER JOIN Data_Axioma.FactSetAccount fsa ON fc.FactsetAccountCode = REPLACE(fsa.Code, '_', '')
LEFT OUTER JOIN dbo.Portfolio p ON fsa.PortfolioId = p.Id
LEFT OUTER JOIN dbo.Mandate m ON p.UnderlyingId = m.id
INNER JOIN dbo.Mandate c ON p.UnderlyingId = c.id
LEFT OUTER JOIN dbo.Desk d ON m.DeskId = d.Id
WHERE IncludesCash = 1
AND [FactSet Fund Code] IS NOT NULL
AND [Fund Manager] IS NOT NULL
AND [FactSet Fund Code] <> 'TSNA_US'
AND [Asset Class] IS NOT NULL
AND FactsetDate IN ('20180531', '20180228')
ORDER BY [Predicted Tracking Error] DESC"))
Это моя попытка, но я получаю неожиданную ошибку символа:
d<-20180531
d2<-20180228
myConn <- odbcDriverConnect(connection = "driver = {SQL Server};server=SLSQL398_AGLIS\\SLSQL398;database=PortfolioAnalytics;trusted_connection=true")
data<- sqlFetch(myConn, "Data_Factset.FundChar", colnames = TRUE)
SQLCommand<- data.frame(sqlQuery(myConn, "SELECT fc.[FactSet Fund Code], fc.FactsetDate, fc.[Asset Class], fc.[Fund Manager], d.Id AS DeskID, d.[Name] AS DeskName, c.ClosingDate, m.NumericGrossPerformanceTarget, fc.[% Factor Risk], fc.[Predicted Tracking Error], fc.[Portfolio Predicted Beta], fc.[Port. Ending Active Share]
FROM [PortfolioAnalytics].[Data_Factset].[FundChar] fc
LEFT OUTER JOIN Data_Axioma.FactSetAccount fsa ON fc.FactsetAccountCode = REPLACE(fsa.Code, '_', '')
LEFT OUTER JOIN dbo.Portfolio p ON fsa.PortfolioId = p.Id
LEFT OUTER JOIN dbo.Mandate m ON p.UnderlyingId = m.id
INNER JOIN dbo.Mandate c ON p.UnderlyingId = c.id
LEFT OUTER JOIN dbo.Desk d ON m.DeskId = d.Id
WHERE IncludesCash = 1
AND [FactSet Fund Code] IS NOT NULL
AND [Fund Manager] IS NOT NULL
AND [FactSet Fund Code] <> 'TSNA_US'
AND [Asset Class] IS NOT NULL
AND FactsetDate IN ('"d"', '"d2"')
ORDER BY [Predicted Tracking Error] DESC"))
}
См. Обновленный вопрос - по сути, я пытаюсь вывести даты из запроса.


Вставки «d» и «d2», вероятно, не работают.
Попробуйте построить строку запроса SQL вне команды sqlQuery().
Попробуй это:
d <- "20180531"
d2 <- "20180228"
my_query <- paste0("SELECT fc.[FactSet Fund Code], fc.FactsetDate, fc.[Asset Class], fc.[Fund Manager], d.Id AS DeskID, d.[Name] AS DeskName, c.ClosingDate, m.NumericGrossPerformanceTarget, fc.[% Factor Risk], fc.[Predicted Tracking Error], fc.[Portfolio Predicted Beta], fc.[Port. Ending Active Share]
FROM [PortfolioAnalytics].[Data_Factset].[FundChar] fc
LEFT OUTER JOIN Data_Axioma.FactSetAccount fsa ON fc.FactsetAccountCode = REPLACE(fsa.Code, '_', '')
LEFT OUTER JOIN dbo.Portfolio p ON fsa.PortfolioId = p.Id
LEFT OUTER JOIN dbo.Mandate m ON p.UnderlyingId = m.id
INNER JOIN dbo.Mandate c ON p.UnderlyingId = c.id
LEFT OUTER JOIN dbo.Desk d ON m.DeskId = d.Id
WHERE IncludesCash = 1
AND [FactSet Fund Code] IS NOT NULL
AND [Fund Manager] IS NOT NULL
AND [FactSet Fund Code] <> 'TSNA_US'
AND [Asset Class] IS NOT NULL
AND FactsetDate IN ('", d, "', '", d2, "')
ORDER BY [Predicted Tracking Error] DESC")
myConn <- odbcDriverConnect(connection = "driver = {SQL Server};server=SLSQL398_AGLIS\\SLSQL398;database=PortfolioAnalytics;trusted_connection=true")
data <- sqlFetch(myConn, "Data_Factset.FundChar", colnames = TRUE)
SQLCommand <- data.frame(sqlQuery(myConn, my_query))
ты герой мой друг
bring out the two dates... что вы имеете в виду? Есть ли в вашем текущем запросе проблема, которую вы хотите исправить?