CODEWITHSUNDEEP
sqlsql-serverdb2-400
Shrikant
Shrikant

Reputation: 21

Change DB2 Select query to sql Server query

I am migrating my application from DB2 database to sql server 2008, for this I have to replace some queries according to sql server.

My query is:

SELECT '' 
       || table1.openid AS OPENID, 
       table2.mfg_part_no, 
       table2.description, 
       binnumber, 
       storageid, 
       tracktime, 
       table1.userid 
FROM   table1 
       LEFT JOIN table2 
              ON table2.openid = table1.openid 
       LEFT JOIN table3 
              ON table3.locationid = table2.locationid 
WHERE  value = '14' 
       AND field = 'STATUSID' 
       AND tracktime > '2012-01-01 00:00:00.00' 
       AND tracktime < '2014-01-02 00:00:00.00'

how can I write this query in sql server?

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

I think this will do it:

SELECT cast(table1.openid as varchar(255)) AS OPENID, 
       table2.mfg_part_no, 
       table2.description, 
       binnumber, 
       storageid, 
       tracktime, 
       table1.userid 
FROM   table1 
       LEFT JOIN table2 
              ON table2.openid = table1.openid 
       LEFT JOIN table3 
              ON table3.locationid = table2.locationid 
WHERE  value = '14' 
       AND field = 'STATUSID' 
       AND tracktime > '2012-01-01' 
       AND tracktime < '2014-01-02' ;

The only DB2 specific thing I see is the string concatenation operator. I also removed the time component from the dates. That shouldn't be needed in either database.

Upvotes: 1

Related Questions