Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Alias not working for filtered parameter!

    Hello all,

    I got an issue how to make the following query is working:

    ================================================== =======

    SELECT SchoolInfo.Year, (SELECT StatusInfo.StatusText FROM StatusInfo WHERE StatusInfo.StatusId = SchoolInfo.StatusId ) Status
    FROM SchoolInfo
    WHERE SchoolInfo.Year = P_YEAR AND Status = P_STATUS;

    ================================================== =======
    I tried to run above query with real value for Year and Status but it display the error: "Status is invalid identifier". How do I can use ALIAS to filter for parameter. Thanks in advance.
    Last edited by avt2k6; 01-17-12 at 17:39. Reason: Clarification

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish the exact query which you "tried to run above query with real value for Year and Status".

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Two obvious mistakes. The first one: space between "StatusInfo" and ".StatusText" in the second line. The second one: comma behind the "Status," (the second line again).

    You should consider copy/pasting of your SQL*Plus session, as it helps a lot. Don't forget to FORMAT your code and enclose it into the [code] tags to preserve formatting.

  4. #4
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    I have corrected my typo. I added another filter for alias name: Status in order to filter the record by Year and Status. Originally, the query is working well if filter by Year. Here is the query with values:

    SELECT SchoolInfo.Year, (SELECT StatusInfo.StatusText FROM StatusInfo WHERE StatusInfo.StatusId = SchoolInfo.StatusId ) Status
    FROM SchoolInfo
    WHERE SchoolInfo.Year = '2006' AND Status = 'Graduated';


    The error occurred at Status is invalid identifier??? I am new with Oracle, so an idea how to change the code to make it happen. Thanks in advance.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT schoolinfo.YEAR,
           statusinfo.statustext
    FROM   statusinfo,
           schoolinfo
    WHERE  statusinfo.statusid = schoolinfo.statusid
           AND schoolinfo.YEAR = '2006'
           AND statusinfo.statustext = 'Graduated';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Thanks for your detailed code. It works very well.

Posting Permissions

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