Sequential Dates and Grouping - Database Discussion Boards

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.

Do you want to know more?

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

Colin Angus Mackay

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

Do you want to know more?

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

Colin Angus Mackay

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.

Do you want to know more?

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

Colin Angus Mackay

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

Do you want to know more?

Select Top 10 from a DataTable ???

luckyShek12-Nov-04 9:56

luckyShek

12-Nov-04 9:56

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.

Stored Procedures

Anonymous12-Nov-04 6:02

Anonymous

12-Nov-04 6:02

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;

Re: Stored Procedures

Colin Angus Mackay12-Nov-04 6:41

Colin Angus Mackay

12-Nov-04 6:41

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?

Re: Stored Procedures

Anonymous14-Nov-04 22:38

Anonymous

14-Nov-04 22:38

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 Wink | ;)

Scott

How to back up database on remote computer??

venadder12-Nov-04 5:07

venadder

12-Nov-04 5:07

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.

Connection String for Connection to OLAP Server

mysorian11-Nov-04 16:50

mysorian

11-Nov-04 16:50

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;"

Re: Connection String for Connection to OLAP Server

David Salter11-Nov-04 23:47

David Salter

11-Nov-04 23:47

You need to set the ActiveConnection to an ADODB.Connection object rather than to a string.

The following site may help.

http://www.devhood.com/Tutorials/tutorial_details.aspx?tutorial_id=640

Store your favourite bookmarks online: my-faves.co.uk

From a Blob to some type of image in Java

Red Sunday11-Nov-04 9:39

Red Sunday

11-Nov-04 9:39

Ok, to create an easy user interface I have created a Microsoft Access database that will store images in an ole object for a jpeg. Laugh | :laugh:

They can actually drag and drop a jpeg and it will convert to an ole object. Now in actual ODBC Java code, I am getting this same OLE object in the form of a blob Java object. How do I type caste this blob object into some type of image that I can display on the screen in the form of a jpeg? Confused | :confused:

Thank you so much,
redsuday69

-----------------
http://www.zachcalvert.com

Inserting

eggie511-Nov-04 8:45

eggie5

11-Nov-04 8:45

I have the following text...

QUALCOMM's

How can I insert this into my sql database?

When I try to do it now I get an SqlException...

---------------------------
Sql Error
---------------------------
ERROR 
System.Data.SqlClient.SqlException: Line 2: Incorrect syntax near 's'.

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at XmlDataTool.XmlParser.Emit(String sqlString) in d:\my documents\visual studio projects\xmldatatool\xmlparser.cs:line 422
---------------------------
OK   
---------------------------

When I remove all the ' I can insert the text, but they seem to be messing it up. What does my column have to be to accept ''s? Right now it is a nvarchar.... because that seemed like the most flexable as to what it will accept...

/\ |_ E X E GG

Re: Inserting

Colin Angus Mackay11-Nov-04 9:12

Colin Angus Mackay

11-Nov-04 9:12

Well, without you suppling the SQL statement you are using it makes it difficult.

That said, are you using a parameterised query? Or are you injecting values into a SQL String to form the command?

Do you want to know more?

Re: Inserting

eggie511-Nov-04 9:15

eggie5

11-Nov-04 9:15

this is my exact sql string

INSERT INTO DEVELOPER (OBJID,LEGAL,DID,ISV,MONTH_YEAR,GMT) VALUES ('1000','\"The Developer Extract XML to you \"as-is.\" asdf makes no representation or warranty to you that the data will be complete, error free, fit for any particular purpose, or provided on an ongoing basis.\nThis information is fsda's .\"','DEVEXTRACT.Toasdfne Networks.082004.xml','Tourmaline Networks','08/01/2004','09/10/2004 21:12:21');

How do I insert the '?

/\ |_ E X E GG

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.