Query Problem - Please Help

  • Hello Team,

    I am working at a school district in northern California. I'm trying to create a data extract from a product called PowerSchool. The underlying database is Oracle, which I connect to via Oracles SQL Developer tool. The query I have works well with 1 period/course, as at an elementary school, or one course from a Middle School.

    However I am being asked for course, section, and period information for all six periods for each student at both of our middle schools. On top of that we have courses that meet on A days, B days, and A-B days. To add to the problem, the application Im pulling data for needs a unique line for each student for each course in addition to the demographic information. Which I think is odd but thats what they want.

    So far my limited SQL skill set is not up to this part of the task. Any insight into this problem would be greatly appreciated. My query is very basic (don't laugh) but works for one course at a time, and currently looks like the attachment for each elementary school. I use the UNION command to pull the schools together in one query.

    I've attached a text file of the basic query for one school.

    Any help would be appreciated.

    Let me know what you think.

    - Sez

    =====================
    No matter where you go
    There you are....

  • Hi, IM going to make an aussmption here that each time you run the script for each school you change

    WHERE students.schoolid=6049142

    Have you tried using the IN clause?

    WHERE students.schoolid in (6049142,6049143,6049144)

    It would be easier to help you if you could provide examples of tables and represenative data

    ***The first step is always the hardest *******

  • Yes, that does work better. It was actually suggested to me by someone else who felt I was using too many UNION commands. Additionally once I removed the line on "period/expression" I got exactly what I wanted.

    However there is a strange thing happening that I don't understand. I use Oracles SQL Developer to create and test scripts. When I load and run the attached script I get a "Java Heap Error", or it says Oracle is in an unstable state and fails.

    However when I copy the exact same code, and paste it into a blank script in SQL Developer it runs fine.

    Any ideas what this means?

    - Sez

    =====================
    No matter where you go
    There you are....

  • As you might have noticed, this is a MS SQL Server forum.

    Maybe you'll find better answers on a Oracle forum... 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply