|
Is there a way to iterate through each line of a Select Statement ?
In the stored proc ? And do other update on another table while in the loop ?
Thank you very much in advance 
Well.. later
|
|
|
|
|
You can use "cursors" to do that. However, Cursors have a big performance penalty - If you can do the same job without cursors then do that instead.
Do you want to know more?
|
|
|
|
|
Thats what i wanted not to happen  I will try every other mean i have before switching to that. Thank you !
Well.. later
|
|
|
|
|
What is it that you are actually trying to do? It might help to know that in order to come up with a non-cursor based solution.
Do you want to know more?
|
|
|
|
|
I was trying to update the first table and updating the second at the same time, depending on one of the 6 items put in the first. It's hard to explain.
Well.. later
|
|
|
|
|
Perhaps the use of a temporary table during the processing would be useful.
I'm assuming the the filter to determine which rows are updated is non-trivial (i.e. it takes some time or a complex set of joins)
Create a temporary table with the primary key to the FirstTable and foreign key to the second table. Then you populate the temp table with the keys to the rows that will need to be updated.
Make the update on the first table using the temporary table to quickly get at the relevant rows.
Now, using the temporary table again, join with the first table and make the update on the second table.
I don't know if that makes sense, but from what I understand of your scenario it would probably work.
Do you want to know more?
|
|
|
|
|
I've got a Delete command in an SQL statement. And i'm getting an INNER JOIN error, but the sub query works fine on its own. It's a bit baffeling.
'Build SQL to Delete subCategory descriptions, and execute
str_SQL = "DELETE FROM Description WHERE Description.BasicDesc IN " _
& "(SELECT Description.BasicDesc " _
& "FROM ((Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE ((Category.CategoryDescription)='" & GetCurrCategory & "')))"
Any thoughts?
Cheers
Cata
|
|
|
|
|
You have to name the subquery and then use the name you've given the subquery in your INNER JOIN.
FROM (SELECT a, b, c FROM d WHERE e = f) AS subQuery<br />
INNER JOIN otherTable ON subQuery.a = otherTable.a
Do you want to know more?
|
|
|
|
|
I don't fully understand the As SubQuery thing. I'm working in access and not exactly an SQL guru. Why does this query work fine:
str_SQL = "SELECT Description.BasicDesc " _
& "FROM ((Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE ((Category.CategoryDescription)='" & GetCurrCategory & "'))"
but when I try and set an IN into it, it doesn't want to know, even though I use full parenthesis?
Cheers
Cata
|
|
|
|
|
The Catalyst wrote:
I don't fully understand the As SubQuery thing
Maybe it is a SQL Server thing. INNER JOINs onto subqueries don't work in SQL Server unless you name the subquery.
Okay. As it is quite difficult to read the SQL in this form let's extract it:
DELETE FROM Description WHERE Description.BasicDesc IN
(SELECT Description.BasicDesc
FROM ((Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey)
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey
WHERE ((Category.CategoryDescription)='xxxxx')))
Alot of these parethesis are confusing. And quite frankly I'd remove all except the ones shown:
DELETE FROM Description WHERE Description.BasicDesc IN
(SELECT Description.BasicDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey
WHERE Category.CategoryDescription='xxxxx')
I don't know if that makes any difference. The only other way I'd do it is something like this:
DELETE Description
FROM Description
INNER JOIN SubCategory ON SubCategory.SubCategoryKey = Description.SubCategoryKey
INNER JOIN Category ON Category.CategoryKey = SubCategory.CategoryKey
WHERE Category.CategoryDescription='xxxxx')
But that may just work in SQL Server.
Do you want to know more?
|
|
|
|
|
When i import both libraries in Pocket PC project i get some redefinition errors:
<br />
error C2011: '_Collection' : 'struct' type redefinition<br />
error C2011: 'DataTypeEnum' : 'enum' type redefinition<br />
error C2011: 'Properties' : 'struct' type redefinition<br />
error C2011: 'Property' : 'struct' type redefinition<br />
how can be used both of them and avoid conflicts?
were rgrtgrtvrtrt rtrtb brt tyuhjghj hbhbnh hnjm 1234567?
|
|
|
|
|
Hi friends
I make a function in sql 2000. I pass One parameter to that function and i return a table from that function
i have 3 different tables. 1 is master table.
e.g
i have table of doctor i.e master
and another 2 are 1 is specialization and another is status in specialization there are specialities of doctror like GP, Physician etc and in status the status of doctor is Important and very Important etc
I want result like this
Important very Important
GP 4 5
Phy 3 2
first row indicate 4 Gp doctors are important and 5 are very important
i make a function to return this result
but i dont know how many Specialities in table
and how many status
so i want to alter temperory table in the function
create function abc(@docid)
returns table @tab (no varchar(10))
as
begin
declare @spec varchar(20)
declare @vis varchar(20)
declare @no int
declare @ctr int
select @ctr = count(visittypeid) from doc_visitytype where visitytypeid <> 0
while (@ctr >= 0)
begin
select @vis = visittype from doc_visittype where visittypeid = @ctr
ALTER TABLE @tab
ADD @vis varchar(10)
end
return
end
this code add the columns in the temporary table
but @vis is local veriable how can i assign a veriable that it should take value of @vis as a coloumn name
Thank You
|
|
|
|
|
Hi All
We have a table with a persons Employee number (Resource tag) , Date Worked and the Shift Worked on that date.
Like So
resource tag date worked shift type
------------ ------------------------------------------------------ --------------------------------------------------
125197584 2004-07-14 00:00:00.000 Night
125197584 2004-07-15 00:00:00.000 Night
125197584 2004-07-16 00:00:00.000 Night
125197584 2004-07-17 00:00:00.000 Night
125197584 2004-07-18 00:00:00.000 Morning
125197584 2004-07-19 00:00:00.000 Morning
125197584 2004-07-20 00:00:00.000 Morning
125197584 2004-07-21 00:00:00.000 Morning
125197584 2004-07-22 00:00:00.000 Morning
125197584 2004-07-23 00:00:00.000 Morning
125197584 2004-07-24 00:00:00.000 Morning
125197584 2004-07-25 00:00:00.000 Night
125197584 2004-07-26 00:00:00.000 Night
125197584 2004-07-27 00:00:00.000 Night
125197584 2004-07-28 00:00:00.000 Night
125197584 2004-07-29 00:00:00.000 Night
125197584 2004-07-30 00:00:00.000 Night
125197584 2004-07-31 00:00:00.000 Night
125197584 2004-08-01 00:00:00.000 Morning
125197584 2004-08-02 00:00:00.000 Morning
125197584 2004-08-03 00:00:00.000 Morning
125197584 2004-08-04 00:00:00.000 Morning
125197584 2004-08-05 00:00:00.000 Afternoon
125197584 2004-08-06 00:00:00.000 Morning
125197584 2004-08-07 00:00:00.000 Morning
125197584 2004-08-08 00:00:00.000 Morning
125197584 2004-08-09 00:00:00.000 Morning
125197584 2004-08-10 00:00:00.000 Morning
125197584 2004-08-11 00:00:00.000 Morning
125197584 2004-08-12 00:00:00.000 Morning
125197584 2004-08-13 00:00:00.000 Morning
125197584 2004-08-14 00:00:00.000 Morning
125197584 2004-08-15 00:00:00.000 Night
125197584 2004-08-16 00:00:00.000 Night
I need to find all the dates where the shift type has changed.
The only way that I have been able to figure out how to do this is to left join the table on itself , adding 1 day to the date worked in the right hand table and then selecting the differences.
The problem with this is that I may have up to 20 thousand people in the table and the left join is time consuming. The other problem is that the very first record has to be retrieved with a separate select.
Thanks
Regards
Peet Schultz
South Africa
If anybody wants I can mail a script to create the table and data.
|
|
|
|
|
You can try something like:
<code>SELECT
wt.DateWorked
FROM
WorkTable wt
WHERE
wt.ShiftType <>
(SELECT
ShiftType
FROM
WorkTable
WHERE
ResourceTag = wt.ResourceTag AND
DateWorked = (SELECT
MAX(DateWorked)
FROM
WorkTable
WHERE
ResourceTag = wt.ResourceTag AND
DateWorked < wt.DateWorked))
The table will need the following index to work quickly:
<code>CREATE INDEX ShiftTest ON WorkTable
(
ResourceTag,
DateWorked
)
This assumes that no worker will work more than 1 shift per day.
|
|
|
|
|
Thanks
This looks good, only problem is does not return the first record either.
I do have a way of getting past that and will run a couple of benchmarks to check the speed of this against the left join
Regards
Peet
YASP
|
|
|
|
|
Didn't think about the first record.
Just wrap the correlated Select in an ISNULL() that will return an invalid ShiftType:
wt.ShiftType <> ISNULL((Select...),'')
|
|
|
|
|
Hello! I am facing a problem while generating sql script from Enterprise Manager for some reason there are storeprocedure which are left while generating the sql script when I checked the difference between the other store procedures and the one which are left and are not being copied to the sql script , I found the only is that for some reason these store procedures are of "SYSTEM" TYPE , I mean in the store procedure area it shows you " Store procedure name , Owner and then Type so the Type for these are shown as system althout these arent system but created by us and their own is of the user we login to the database with and we have all the premission to change them . Please some one help me with this issue as I cant find a solution .
Thanks !
|
|
|
|
|
hi dear...
how i can view all of tables that there are in database but i dont know their names ?
is it possible with SQL commands??
or view just name of tables that there are in databse..
infact get tables names...
is it possible??
|
|
|
|
|
For a list of databases on the current server:
select [name] from master.dbo.sysdatabases
For each database you can then do
select [name] from sysobjects where type='U'
You can find documentation for both in MSDN:
* sysdatabases[^]
* sysobjects[^]
Do you want to know more?
|
|
|
|
|
Hi! I want to select only 10 rows from a dataTable each time, just as a paging from a database("SELECT TOP 10 FROM orders WHERE ID <100 ORDER BY ID"). for example:
DataTable myTable = new DataTable("orders");
DataRow[] rows = myTable.Select("TOP 10 ID < 100");
1)How can I write the correct statement.
2)Can I ues "TOP" key word or there's other way to do it?
Thank you very much.
|
|
|
|
|
Hi everyone
hope someone can help on a quite simple query. I have a stored procedure that simply returns the row count in a table (code at end of message) and some c# code that says if the row count is greater than 1000 do one thing else do something else (poor pseudocode I know, I apologise).
What I want to do is pass the value from the SP back to the main program. Can it be done?
Hope someone can help,
thanks in advance
Scott
OracleCommand cmdImportCount = new OracleCommand();
cmdImportCount.CommandText = "tv.get_clock_number_import_count";
cmdImportCount.CommandType = CommandType.StoredProcedure;
cmdImportCount.Connection = con;
OracleParameter import_count = new OracleParameter();
import_count = cmdImportCount.Parameters.Add("import_count",OracleType.Number);
import_count.Direction = ParameterDirection.Output;
int import_count_limit = 1000;
int current_total = Convert.ToInt32(cmdImportCount.Parameters["import_count"].Value);
if(current_total < import_count_limit)
{
rest of code
}
PROCEDURE get_clock_number_import_count (import_count OUT NUMBER)
IS
BEGIN
SELECT count(1)
INTO import_count
FROM tv_clock_number_import;
END get_clock_number_import_count;
|
|
|
|
|
First a disclaimer: I've never used Oracle.
One possiblility is make your query something like
SELECT count(*) FROM ...
and removing the output parameter. [see footnote]
Then in the .NET Application you can use OracleCommand.ExecuteScalar()[^] method to get the value back.
By the way, to do know that the code you supplied doesn't actually run the query, it just sets it up. So... anyway... you would change the line that start int current_total = to read
int current_total = (int)cmdImportCount.ExecuteScalar(); and remove the references in your code to the parameters.
Does this help?
Footnote: I don't know what the count(1) does in Oracle, so I'm presuming that it does the same thing as count(*) in SQL Server
Do you want to know more?
|
|
|
|
|
Hi,
thanks for replying. Got it working now.
I didnt supply the code that fires the event as its only a click on a menu item, didnt think it was that relevant to why the code wasnt working as hoped. Maybe I should have done and will do in the future.
Thanks for your help, much appreciated 
Scott
|
|
|
|
|
 Hi,
