how i convert varchar sal field to numeric in query
select sum(sal) from emp1
error:the sum or average aggregate operation cannot take a varchar data type as an argument.
Use Cast as in:
select sum(cast(sal as float)) from emp1
Showing posts with label operation. Show all posts
Showing posts with label operation. Show all posts
Friday, February 24, 2012
How I can start SQL Manager 2005
I have a Visual Studio 2003 and intalled Visual Studio 2005 same operation I made with SQL Server 2005 CPT but didn′t install the SQL Server manager only install SQL Configuration Manager Can you help me?You just need to re-run the install and install the database tools/workstation subset. That will include the management studio component.
Sunday, February 19, 2012
How I can do Set Minus operation?
I would like to perform the Set Minus operation on two
table containg same structure.
For Example
Table1(Field1, Field2) having rows like (1,1),(2,1),(3,2),
(4,2).
Table2(Field1, Field2) having rows like (1,1),(3,2)
When I perfom Set Minus Operation on like Table1-Table2.
It should generate the resultant rows like (2,1) and (4,2)
in output.
I would like to perform this type of operation with SQL
Server.SELECT T1.*
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.col1 = T2.col1 AND T1.col2 = T2.col2
WHERE T2.col1 IS NULL
--
David Portas
--
Please reply only to the newsgroup
--|||The solutions works if there a no NULLS in the T2.col1.
Personaly (but this is a preference) I would use a not exists for a minus
operation.
Select * form T1 Where not exists (Select * from T2 where T1.pk = T2.pk)
The compare on pk is for all fields belonging to the primairy key.
When not using a pk, be carefull about NULLs (NULL = NULL evaluates
to false).
If there are potentially more matches the exists clause could work faster,
because it has only to evaluate one existence and can stop there.
ben brugman.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Oj1JylDfDHA.3268@.tk2msftngp13.phx.gbl...
> SELECT T1.*
> FROM Table1 AS T1
> LEFT JOIN Table2 AS T2
> ON T1.col1 = T2.col1 AND T1.col2 = T2.col2
> WHERE T2.col1 IS NULL
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
table containg same structure.
For Example
Table1(Field1, Field2) having rows like (1,1),(2,1),(3,2),
(4,2).
Table2(Field1, Field2) having rows like (1,1),(3,2)
When I perfom Set Minus Operation on like Table1-Table2.
It should generate the resultant rows like (2,1) and (4,2)
in output.
I would like to perform this type of operation with SQL
Server.SELECT T1.*
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.col1 = T2.col1 AND T1.col2 = T2.col2
WHERE T2.col1 IS NULL
--
David Portas
--
Please reply only to the newsgroup
--|||The solutions works if there a no NULLS in the T2.col1.
Personaly (but this is a preference) I would use a not exists for a minus
operation.
Select * form T1 Where not exists (Select * from T2 where T1.pk = T2.pk)
The compare on pk is for all fields belonging to the primairy key.
When not using a pk, be carefull about NULLs (NULL = NULL evaluates
to false).
If there are potentially more matches the exists clause could work faster,
because it has only to evaluate one existence and can stop there.
ben brugman.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Oj1JylDfDHA.3268@.tk2msftngp13.phx.gbl...
> SELECT T1.*
> FROM Table1 AS T1
> LEFT JOIN Table2 AS T2
> ON T1.col1 = T2.col1 AND T1.col2 = T2.col2
> WHERE T2.col1 IS NULL
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
Subscribe to:
Posts (Atom)