|
hi,
i have an sql table which has no primary key. i want to find out wether the same row is repeated. how can i see it.
regards
Ruwandi
rkherath
|
|
|
|
|
select field_name count(*) from tablename where group by field_name order by 1;
This query to find out the number of duplicate value.
|
|
|
|
|
its also the best query
SELECT *
FROM BB1
WHERE (DATE_STAMP,DB_NAME,TABLE_NAME) IN (SELECT DATE_STAMP,
DB_NAME,
TABLE_NAME
FROM BB1
GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
HAVING COUNT(* ) > 1);
|
|
|
|
|
hi ganesamoorthidhayalan,
it works. thanks a lot.
Regards
Ruwandi
rkherath
|
|
|
|
|
I'm using SQL to output some data from the 3 table below (Sales, Product,& Staff).
How if i want to display a table as this...Have any ideas??
ProductId TotalSale Sale_of_team1 sale_of_team2 sale_of_team3
P012 3 0 3 0
P016 27 7 0 20
P017 4 4 0 0
P019 2 0 2 0
P022 2 0 2 0
>>Sales
ProductId Date Week SalesUnit StaffId
P012 20070514 20 3 S002
P016 20070531 22 7 S001
P016 20070531 22 20 S003
P017 20070531 22 2 S001
P017 20070523 21 2 S001
P019 20070530 22 2 S002
P022 20070502 18 2 S002
>>Product
ProductId ProductName Category Cost
P012 ADSL 512M/256K Streamyx 77
P016 LT 38 Lets Talk 38
P017 PLAN A Lets Talk 68
P019 PLAN C Lets Talk 198
P022 Italk Mobile-50 Italk Prepaid Cards 50
>>Staff
StaffId StaffName Team Level
S001 Jass 1 1
S002 kwee 2 2
S003 Rain 3 2
Thank for helping...arigatou gozaimasu!!!
|
|
|
|
|
The following should do the trick:
SELECT Sales.ProductId,
SUM(Sales.SalesUnit) AS TotalSale,
SUM(CASE Staff.Team = 1 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team1,
SUM(CASE Staff.Team = 2 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team2,
SUM(CASE Staff.Team = 3 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team3
FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
GROUP BY Sales.ProductId The "case" clauses allow you to conditionally summate a column.
Or
SELECT Product.ProductId,
(SELECT SUM(SalesUnit) FROM Sales) AS TotalSale,
(SELECT SUM(SalesUnit) FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
AND Staff.Team = 1
WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team1,
(SELECT SUM(SalesUnit) FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
AND Staff.Team = 2
WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team2,
(SELECT SUM(SalesUnit) FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
AND Staff.Team = 3
WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team3
FROM Products This uses correlated sub-queries to do the work.
Regards
Andy
|
|
|
|
|
hi All,
i have a dates field which contains dates as varchar type
Dates
(yyyymmdd)
20071231
20070312
20071123
i wanna convert this in to
yyyy/mm/dd
2007/12/31
2007/03/12
2007/11/23
how can i do this please tell me
convert(varchar,dates,101)
didnt include the '/'
regards
Ruwandi
rkherath
|
|
|
|
|
to get the dates with "/" seperated you can use
substring(dates, 1, 4) + '/' + substring(dates, 5, 2) + '/' + substring(dates, 7, 2)
But the dates remains varchar
convert function actually converts to datetime data type.
Regards
KP
|
|
|
|
|
hi,
thank. i got it .
thx a lot.
regards
Ruwandi
rkherath
|
|
|
|
|
I need to change the display from (123.98) to -123.98 in SSRS.
I tried couple of fromat option on text box and still no good results...
Any Help!!
Amit
|
|
|
|
|
Hi,
I have one procedure in Oracle which is returning the REF Cursor I am accessing it by using the Data access application block DAAB like the following:
OracleParameter param = new OracleParameter();
param.ParameterName = "Emp_Cursor";
param.OracleType = OracleType.Cursor;
param.Direction = ParameterDirection.Output;
param.Size = 3000;
Database db = DatabaseFactory.CreateDatabase("DBConnection");
//DbCommand cmd = db.GetSqlStringCommand("Select * from emp");
DbCommand cmd = db.GetStoredProcCommand("emppackage.select_employee");
db.AddOutParameter(cmd, param.ParameterName, param.DbType, param.Size);
DataSet ds = new DataSet();
ds = db.ExecuteDataSet(cmd);
GridView1.DataSource = ds;
GridView1.DataBind();
But iam getting this error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_EMPLOYEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
How to call Ref Cursors in DAAB.
Thanks in advance.
|
|
|
|
|
Hi,
I am using a custom .NET DLL from within a script task in SSIS. I want to override some configuration settings in the the DLL (app.config settings). Can I do this is SSIS and if so how?
Thanks,
Fergal.
|
|
|
|
|
Hi,
I have two stored procedures that returns a SELECT result.
I want to be able to return this results, from a third stored procedure, according to a parameter.
Something like this:
IF (@DisplayUnknown = 0)<br />
BEGIN<br />
EXEC stpSnapshotNormal(@Type, @dtDate)<br />
END<br />
ELSE<br />
BEGIN<br />
EXEC stpSnapshotUnknown(@Type, @dtDate)<br />
END
This of course does not work.
Does anyone knows how is it possible to make work?
Thanks!
|
|
|
|
|
the problem is parameters are enclosed with in parenthesis
use this way
IF (@DisplayUnknown = 0)
BEGIN
EXEC stpSnapshotNormal @Type, @dtDate
END
ELSE
BEGIN
EXEC stpSnapshotUnknown @Type, @dtDate
END
Regards
KP
|
|
|
|
|
Thanks, for some reason I missed that!
Hate when it happens
|
|
|
|
|
|
Hi,
I have a table that looks like this:
Name Job Salary
Jim Eng 50000
Jane NULL 100000
Bill NULL 55000
Bill Dev 55000
I need a query that would strip out the rows that have NULLs for 'Job' if there's a row with the same 'Name' with a non NULL 'Job'.
The result would look like this:
Jim Eng 50000
Jane NULL 100000
Bill Dev 55000
I could easily do this in C# after I bring all the rows in, but I thought it would be more interesting to get the right data with a query.
Any ideas?
thanx
|
|
|
|
|
SELECT * FROM MyTable<br />
WHERE Job IS NOT NULL<br />
OR Name NOT IN (SELECT NAME FROM MyTable WHERE Job IS NOT NULL)
|
|
|
|
|
thanks Andy, that was it!
|
|
|
|
|
Hi
can u help me?
i have created an Entity Relation Diagram in Visio Enterprise Architect 2007. Now i want to export the ERD from Visio to SQL Server 2005.I don`t know how to do that?
I need help on this stage.
Thanks.
|
|
|
|
|
Hi all
i am having problems with a case statment within my query, i want to list a series of dates and times, and then list against it if it is AM or PM
im trying to do something like the below, but im getting an error
anyone any ideas ??
select mydatetime,
CASE mydatetime
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable
thanks
simon
|
|
|
|
|
DOH !!!
select mydatetime,
CASE
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable
|
|
|
|
|
CASE statemet to be terminated with END
which is missing in your select query
Regards
KP
|
|
|
|
|
Hi,
Try this code:
select mydatetime,
'exp1' =
case
when datepart(hour,mydatetime) > 11 THEN 'PM'
else 'AM'
end
from mytable
|
|
|
|
|
Hi
We are using reporting service,
How to change the reporting services column header,headers...etc
into different language, in c# we use resource file and use this
resource file to change different language(multilingual Option).
How can change these into reporting services.
Regards
Arunkumar Sundaravelu
|
|
|
|