|
hi,
i just want to know is it possible to get a row by its number in SQL like rowid in oracle.if it is there some one please help me.
like i want to get the values of 10 th row.
with regards,
susa
|
|
|
|
|
SELECT TOP 1 * FROM (SELECT TOP 10 * FROM tablex ORDER BY columnx DESC) AS aliasx ORDER BY columnx ASC
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
|
Hi Friend,
If U want to get the 10th Row:
------------------------------
SELECT TOP 1 * FROM
(SELECT top 10 * FROM TABLENAME ORDER BY NUMERICCOLUM) AS SUBS
order by NUMERICCOLUM desc
If U want to get the 12th Row:
------------------------------
SELECT TOP 1 * FROM
(SELECT top 12 * FROM TABLENAME ORDER BY NUMERICCOLUM) AS SUBS
order by NUMERICCOLUM desc
With Regards,
Pandian S
|
|
|
|
|
Hello. I have 2 table. I wrote below codes but after delete command parent row have been deleted but chid rows haven't been deleted from "B" table. Is there any wrong thing for relation or delete automation?
Thanks a lot.
fkc = new ForeignKeyConstraint("FKC", ds.Tables["A"].Columns["A2"], ds.Tables["B"].Columns["B2"]);
ds.Tables["B"].Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
ds.EnforceConstraints = true;
ds.Relations.Add("REL", ds.Tables["A"].Columns["A2"], ds.Tables["B"].Columns["B2"]);
|
|
|
|
|
Hi guy -
I think you have several problems here. (I just went through some of this stuff myself.)
1. AcceptRejectRule.None means that when you call ParentRow.AcceptChanges() it will not be propagated (passed on) to the child table. You ARE calling ParentRow.AcceptChanges() at some point, right?
I think what you want is AcceptRejectRule.Cascade
Either that or you will just have to call AcceptChanges() on the child table explicitly.
2. pirogramci wrote: ds.Relations.Add("REL", ds.Tables["A"].Columns["A2"], ds.Tables["B"].Columns["B2"]);
This format of the statement automatically creates a foreign key constraint named the same as the relation, with potentially conflicting rules from the ForeignKeyConstraint "FKC" you explicitly created. (I don't remember what the defaults are, but I think it's None.)
Use this instead: ds.Relations.Add("REL", ds.Tables["A"].Columns["A2"], ds.Tables["B"].Columns["B2"] ,FALSE);
so that it will not create the additional FK constraint and conflict with the one you already created.
I think that *should* fix it... Good luck. 
|
|
|
|
|
Got a simple table in the database which has two columns:
TableName: nchar(30) [Primary Key]
Index : int
The contents of this table are temporarily stored in a Dictionary<string, int> object during the program where some modifications are made, later on they are updated back to the database. All goes well apart from two rows of the 37 which some characters are lost. E.g. the two in question are:
MajorNonConformityReport -> MajorNonConformityRe
GoodsInwardThroughput -> GoodsInwardThroughpu
Any ideas on why this is happening, the update code which is a simple UPDATE T-SQL command has the TableName and Index inserted via parameters, by stepping through the code the parameters are input correctly.
Any ideas on what may be causing this? Before you ask, I don't have access to SQL Profiler, I have been given a workstation with VS2005 and access to the database on the server (running SQL 2005 WorkGroup edition).
Formula 1 - Short for "F1 Racing" - named after the standard "help" key in Windows, it's a sport where participants desperately search through software help files trying to find actual documentation. It's tedious and somewhat cruel, most matches ending in a draw as no participant is able to find anything helpful. - Shog9
Ed
|
|
|
|
|
That looks remarkably like somthing somewhere is treating the table name as nChar(20), not nChar(30), since they are both being truncated.
Are you sure that the parameters are declared correctly?
Steve S
Developer for hire
|
|
|
|
|
Yes parameters are being declared properly, I double checked all of that but I will double check it tomorrow when I get back (in case I fell asleep at the computer and dreamed it).
It might be that since it appears to be happening "randomly".
I've got a work around at the moment, I've abbreviated the names which is quite apt since "GoodsInwardThroughput" has been abbreviated to "GIT"  .
As of how to accomplish this I wouldn't have a clue at the moment and I'm too lazy to google it
|
|
|
|
|
Hey I am using TripleDES encryption 192bit key algorithm to encrypt my data...Now i have got a doubt like if my password datatype length is only 10 chars. Then how much space or length do i need to allocate in database table for this field when i am using 192bit alogrithm...
Please help.
|
|
|
|
|
Don't quote me:
TDES uses 3 64bit keys (the 192 bits you pass) so you should pad to 64 bits. Non-unicode would mean you nead 128bits.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
hi all,
i just want to know how to get the nth highest salary from employee table.any help appreciated.
with regards,
susa
|
|
|
|
|
select top 1 sal from (select top 6 from emp order by sal asc) order by sal desc
|
|
|
|
|
Try this
SELECT empid,MAX(Salary) FROM Employees WHERE EmpID IN (SELECT TOP 2 EmpID FROM Employees ORDER BY Salary Desc)
Muralidhar
|
|
|
|
|
hi,
when im executed this query i got only the first two higher values even if i speciofied top 5 values
thanks
with regards,
susa
|
|
|
|
|
hi boss ,
At last i got a solution for that problem.
i used this one
select min(sal) from(SELECT distinct TOP n sal FROM account ORDER BY Sal desc) as newsalary
here instead of n we can give what ever the highest number we need.
with regards,
susa
|
|
|
|
|
|
I hope I am in the right forum. My data type column from the table ClockIn_Date is DateTime. The dropdownlist seems to select distinct date+time. I want to only select distinct the date and not inclusive of the time. What is the correct sql statement?
SELECT DISTINCT ClockIn_Date<br />
FROM dbo.Stud_ClockIn
thanks in advance. Much appreciated.
|
|
|
|
|
Hi I am using sql reporting services 2000.
I have a stored proc as below,
----------------
CREATE PROC Casual_Hirers_Event_List
(
@StartDate Datetime
,@EndDate Datetime
)
AS
SET NOCOUNT ON
SELECT VENUE.Venue_Name
,EVENT.Start_Date
,EVENT.End_Date
,EVENT.Event_Name
,EVENT.Event_Number
,STATUS_DESC.Status_RecNo
,EVENT.Event_Status_Desc_RecNo
FROM EVENT EVENT
INNER JOIN EVENT_VENUE EVENT_VENUE ON EVENT.Event_RecNo = EVENT_VENUE.Event_RecNo
INNER JOIN EVENT_STATUS EVENT_STATUS ON EVENT.Event_RecNo = EVENT_STATUS.Event_RecNo
INNER JOIN STATUS_DESC STATUS_DESC ON EVENT_STATUS.Status_RecNo = STATUS_DESC.Status_RecNo
INNER JOIN VENUE VENUE ON EVENT_VENUE.Venue_RecNo = VENUE.Venue_RecNo
WHERE (EVENT.Start_Date >= @StartDate)
AND (EVENT.Start_Date < @EndDate)
AND (STATUS_DESC.Status_RecNo = 4
OR STATUS_DESC.Status_RecNo = 37)
AND (EVENT.Event_Status_Desc_RecNo = 1)
ORDER BY VENUE.Venue_Name, EVENT.Start_Date
SET NOCOUNT OFF
Return
GO
----------------------
I want this proc to be executed in reporting service without creating another dataset which prompts me first for the start and end date and display the results.
is this possible in sql reporting services.
if i run with only one data set i get following error
----
c:\reports\casual hirers event list\Report1.rdl The report parameter ‘StartDate’ has a DefaultValue or a ValidValue that depends on the report parameter “StartDate”. Forward dependencies are not valid.
----
please help.
|
|
|
|
|
Hi All,
I have a table called movements:
AssetID Date LocationID
1234 3/4/06 15
312 5/6/05 12
1234 12/12/05 18
1234 8/8/06 15
312 9/9/05 10
The latest date in the table for each Asset shows where that Asset currently is. Can someone please help me with a query that, for each asset, will return its current location?
Thanks,
Martin.
-- modified at 16:06 Wednesday 9th August, 2006
|
|
|
|
|
select locationid from movements mt inner join<br />
(select max(date) as dt, assetid from movements group by assetid) as mt2<br />
on mt.date = mt2.dt and mt.assetid = mt2.assetid
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi All
I want to calculate the total of some monatry amounts within a table
i can do this by the following
declare @cost1 money
select @cost1 =sum(cost)from mydb..amounts
sometimes though there maybe no records in the amounts table, and the above statement would then return NULL
instead of NULL i would like it to return 0
i would like to do something like
select @cost1 =sum(cost) 0 as null from mydb..amounts
but this doesnt work
does anyone have any ideas ??
Thanks
Si
|
|
|
|
|
select sum(nvl(cost,0) total_cost from mydb..amounts;
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
|
|
|
|
|
Chris Meech wrote: select sum(nvl(cost,0) total_cost from mydb..amounts;
thanks, but this doesnt work
whats nvl ??
|
|
|
|
|
SELECT @cost1 = ISNULL(SUM(cost), 0) FROM mydb..amounts
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
