SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 12:09 PM
| ||||
| ||||
 Who can help me with DB2 synax? Hi every, Recently I'm translating some SQL Server query into DB2 query. But some problems blocked me. Who can help me? 1. How to set a null for a new column in DB2? I want to write a query like this(SQL Server query): SELECT ResellerKey, NULL AS CustomerKe FROM A UNION SELECT NULL AS ResellerKey, CustomerKe FROM B How to set a null to a new column in DB2? 2.How to convert the smallint to bit? like a column A, it's datatype is smallint, But I need bit datatype, hot to convert? 3. How to convert the double to money? like a column A, it's datatype is double But I need money datatype, hot to convert? Any reponse is appreciated. Thanks Winnie      |
|
02-27-2008, 12:09 PM
| |||
| |||
| Re: Who can help me with DB2 synax? winnie.wang11@gmail.com wrote: > Hi every, > > Recently I'm translating some SQL Server query into DB2 query. But > some problems blocked me. Who can help me? > 1. How to set a null for a new column in DB2? > I want to write a query like this(SQL Server query): > SELECT ResellerKey, NULL AS CustomerKe > FROM A > UNION > SELECT NULL AS ResellerKey, CustomerKe > FROM B > How to set a null to a new column in DB2? SELECT ResellerKey, CAST(NULL AS INTEGER) AS CustomerKe FROM A UNION ALL SELECT CAST(NULL AS SMALLINT) AS ResellerKey, CustomerKe FROM B Substitute the types as appropriate. Also note that I added UNION ALL instead of UNION. Judjing by the names the columns can't be NULL, so there are no dups. You should use UNION ALL in SQL Server as well.... > 2.How to convert the smallint to bit? > like a column A, it's datatype is smallint, But I need bit datatype, > hot to convert? There is no BIT data type in DB2. Either use SMALLINT or CHAR(1). > 3. How to convert the double to money? > like a column A, it's datatype is double But I need money datatype, > hot to convert? You can define a DISTINCT TYPE money and SOURCED FUNCTIONS to operate on it if you wish. I would choose DECIMAL as a base... Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
|
02-27-2008, 12:09 PM
| |||
| |||
| Re: Who can help me with DB2 synax? On Apr 13, 2:09 pm, winnie.wan...@gmail.com wrote: > Hi every, > > Recently I'm translating some SQL Server query into DB2 query. But > some problems blocked me. Who can help me? > 1. How to set a null for a new column in DB2? > I want to write a query like this(SQL Server query): > SELECT ResellerKey, NULL AS CustomerKe > FROM A > UNION > SELECT NULL AS ResellerKey, CustomerKe > FROM B > How to set a null to a new column in DB2? SELECT ResellerKey, CustomerKe FROM A FULL OUTER JOIN B ON 0=1 |
|
02-27-2008, 12:09 PM
| ||||
| ||||
| Re: Who can help me with DB2 synax? Serge Rielau wrote: > winnie.wang11@gmail.com wrote: >> Hi every, >> >> Recently I'm translating some SQL Server query into DB2 query. But >> some problems blocked me. Who can help me? >> 1. How to set a null for a new column in DB2? >> I want to write a query like this(SQL Server query): >> SELECT ResellerKey, NULL AS CustomerKe >> FROM A >> UNION >> SELECT NULL AS ResellerKey, CustomerKe >> FROM B >> How to set a null to a new column in DB2? > SELECT ResellerKey, CAST(NULL AS INTEGER) AS CustomerKe FROM A > UNION ALL > SELECT CAST(NULL AS SMALLINT) AS ResellerKey, CustomerKe FROM B > > Substitute the types as appropriate. Also note that I added UNION ALL > instead of UNION. Judjing by the names the columns can't be NULL, so > there are no dups. > You should use UNION ALL in SQL Server as well.... > >> 2.How to convert the smallint to bit? >> like a column A, it's datatype is smallint, But I need bit datatype, >> hot to convert? > There is no BIT data type in DB2. Either use SMALLINT or CHAR(1). > >> 3. How to convert the double to money? >> like a column A, it's datatype is double But I need money datatype, >> hot to convert? > You can define a DISTINCT TYPE money and SOURCED FUNCTIONS to operate on > it if you wish. I would choose DECIMAL as a base... Agree with Serge on this one. Bear in mind that "money" is not just a value, but also has (at least) a unit of currency. So you have to take care that all the values are in the same currency before just summing them. It's worth checking out the Design Pattern on Money to get a handle on how complex a topic this actually is. > > Cheers > Serge |
Linear Mode
