|
Thank you for your reply! Seems the problem has gone after I wait for some time...
|
|
|
|
|
sir,
I have a table which is having duplicate and some distinct records. Please can anybody please tell me the SQL-Server-2000 query to get only the duplicate records.
Means I dont need those records, which are not duplicate, but I need to print only those records which are duplicate. Thank you.
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
let us take a table TBL1 with columns col1, col2, col3 for example
the query for fetchig is ...
SELECT col1, col2, col3 FROM TBL1 GROUP BY col1, col2, col3 HAVING count(*) > 1
|
|
|
|
|
Its not giving any records in SQL Server. Pls. give me some thing, which works in SQL Server. Thank you.
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
I've tested the query on SQL server 2000
i'm getting proper output fetching proper data
i.e. i've a table in that new columns have duplicate data and other unique
when i include only duplicate data columns in the table i'm getting those rows which is duplicated that too when used ">10" returns those having 11 or more times repeated and when used ">3" returns those repeated 4 or more times and so on.
Regards
KP
|
|
|
|
|
Sir,
No sir, its not coming here why I dont know. pls. dont mind and tell me some thing na. pls. Thank you.
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
First thing pls. don't address my Sir.
I'm Krishna Prasad, you can call me using full name or KP ...
comming for your query.
pls let me know a) table structure b) some same data c) required output.
Regards
KP
|
|
|
|
|
KP lets suppose this is my table structure, with a, b, c are columns. Now I would like to print only those columns which are duplicate. And by using the query its not printing any thing.
* SELECT a, b, c FROM SampleTable GROUP BY a, b, c HAVING COUNT(*) > 1 *
But if I give <=1 or < or = its giving atleast some values.
And even if I give this also * SELECT a, b FROM SampleTable GROUP BY a, b HAVING COUNT(*) > 1 *
And * SELECT a, b FROM SampleTable GROUP BY a, b HAVING COUNT(a) > 1 * its also not giving me any thing.
Thank you.
----------------------------------
a b c
----------------------------------
AA 12 1
BB 12 tyt
CC rtr yuyu
TT Gfg yuy
TT sdsd iuiu
AA dsfdf uiui
DD Ere bvn
EE DS hj
FF dfd bn
GG fdf bnbn
HH Tr bnb
AA FDF bn
BB fdfd bn
RR dfd bn
II dfdf bvn
JJ dsfd nvbnnb
KK dfdf hnmnmn
LL dfdf nm
MM dsfdf nmn
NN dfd mn
NN dfdf mn
TT dfdf mnghjf
OO fdgfg jhj
PP fgf j
QQ fgf j
RR fgfg jhgjh
SS fgfg fdgfdg
TT fgf f
UU fgfgf gf
VV fgfg gh
XX ffg h
YY ghg bbv
ZZ rfg tyt
TT fgfg gfg
UU fgf fgf
VV fgf fgf
XX fgfg g
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
As per the sample data combination all three columns are unique. no duplication
however, separatly each column has duplicate values.
query given by me will list if combination three columns are repeated.
for example
AA 12 1
BB tyt 12
AA 12 1
CC tr mn
CC df tr
output would be
AA 12 1
rows with BB & CC will not be listed as they re present only once.
Regards
KP
|
|
|
|
|
Yeah its working K.P. Thank you very much.
Regards,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Hello,
I've created the following stored procedure:
<br />
CREATE PROCEDURE SOM_RO_ISVALIDPRJID <br />
@strProjectID varchar(32)<br />
AS<br />
BEGIN<br />
DECLARE @intPrjID int<br />
<br />
SET NOCOUNT ON;<br />
@intPrjID = (SELECT COUNT(*) FROM dbo.tbl_projects_main WHERE prj_custom_id = @strProjectID)<br />
END<br />
IF (@intPrjID = 0)<br />
RETURN 0<br />
ELSE<br />
RETURN 1<br />
GO<br />
I keep getting the error:
Incorrect syntax near '@intPrjID' at line 8.
The line it is referring to is where I have @intPrjID = (SELECT ...)
Ryan
|
|
|
|
|
replace it with:
SELECT @intPrjID = COUNT(*) FROM dbo.tbl_projects_main WHERE prj_custom_id = @strProjectID
or with
Set @intPrjID = (SELECT COUNT(*) FROM dbo.tbl_projects_main WHERE prj_custom_id = @strProjectID)
Wout Louwers
|
|
|
|
|
along with the two methods specified by Wout Louwers
also this can be used
SELECT @intPrjID = (SELECT COUNT(*) FROM dbo.tbl_projects_main WHERE prj_custom_id = @strProjectID)
(i.e. instead of SET, SELECT can be used.)
|
|
|
|
|
Someone mentioned something about docking or linking tables into Access. What does that mean? I believe linking means accessing a table through Access but I dont know about docking.
CleAkO
|
|
|
|
|
Hi,
I have 2 problems:
a) cannot connect to my sql table eventhough I used the same userid and pwd in Query Analyzer . I keep getting 'Not associated with a trusted SQL Server connection'
b) which date format I have to use to lookup for a row (from a search textbox)
Here is my code:
<%@ Import Namespace="System.Web.UI.WebControls"%>
<%@ Import Namespace="System.Data.SQLclient"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb" %>
Sub LookupDate (ByVal s As Object, ByVal e As EventArgs)
Dim myconn As SqlConnection
Dim strSelect As String
Dim cmdSelect As SqlCommand
myconn = New SqlConnection("server=localhost;UID=myUSERID;PWD=myPASSWRD;Database=myDBNAME")
strSelect = "SELECT [col1], [col2], [myTime] FROM [myTableName] WHERE myTime = @myTime ORDER BY [myTime] desc "
cmdSelect = New SqlCommand(strSelect, myconn)
cmdSelect.Parameters.AddWithValue("@myTime", textMyTime.Text)
myconn.Open()
lblVal.Text = cmdSelect.ExecuteScalar()
'lblVal.DataBind()
myconn.Close()
End Sub
<title>title
Date Lookup
<asp:textbox id="textMyTime" runat="server">
<asp:button text="search" id="search" onclick="LookupDate" runat="server">
<asp:label id="lblVal" enableviewstate="False" runat="server">
|
|
|
|
|
For some reason i got cutoff so here is my question again:
<%@ Import Namespace="System.Web.UI.WebControls"%>
<%@ Import Namespace="System.Data.SQLclient"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb" %>
Sub LookupDate (ByVal s As Object, ByVal e As EventArgs)
Dim myconn As SqlConnection
Dim strSelect As String
Dim cmdSelect As SqlCommand
conValid = New SqlConnection("server=localhost;UID=myUSERID; PWD=myPASSWRD;Database=myDBNAME")
strSelect = "SELECT [col1], [col2], [myTime] FROM [myTableName] WHERE myTime = @myTime ORDER BY [myTime] desc "
cmdSelect = New SqlCommand(strSelect, myconn)
cmdSelect.Parameters.AddWithValue("@myTime", textMyTime.Text)
myconn.Open()
lblVal.Text = cmdSelect.ExecuteScalar()
'lblVal.DataBind()
myconn.Close()
End Sub
<title>title
Date Lookup
<asp:textbox id="textMyTime" runat="server">
<asp:button text="search" id="search" onclick="LookupDate" runat="server">
<asp:label id="lblVal" enableviewstate="False" runat="server">
|
|
|
|
|
When you start Query Analyzer, tick the "Windows Authentication" option. You should need to input a user ID and Password for a trusted connection.
I would question the data-type of your parameter. If you use a datetime type and cast your text to a datetime variable then you shouldn't have to worry about the format.
Also, you probably should be using ExecuteScalar because your select statement is returning several columns, and looks like you want to return several rows too.
|
|
|
|
|
in SQL Analyzer, I was able to connect and login under SQL auth. I also able to login using Window Auth. but my query comes back empty. In another code, I used
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dbReMUSConnectionString %>"
="" selectcommand="SELECT .................... ">
on the same db and I had no problem.
I also using vs 2005 and the db is a sql 2000 db
Thanks
|
|
|
|
|
where the rest of my asp tag goes? Do I need to put something around them?
|
|
|
|
|
hi
how should i retrieve View's SQL statement in SQL Server.
i am using OleDb and i want it to be compatible with all the data Systems.
for example i can retrieve the Query statements from an Access database (with GetOleDbSchemaGuid() ) but i am not able to do it with SQL Server (it dowsnt support retrieving views that way).
is there another way or a specific SQL statement to do so??
thanks in advance
-- modified at 6:20 Monday 26th February, 2007
|
|
|
|
|
I used profiler to create a trace of all the queries from SSMS to the database and came up with this:
<br />
SELECT ISNULL(smv.definition, ssmv.definition) AS [Definition]<br />
FROM sys.all_views AS v<br />
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id<br />
WHERE (v.type = 'V')and(v.name=N'{my_view_name}' and SCHEMA_NAME(v.schema_id)=N'dbo')<br />
Just replace {my_view_name} with the name of the view you're interested in. Also, if the view is using a schema other than 'dbo' make sure to change that as well.
|
|
|
|
|
Hello everybody,
I have a Attendance table, which has columns Date. ShiftInTime, ShiftOutTime, EventTime, Login_Type.
Now for every EventCode= "I" or "O" there are two records. That means for a day say 07/10/2006 as Workdate there are two records like,
EmpID Date EventTime EventCode
1 07/10/2006 9:00 I
1 07/10/2006 17:15 O
NOW.. I want a report in Table or Crystal report which will have a single record for a single Workdate..as folllows
EmpID WorkDate ShiftInTime ShiftOutTime InTime OutTime
1 07/10/2006 09:00 17:00 9:00 17:15
Can you please help me out..I'm usng a table in ASp
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
select * from table t1 inner join table t2
on t1.date = t2.date
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
|
|
|
|
|
Sir,
U'r suggestion is not enough...because i am using onlu only table ,,,my quear gave me theattendance report like i shown above...but Can i simply make the inner join with the same table?
i need the out put as follows
----------------------------------
ID |Name |InTime |OutTime|
1 |xxxxxxxxxxx| 09:05 | 06:01
2 |sdsssdddddd| 06:15 | 06:15
-----------------------------------
Like that..But at present they are displaying as i explainned in my first question.
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
Try this:
SELECT main.EmpId, main.Date,
(SELECT i.EventTime FROM Attendance i
WHERE i.EmpID = main.EmpId AND
i.Date = main.Date AND i.Login_Type = 'I') AS ShiftInTime,
(SELECT o.EventTime FROM Attendance o
WHERE o.EmpID = main.EmpID AND
o.Date = main.Date AND o.Login_Type = 'O') AS ShiftOutTime
FROM Attendance main
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
