asked on Sybase to SQL Server migration
experts... I have a query in Sybase as below..
SELECT h.StatusDate ,
Count(*) AS TotalLoansExported,
(SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) AS TotalLoansImported,
(IF (TotalLoansImported > TotalLoansExported) THEN
'Duplicates exist'
ELSE
IF (TotalLoansImported = TotalLoansExported) THEN
'Imported'
ELSE
IF (TotalLoansImported = 0) THEN
'Not imported'
ENDIF
ENDIF
ENDIF) AS SystemMessage
FROM ezadmin.ft_ImportHistory h
WHERE (IALoanNum <> '')
GROUP BY h.StatusDate
ORDER BY h.StatusDate DESC
I need to convert this to SQL Server 2005/2008 format. I get the famous "multipart identifier cannot be bound" error in the first line where the h.status_date is.
How do I correct this query to make it work in SQl Server. Thanks.
SELECT h.StatusDate ,
Count(*) AS TotalLoansExported,
(SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) AS TotalLoansImported,
(IF (TotalLoansImported > TotalLoansExported) THEN
'Duplicates exist'
ELSE
IF (TotalLoansImported = TotalLoansExported) THEN
'Imported'
ELSE
IF (TotalLoansImported = 0) THEN
'Not imported'
ENDIF
ENDIF
ENDIF) AS SystemMessage
FROM ezadmin.ft_ImportHistory h
WHERE (IALoanNum <> '')
GROUP BY h.StatusDate
ORDER BY h.StatusDate DESC
I need to convert this to SQL Server 2005/2008 format. I get the famous "multipart identifier cannot be bound" error in the first line where the h.status_date is.
How do I correct this query to make it work in SQl Server. Thanks.
Microsoft SQL Server
Last Comment
lcohan
ASKER
I am getting "invalid column name" error for TotalLoansImported and TotalLoansExported.
Right...this hopefully works for you:
SELECT h.StatusDate ,
Count(*) AS TotalLoansExported,
(SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) AS TotalLoansImported,
CASE WHEN (SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) > TotalLoansExported THEN 'Duplicates exist'
ELSE
CASE WHEN (SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) = TotalLoansExported THEN 'Imported'
ELSE
CASE WHEN (SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) = 0 THEN 'Not imported'
END
END
END AS SystemMessage
FROM ezadmin.ft_ImportHistory h
WHERE (IALoanNum <> '')
GROUP BY h.StatusDate
ORDER BY h.StatusDate DESC
SELECT h.StatusDate ,
Count(*) AS TotalLoansExported,
(SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) AS TotalLoansImported,
CASE WHEN (SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) > TotalLoansExported THEN 'Duplicates exist'
ELSE
CASE WHEN (SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) = TotalLoansExported THEN 'Imported'
ELSE
CASE WHEN (SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) = 0 THEN 'Not imported'
END
END
END AS SystemMessage
FROM ezadmin.ft_ImportHistory h
WHERE (IALoanNum <> '')
GROUP BY h.StatusDate
ORDER BY h.StatusDate DESC
ASKER
Now TotalLoansExported gives the error "invalid column name" . TotalLoansImported is fine now.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SELECT h.StatusDate ,
Count(*) AS TotalLoansExported,
(SELECT COUNT(*)
FROM ezadmin.ft_LoanMaster m
WHERE (h.statusdate = m.statusdate)) AS TotalLoansImported,
CASE WHEN TotalLoansImported > TotalLoansExported THEN 'Duplicates exist'
ELSE
CASE WHEN TotalLoansImported = TotalLoansExported THEN 'Imported'
ELSE
CASE WHEN TotalLoansImported = 0 THEN 'Not imported'
END
END
END AS SystemMessage
FROM ezadmin.ft_ImportHistory h
WHERE (IALoanNum <> '')
GROUP BY h.StatusDate
ORDER BY h.StatusDate DESC