Query SQL server to MySQL - get data
I am trying to extract some information from a MySQL database using MS SQL server Linked Server functionality (sql server 2005).
I need to get debit information and credit information and join on key column. I am not sure what is wrong with. I even tried print query. I removed lot of column Names and table names from joins and written basic query. Here it is:
Declare @pExecutionDate datetime
DECLARE @sqlCmd nvarchar(max)
set @pExecutionDate ='20120313'
SET @sqlCmd ='SELECT CASE LEN(RTRIM(ID)) WHEN 0 THEN CONvERT(VARCHAR(16), TxID) ELSE ID END AS GroupingID,
TxID, ID, Type, Category, TradeDate, Quantity,
CRDT_ShortName, NULL AS CRDT_LongName,
CRDT_Bank,
DBT_Code, NULL AS DBT_LongName,DBT_Bank,
Reference, Notes, NULL AS Comments
FROM OPENQUERY(mysqlcop,''SELECT DISTINCT * FROM (
SELECT CRDT.TxID, CRDT.ID, CRDT.Type,
CRDT.Category, CRDT.TradeDate, CRDT.Quantity,
CRDT_ShortName
FROM
(SELECT * FROM Table1
WHERE TradeDate = ' + CONVERT(varchar(10), @pExecutionDate, 112) + '
AND Type IN(''''XXX-'''', ''''CRDT'''')
AND Category IN (''''NOT-APPL'''', ''''TTTT'''')
AND CashID = ''''BILL''''
) AS CRDT
INNER JOIN ….
INNER JOIN ….
INNER JOIN …
LEFT OUTER JOIN …
LEFT OUTER JOIN …
LEFT OUTER JOIN …) AS CRDT
INNER JOIN OPENQUERY (mysqlcop, ''Select * from (SELECT DBT.ID, …..
FROM
(SELECT * FROM Table1
WHERE TradeDate = ' + CONVERT(varchar(10), @pExecutionDate, 112) + '
AND Type IN(''''XXX+'''', ''''DBT'''')
AND SettleTxnCategory = ''''NOT-APPL''''
AND SecurityID = ''''BILL''''
) AS DBT
INNER JOIN ….
INNER JOIN ….
INNER JOIN …
AND …
LEFT OUTER JOIN …
) AS DBT
ON CRDT.ID = DBT.ID
AND CRDT.Quantity = DBT.Quantity
AND CRDT.SettleDate = DBT.SettleDate
WHERE NOT CRDT.CRDT_BankAccount = DBT.DBT_BankAccount
'')';
Thanks,