Re: Recursive drill down query? - Database Discussion Boards

silvioribeiro19-Jan-07 12:20

19-Jan-07 12:20

Thanks so much Rob, your answer was very helpful as it covered everything that wasn’t clear to me. I started to learn Visual Basic about 2 years ago and found out that without knowing at least the basics of database design everything can be a lot harder.
Also, the fact that I don’t work as a programmer, and don’t have anyone to ask questions doesn’t help me at all.
I’m constantly trying to learn everything on my own. Not easy!

Thanks a lot,

Silvio.

http://www.silvioribeiro.com

Re: Database Design and Normalization

Chris Meech19-Jan-07 16:10

Chris Meech

19-Jan-07 16:10

I have a database that has the final three combined in a table called Jurisdiction. Every entry in the Address table has a reference to an entry in the Jurisdiction table, too. But it depends upon the usage. In my case I have other uses for jurisdictions, besides addresses. Things like holidays, for example.

Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]

Recursive drill down query?

Marc Clifton19-Jan-07 7:33

Marc Clifton

19-Jan-07 7:33

I have table "tasks" that is recursive--tasks can have subtasks. The table has an ID and a ParentTaskID.

Is there a way to write a query to recurse through all the subtasks of a particular task?

Thanks!

Marc

Thyme In The Country

People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

Re: Recursive drill down query?

Kschuler19-Jan-07 8:33

Kschuler

19-Jan-07 8:33

If I understand your problem correctly, than the only way I can think of to do this would be in your code...not in a single SQL statement. You would have to put the SQL select in a loop that would keep checking for a record until it finds one that doesn't have a parent task.

Hope this helps.

Re: Recursive drill down query?

Pete O'Hanlon19-Jan-07 9:13

Pete O'Hanlon

19-Jan-07 9:13

Marc

Is this a SQL Server 2005 table? I ask this because you can do some pretty amazing things with something called a Common Table Expression (CTE).

If not, there are ways that you can achieve this using ordinary SQL, but they aren't pretty.

Let me know which way, and I'll see if I can put an example together for you.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Re: Recursive drill down query?

Marc Clifton19-Jan-07 9:29

Marc Clifton

19-Jan-07 9:29

Pete O`Hanlon wrote:
Is this a SQL Server 2005 table?

Yes it is. Smile | :)

Pete O`Hanlon wrote:
and I'll see if I can put an example together for you.

Thanks! In the meantime, I'll research CTE!

Marc

Thyme In The Country

Re: Recursive drill down query?

Pete O'Hanlon19-Jan-07 9:55

Pete O'Hanlon

19-Jan-07 9:55

Without knowing the specifics of what you are trying to do, you could try something along the lines of:

WITH TaskSearch (TaskID, TaskName, TaskParent, Level)
AS
(
  SELECT ID, Task, ParentTaskID, 0 AS Level
  FROM Tasks WHERE ParentTaskID IS NULL
  UNION ALL
  SELECT ID, Task, ParentTaskID, Level + 1
  FROM Tasks 
  INNER JOIN TaskSearch 
  ON TaskID = ParentTaskID

)

SELECT TaskID, TaskName, TaskParent, Level
FROM TaskSearch

Sorry - I've done this from memory so it might not be 100%.

Re: Recursive drill down query?

Marc Clifton19-Jan-07 10:31

Marc Clifton

19-Jan-07 10:31

