Sybase to SQL Server migration

chickanna
chickanna used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
SQL is using CASE instead of IF - please try below:

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

Author

Commented:
I am getting "invalid column name" error for TotalLoansImported and TotalLoansExported.
lcohanDatabase Analyst

Commented:
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

Author

Commented:
Now TotalLoansExported gives the error "invalid column name" . TotalLoansImported is fine now.
Database Analyst
Commented:
Updated again as the column TotalLoansExported was a calculated one - sorry I missed that

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)) > Count(*) THEN 'Duplicates exist'
ELSE
CASE WHEN (SELECT COUNT(*)
    FROM ezadmin.ft_LoanMaster m
    WHERE (h.statusdate = m.statusdate)) = Count(*) 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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial