|
Yeah, I can see your query would run faster but my database is far more complicated than this example. But you're right! I have to look at the structure, indexes, etc, of my database and see if I can make this query look like yours. However, I don't think it can look like yours because it's very dynamic query and I don't want to create different queries for each cases - I'm just modifing parts of the query. Maybe I shouldn't be so lazy and write a dedicated queries for each of the cases (even though it's against the best programming practices).
... No wonder why there's so many database jobs out there =)
Thank you again!
-- modified at 16:47 Friday 13th April, 2007
|
|
|
|
|
I wanted to know if it is possible to get the data in parts.
I mean my application has many records in the table, so for a sql query like select * from table, there is lots of records to be read from the ResultSet.
Is there any query like
select (first 100) * from table
then
select (next 100) * from table
.
.
.
something like this ?
please help
|
|
|
|
|
select top 100 records from table where id > @id The first time in, pass in an @id of 0. Next time, pass in the maximum id of the previous results.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
You are right that it is a simplistic solution. I find that it fits a lot of cases though.
A lot of time when you sort/shuffle the id's around, this is done by retrieving the data in one go and performing the sorting/shuffling on the server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
SELECT *<br />
FROM Table <br />
WHERE RowNumber Between @FirstRow AND @EndRow
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
I still didnt get what to do
How should the above command be written as ? my table name is 'orders' and there are only 2 fields in that 'ORDERID' and 'ITEMDESIRED'
There is no primary key as such. Yes I can make 'ORDERID' the Primary key though.
What should be the '@FirstRow' and '@EndRow' values be?? or should I write them just like that. Please help, its urgent.
I am using the ojdbc14.jar to connect to the Oracle 10g database.
I read somewhere about the LIMIT and OFFSET command, but tried it in Oracle SQL* Plus and saw that it does not work. Also it gave the same exception when tried to run the command through my Java program.
The command I ran is
<br />
select * from orders limit 10;<br />
and the Exception I got is
<br />
ORA-00933: SQL command not properly ended<br />
|
|
|
|
|
The example query I gave works fine for SQL Server 2005
@FirstRow = row, from which you want to start getting the results
@EndRow = last row of your results
In your case, for exapmple, it can be
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID BETWEEN 5 AND 25
OR, if this does not work, in your db, try:
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID > 5 AND ORDERID < 25
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
i problem
in a database i do i find how many feilds name id in database
|
|
|
|
|
You have another problem. The inability to read posting guidelines.
You've already posted about this, so don't start a new thread. It's not polite.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O'Hanlon wrote: another problem. The inability to read posting guidelines
|
|
|
|
|
I am using MS Access 2003 with VB.Net 2003. In one of my Table A field(ResignDate) is of type Text
I am writing following query to retrieve the name and other information of employee who resigned before a specific date which is to be given in the condition.....
my query is..
Dim sql16 As String = "SELECT Employee_AutoID,NameInitial,NameInitial+' '+Name " & _<br />
"AS Emp_Name,Designation FROM tblEmployeeMaster " & _<br />
"WHERE Resign_Flag='No' OR CDate(ResignDate)< #" & endDayOFFinancialYear & "# "
what is wrong in the query....
Tirtha
Miles to go before I sleep
|
|
|
|
|
remove Cdate function
resigndate='"+enddayofffinancialyear+"'
|
|
|
|
|
how do we count stored proc in database
how do we count system difined stored proc and userdifined stored proc
|
|
|
|
|
The stored procedures in sql are objects so whenever a stored procedure is created its information is stored in the system table sysobjects, and as it is a sp the type would be 'p'
Thus to know the number of stored procedures for a database the query would be
select count(*) from sysobjects where type='p'
Gautham
|
|
|
|
|
thanks but now i have another problem
in a database i do i find how many feilds name=id in database
|
|
|
|
|
I didnt get that???
Gautham
|
|
|
|
|
i mean that in a database there are three table
emp1
emp2
emp3
all table have a column name id
how to count number of table with column id
|
|
|
|
|
select * from sysobjects t1 join syscolumns t2 on t1.id=t2.id where t2.name="columnname"
i guess, not sure though!
Gautham
|
|
|
|
|
This will show all tables where there is a column named id
SELECT TABLE_NAME
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id';
Here you are counting how many such cases are in your db.
SELECT COUNT(COLUMN_NAME)
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id' ;
Note: This is correct for SQL 2005
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
Hi all,
I'm using interopt object to handle data in an excel file, like that:
Excel.Application ExcelObj = new Excel.Application();<br />
<br />
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(FilePath, 0, true, 5,<br />
"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);<br />
<br />
Excel.Sheets sheets = theWorkbook.Worksheets;<br />
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(SheetIndex);<br />
<br />
Excel.Range range = worksheet.get_Range(BeginCol + BeginRow.ToString(), EndCol + BeginRow.ToString());<br />
System.Array myvalues = (System.Array)range.Cells.Value2;<br />
With cells formated as DateTime datatype, this method didn't get the correct format...Ex: 04/11/2007, it'll get as 39183.00...
How can I get the correct value? Please help me.
Thank you.
|
|
|
|
|
Hi i have doubt in roles using in vb.net...,
in my sqlserver for a paricular database,i created two application roles..and i given permission for those roles also..,
Now how to tell the roles in vb.net..,
(i.e) how to interact with those roles from vb.net codes...,
I dont know how to interact with those roles...,
so plz give me some idea..,if any sample codes will be there...,
it will be more usefull for me...,
Magi
|
|
|
|
|
i have one column datatype as datetime
i want to reaplce year of all date to 2030
can i use repalce and left functionbut its showing error plz help
|
|
|
|
|
REPLACE and LEFT functions take character expressions.
hope you might have used datetime data directly in the functions.
convert datatime to character and then use it in replace & left
Regards
KP
|
|
|
|
|
You could use:
Column = DateAdd(yy, 2030 - Year(Column), Column)
Wout Louwers
|
|
|
|