Pete O`Hanlon wrote:
Sorry - I've done this from memory so it might not be 100%.

Looks great! I was fussing with the Microsoft example and fumbled with the inner join where (had the child and parent ID comparison backwards with relation to the "from" and "inner join" tables).

Thanks so much for showing me about CTE. Very cool!

Marc

Thyme In The Country

Re: Recursive drill down query?

Jon Sagara19-Jan-07 9:34

Jon Sagara

19-Jan-07 9:34

You can write recursive stored procedures. Will that help, or do you need it to be a single query?

Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash

Blog | Site | Articles

SQL Server table names has (dbo) as part of name

dptalt19-Jan-07 3:26

dptalt

19-Jan-07 3:26

I have a Microsoft SQL Server database that was moved. When I looked at the tables in the Server Explorer there are 2 sets of the same tables 1 set having the text '(dbo)' tacked on to the end of the table names. Why is this? Am asking because the website that connects to the database updates the tables with '(dbo)' while the admin page querys the tables without '(dbo)'. Can I delete one of the sets?

Re: SQL Server table names has (dbo) as part of name

Bassam Saoud20-Jan-07 0:00

Bassam Saoud

20-Jan-07 0:00

dbo means data base owner, in short its the user who owns the tables.if you created the tables with user name "x" the resulting table will be referenced by x.tablename

You can change the owner of an object by using stored procedure sp_changeobjectowner

dptalt wrote:
Can I delete one of the sets?

Well as long as you have the right permission in your web site to access thes tables you can...

What You See Is What You Get (WYSIWYG) .... doesn't work with SQL Reporting Services

Member 331398919-Jan-07 3:05

Member 3313989

19-Jan-07 3:05

I did the following steps:
1. Created a report using SQL Reporting Services. (OK)
2. Deployed the project (ABC) into the ServerX. (OK)
3. Opened the SQL Reporting Services using the Report Manager on localhost of the ServerX. (OK)
4. Opened the ABC folder. Files deployed. (OK)
5. Created two subscriptions. One (Service1) for printing to the Printer. One (Service2) for saving into a file. (OK)
6. Opened the Service1. In the View Tab, after entering the Report Parameters, clicked on "View Report". (OK)
7. I expected view to show what I viewed in the Project (ABC). (NOK - It printed a wrong format. Alignment was completely different)
8. Tried to export to a TIFF file expecting to see the same view as I saw in the Project (ABC). (NOK - It printed a wrong format. Alignment was completely different)
9. Tried to export to a PDF file expecting to see the same view as I saw in the Project (ABC). (NOK - It printed a wrong format. Alignment was completely different)
10. In the subscriptions, I ran the Service1 to print the report from the SQL Reporting Services directly onto the Printer. (NOK - It printed a wrong format. Alignment was completely different)

Is this an expected issue with SQL Reporting Services which incase doesnt make any sense to use it. Or it is an issue with my code. All I assumed that I should expect from SQL Reporting Services is: if I create a Report and I preview the Report, I should get a WYSIWYG formats.

Half the way through our project, we are completely stuck up. SOS!!!

Comparing Dates Problem:

Shahzad.Aslam19-Jan-07 0:56

Shahzad.Aslam

19-Jan-07 0:56

Hi There are two date fields in the table and i want to select Maximum date field from two fields i m trying to query like this.
Table Fileds:
UserID Int
LastDDTxn Datetime
LastCardTxn Datetime

SELECT LAST_TRAN_DATE=
(CASE WHEN LastCardTxn>LastDDTxn THEN LastCardTxn
ELSE WHEN LastDDTxn>LastCardTxn THEN LastDDTxn
ELSE NULL
END) AS Last_Tran_Date
FROM USERHISTORY

The Error is showing that
Incorrect syntax near the keyword 'WHEN'

SHAHZAD

Re: Comparing Dates Problem:

WoutL19-Jan-07 2:21

WoutL

19-Jan-07 2:21

Select LAST_TRAN_DATE = Max([Date])
From(
   Select [Date] = LastDDTxn
   From UserHistory
   Union
   Select [Date] = LastCardTxn
   From UserHistory
) Dates

Wout Louwers

Re: Comparing Dates Problem:

Shahzad.Aslam19-Jan-07 18:41

Shahzad.Aslam

19-Jan-07 18:41

Thanx! Its working fine.

Shahzad

Re: Comparing Dates Problem:

Mike Dimmick19-Jan-07 2:44

Mike Dimmick

19-Jan-07 2:44

SELECT LAST_TRAN_DATE =
   (CASE
       WHEN LastCardTxn > LastDDTxn THEN LastCardTxn 
       <s>ELSE</s> WHEN LastDDTxn > LastCardTxn THEN LastDDTxn
       ELSE NULL
   END) AS Last_Tran_Date
FROM USERHISTORY

The first, crossed-out, ELSE is incorrect - ELSE is only used for the 'default' case i.e. when no WHEN clauses match. Therefore, when the query parser encounters ELSE, it's expecting an expression, and reports that WHEN is an error.

Stability. What an interesting concept. -- Chris Maunder

need query

Tauseef A19-Jan-07 0:56

Tauseef A

19-Jan-07 0:56

hi all

please tell me the query

actually i have a table named history from which i want to get first and last record of a particualr person based on a date given in the query parameter

thanks

hello

hello

Re: need query

Pete O'Hanlon19-Jan-07 2:32

Pete O'Hanlon

19-Jan-07 2:32

Not only cross posting, but reposting. Do you really think we are so stupid as to not notice this?

OK - some guidelines. Tell us what the table structure is and the query criteria you have to work with. Then, give us some idea as to what you have tried so we can see that you aren't just trying to get us to do your homework for you. Finally, pick a meaningful subject. "Re: need query" is not going to get your question answered.

hi its very urgent

Tauseef A19-Jan-07 0:11

Tauseef A

19-Jan-07 0:11

hi all

please tell me the query

actually i have a table named history from which i want to get first and last record of a particualr person based on a date given in the query parameter

thanks

hello

Re: hi its very urgent

Guffa19-Jan-07 0:54

Guffa

19-Jan-07 0:54

Please don't cross post.

---
Year happy = new Year(2007);

query to rename a field of an existing table in SQL Server??

Saira Tanwir18-Jan-07 21:48

Saira Tanwir

18-Jan-07 21:48

hello
can anyone plz tell me the query to rename an existing field in a table???? Sigh | :sigh:

Saira

Re: query to rename a field of an existing table in SQL Server??

andyharman19-Jan-07 0:10

andyharman

19-Jan-07 0:10

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

Re: query to rename a field of an existing table in SQL Server??

Elina Blank19-Jan-07 3:14

Elina Blank

19-Jan-07 3:14

SQL Server:

exec sp_rename @objname='TableName.ColumnName', @newname='NewColumnName'

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

querry

pradeep kulkarni18-Jan-07 20:03

pradeep kulkarni

18-Jan-07 20:03

:-Dwrite querry to compare row in table A and in table B.

for example
if in table A contains row values as a 12
b 14
c 15

if in table B contains row values as a 13
b 21
c 43

compare table A's row c with table B's row c.

pradeep kulkarni

Re: querry

Christian Graus18-Jan-07 22:10

Christian Graus

18-Jan-07 22:10

The questions you're asking are very easy, and you're obviously typing in your homework. Please try yourself, if you can't work this out, you should quit your course.

Christian Graus - C++ MVP

'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert

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