Database

Colin Angus Mackay16-Nov-04 5:47

16-Nov-04 5:47

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.

Sebastien Lachance16-Nov-04 7:05

Sebastien Lachance

16-Nov-04 7:05

Thats what i wanted not to happen Frown | :(

I will try every other mean i have before switching to that. Thank you !

Well.. later

Colin Angus Mackay16-Nov-04 9:45

16-Nov-04 9:45

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.

Sebastien Lachance16-Nov-04 9:54

Sebastien Lachance

16-Nov-04 9:54

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

Colin Angus Mackay16-Nov-04 10:04

16-Nov-04 10:04

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.

DELETE X WHERE Y IN (SubQuery) Problem

Tristan Rhodes16-Nov-04 0:55

Tristan Rhodes

16-Nov-04 0:55

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

Re: DELETE X WHERE Y IN (SubQuery) Problem

Colin Angus Mackay16-Nov-04 1:38

16-Nov-04 1:38

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

Re: DELETE X WHERE Y IN (SubQuery) Problem

Tristan Rhodes16-Nov-04 3:17

Tristan Rhodes

16-Nov-04 3:17

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

Re: DELETE X WHERE Y IN (SubQuery) Problem

Colin Angus Mackay16-Nov-04 5:46

16-Nov-04 5:46

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.

How to import both adoce31.dll & adoxce31.dll?

george ivanov16-Nov-04 0:53

george ivanov

16-Nov-04 0:53

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?

Declare Varibales In SQL 2000

Hemant Mane15-Nov-04 19:03

Hemant Mane

15-Nov-04 19:03

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

Sequential Dates and Grouping

Peet Schultz15-Nov-04 2:47

Peet Schultz

15-Nov-04 2:47

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.

Re: Sequential Dates and Grouping

Michael Potter15-Nov-04 3:33

Michael Potter

15-Nov-04 3:33

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.

Re: Sequential Dates and Grouping

Peet Schultz15-Nov-04 4:11

Peet Schultz

15-Nov-04 4:11

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

Re: Sequential Dates and Grouping

Michael Potter15-Nov-04 8:10

Michael Potter

15-Nov-04 8:10

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...),'')

Generate SQL Script ( store procedure problem)

manxx14-Nov-04 8:40

manxx

14-Nov-04 8:40

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 !

View all Tables in database

visitsaadi13-Nov-04 1:33

visitsaadi

13-Nov-04 1:33

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??

Re: View all Tables in database

Colin Angus Mackay13-Nov-04 1:51

13-Nov-04 1:51

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[^]