I am trying to tell MSSQL Server running on a remote computer on LAN to back up a database on my computer. This is done through a application written in c++.
The Idea is to back up the database on the computer where the server is located as well as on the computer running the app.
What I did before this dual requirement was presented was create a stored procedure to backup the database which took the database name and the location to be backed up to as arguments.
It works well as SQL Server backs it on the local drive specified the path.
No if I send in the network address of my computer to do the same, SQL SERVER error log tells me that operating system denied permission ( which is what will be expected ).
I send in a string as \\computername\driverletter$\path\filename.zip
How can I modify this to include the user name and passwrod so that SQL SERVER can back it up on my computer without security problems???
I have tried net use kind of syntax but it does not help.
Please help!! this is really important.
Thanks In advance.
|
|
|
|
|
This high lighted connection string[ without the .2 after MSOLAP] works in VB 6.0, but not in ADO.NET. any comments?
----------------------------------------------
Exception Details: System.Runtime.InteropServices.COMException: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Source Error:
Line 48: txtSrc = txtSrc + "NON EMPTY [Store].[Store Name] on rows"
Line 49: txtSrc = txtSrc + " from Warehouse"
Line 50: olapCat.ActiveConnection = "Provider=MSOLAP.2;Data Source=xphtek;Initial Catalog=FoodMart 2000;"
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
