Welcome Guest.   Make a donation to an author on the site December 21, 2014, 08:05:37 PM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2011! Download 30+ custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: SQL query newbie: Help me work through my first project?  (Read 1578 times)
superboyac
Charter Member
***
Posts: 5,794


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« on: March 01, 2011, 10:13:44 AM »

Hi everyone, I've gotten my hands into programming now after not doing anything like this for maybe 10 years now.  I'm working with an Oracle database and I'm just starting to learn how to run SQL queries.  I've gotten the basics so far of the select, where functions.  But I'm trying to do a little more fancy things, and it's becoming difficult.  So I wouldn't mind getting coached throughout this thread.  Yeah?

First challenge:
I've been able to write a simple query using and/or statements.  Now, I want to group everything in a particular way to reflect the hierarchy that the database is representing.  So I need to somehow figure out how to group things according to the proper parents/children.  What I'm doing now is trying to figure out which columns control the hierarchy, because it's not clear, and I can't ask anyone here.
Logged

mouser
First Author
Administrator
*****
Posts: 33,793



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #1 on: March 01, 2011, 10:17:15 AM »

I think you might need to be a little more specific and maybe give some example data.

And furthermore, when you talk about grouping and hierarchy it sounds like you are talking about how to organize the data after it comes back from sql in whatever other language you are using -- since sql really will only ever return rows of flat data.  Or maybe you're talking about how to pull data from multiple tables based on what they call "foreign keys", where you are combining rows from different tables based on matching column values.  Either way, i think you'll need to be more specific.
Logged
superboyac
Charter Member
***
Posts: 5,794


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #2 on: March 01, 2011, 10:19:43 AM »

Sure, I just don't know how confidential everything is.  here's an example:
Quote
and DESCRIPTION like '%{%}%'
I'm trying to add a way for the query to return matches that only contain the bracket symbols "{" and "}" in the description column.  I have entries that have a code inside brackets like "{something here} some text", and I only want those returned.  So the brackets are the distinguishing feature.  But I just ran the query, and that and statement above didn't do anything.
Logged

superboyac
Charter Member
***
Posts: 5,794


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #3 on: March 01, 2011, 10:28:12 AM »

Ok, I got the above question resolved.  No questions for now.
Logged

superboyac
Charter Member
***
Posts: 5,794


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #4 on: March 08, 2011, 04:21:23 PM »

I'm having a real hard time trying to get a query to return some kind of outline numbering.  So, I've been able to arrange my query results in the proper hierarchical order using the Oracle hierarchical functions like:
Start with
connect by prior

So that's good.  Now I need to add a column showing the outline numbering, such as:
1
1.1
1.2
1.2.1
1.2.2
etc.

How do i do that?  I've tried using this:
SYS_CONNECT_BY_PATH

but the hard part is trying to get it to COUNT the items as it goes through the hierarchy.  The closest I've come to using the connect by path command above is to show the path to of the row in the hierarchy.  So I can get it to show something like:
Parent/Child1/Child2
But I'd like it to do the "1.2.1" kind of numbering instead.  Any thoughts?  I'm missing a way to make it count stuff.
Logged

CWuestefeld
Supporting Member
**
Posts: 944



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #5 on: March 26, 2011, 10:11:51 AM »

You've chosen just about the hardest thing to get a relational database to do ohmy. They really don't lend themselves well to hierarchical data, although it is possible with some work.

There are a number of ways to attack it, but it's highly dependent on the specifics of the problem, and varies greatly depending on the database engine being used (I'm a SQL Server expert, not Oracle).

My recommendation is to get a copy of Joe Celko's Trees and Hierarchies. He explains all your options, with pretty good examples.
Logged



Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.036s | Server load: 0.07 ]