Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: Query Problems

  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Query Problems

    I have this query:

    Code:
    SELECT EM_KEY, EM_DPKEY, EM_TMKEY, EM_LAST_NAME, 
    EM_FIRST_NAME, ATX_DATE
    FROM ATX, EM
    WHERE (ATX_EMKEY = EM_KEY) AND ATX_DATE BETWEEN TO_CHAR('25-NOV-2002') AND
    TO_CHAR('08-DEC-2002') ORDER BY EM_TMKEY, EM_DPKEY,
    EM_LAST_NAME, EM_FIRST_NAME
    That puts the information in a proper sorting. Now, I need to take the results of that query and get only the distinct EM_KEY records in the same order as they are in the above query. I tried:

    Code:
    SELECT DISTINCT EM_KEY FROM (SELECT EM_KEY, EM_DPKEY, EM_TMKEY, EM_LAST_NAME, 
    EM_FIRST_NAME, ATX_DATE
    FROM ATX, EM
    WHERE (ATX_EMKEY = EM_KEY) AND ATX_DATE BETWEEN TO_CHAR('25-NOV-2002') AND
    TO_CHAR('08-DEC-2002') ORDER BY EM_TMKEY, EM_DPKEY,
    EM_LAST_NAME, EM_FIRST_NAME)
    but that doesn't seem to keep the EM_KEY records in the proper order. I tried to move the ORDER BY clause outside the INNER SELECT but I get an error:

    Code:
    TO_CHAR('08-DEC-2002') ) ORDER BY EM_TMKEY, EM_DPKEY,
                                      *
    ERROR at line 5:
    ORA-01791: not a SELECTed expression
    The error makes sense but I still need to be able to get the proper records in the proper order. Can someone help me out? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    P.S. - The "*" in the above code posting is under the "EM_TMKEY".
    Nothing better than a good ride.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
      ATX_DATE BETWEEN TO_DATE('25-NOV-2002''DD-MON-YYYY') AND
                                     
    TO_DATE('08-DEC-2002''DD-MON-YYYY'
    you want to find the between for those dates, so you need to_date.

    at the top of your query, try a

    select emt_key, count(*)

    then at the end group by emt_key
    Last edited by The_Duck; 09-04-03 at 17:46.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Bah!

    What am I thinking?

    SELECT distinct EM_KEY
    FROM ATX, EM
    WHERE (ATX_EMKEY = EM_KEY) AND
    ATX_DATE BETWEEN TO_CHAR('25-NOV-2002') AND
    TO_CHAR('08-DEC-2002')
    ORDER BY
    EM_TMKEY,
    EM_DPKEY,
    EM_LAST_NAME,
    EM_FIRST_NAME
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I get the same error I get from my first post. I'm trying to order by items that aren't selected in my query. Got anymore ideas? Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Try this

    SELECT DISTINCT EM_KEY FROM (SELECT EM_KEY, EM_DPKEY, EM_TMKEY, EM_LAST_NAME,
    EM_FIRST_NAME, ATX_DATE
    FROM ATX, EM
    WHERE (ATX_EMKEY = EM_KEY) AND ATX_DATE BETWEEN TO_CHAR('25-NOV-2002') AND
    TO_CHAR('08-DEC-2002') ORDER BY EM_TMKEY, EM_DPKEY,
    EM_LAST_NAME, EM_FIRST_NAME) ORDER BY EM_TMKEY, EM_DPKEY, EM_LAST_NAME, EM_FIRST_NAME

    This should work as the subquery is returning all the columns and should be able to perform the order in a similar manner.

    Thanx and Regards
    Aruneesh

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    That also results in the same error as my first post. Besides...in my first post it showed that I had tried that. Thanks though and if you think of something else, let me know. Thanks, Jeremy
    Nothing better than a good ride.

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Cant result in an error

    Hi

    I did see in the first post itself, but it seems there is a syntax problem there. Nothing else.
    You wont be voilating any Oracle rules, if you perform the ORDER by twice.

    Could u please paste the exact SQL u are using which is resulting in the error.

    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Here is the code I'm using:

    Code:
    SELECT DISTINCT EM_KEY FROM (SELECT EM_KEY, EM_DPKEY, EM_TMKEY, EM_LAST_NAME, 
    EM_FIRST_NAME, ATX_DATE
    FROM ATX, EM
    WHERE (ATX_EMKEY = EM_KEY) AND ATX_DATE BETWEEN TO_CHAR('25-NOV-2002') AND
    TO_CHAR('08-DEC-2002') ORDER BY EM_TMKEY, EM_DPKEY,
    EM_LAST_NAME, EM_FIRST_NAME) ORDER BY EM_TMKEY, EM_DPKEY, EM_LAST_NAME, EM_FIRST_NAME
    And here is the full output from SQL*Plus:

    Code:
    SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 4 16:08:35 2003
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    Connected.
    EM_LAST_NAME, EM_FIRST_NAME) ORDER BY EM_TMKEY, EM_DPKEY, EM_LAST_NAME, EM_FIRST_NAME
                                          *
    ERROR at line 6:
    ORA-01791: not a SELECTed expression
    I hope this helps. Thanks, Jeremy
    Nothing better than a good ride.

  10. #10
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Try this

    Jeremy,

    Try this ... unfortunately I dont have a DB to try it myself

    SELECT DISTINCT EM_KEY FROM (SELECT EM_KEY, EM_DPKEY, EM_TMKEY, EM_LAST_NAME,
    EM_FIRST_NAME, ATX_DATE
    FROM ATX, EM
    WHERE (ATX_EMKEY = EM_KEY) AND ATX_DATE BETWEEN TO_DATE('25-NOV-2002','DD-MON-YYYY') AND
    TO_DATE('08-DEC-2002','DD-MON-YYYY') ORDER BY EM_TMKEY, EM_DPKEY,
    EM_LAST_NAME, EM_FIRST_NAME) ORDER BY EM_TMKEY, EM_DPKEY, EM_LAST_NAME, EM_FIRST_NAME


    Thanx and Regards
    Aruneesh

  11. #11
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Same exact error in same exact place. I see why I'm getting it but all I want is the EM_KEY in the proper order and I can't add those items in my main query. Got any ideas? Thanks, Jeremy
    Nothing better than a good ride.

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Guys,

    Excuse if I'm wrong but I just got back from the pub ... it's a perk of the job :-) An old 'C' trick is to count brackets. Start with zero and read your code, for every opening bracket add one, for every closing bracket subtract one.

    You appear to be missing a closing bracket (or the beer glasses are talking)!

    Hth
    Bill

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I just counted brackets and the number of closing and opening brackets match so that isn't the problem. Can anyone shed some light on this? Thanks, Jeremy
    Nothing better than a good ride.

  14. #14
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow RowNum

    Hi

    I am not really sure what is going wrong here.
    Why dont u try using "rownum" in the inner query.

    Thanx and Regards
    Aruneesh

  15. #15
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Aruneesh,
    What's going on is that my inner query gives me 6 pieces of information (ATX_DATE, EM_KEY, EM_DPKEY, EM_TMKEY, EM_LASTNAME and EM_FIRSTNAME) sorted in a particular order (EM_TMKEY, EM_DPKEY, EM_LASTNAME, EM_FIRSTNAME) and everything is great. I get about 898 records sorted correctly. What I need to do is query the result of the query mentioned and get only the DISTINCT EM_KEY from the results but in the same order as when the query mentioned put them in. Does this make better sense? Now...you have given me ideas to try but each one fails with the same problem and that is that I'm trying to order by a column that I'm not selected in the outer query. This is true and I see an easy way to fix it but it will not result in what I need. Does this shed some light on things? Thanks, Jeremy
    Nothing better than a good ride.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •