Results 1 to 9 of 9

Thread: Help with query

  1. #1
    Join Date
    Feb 2011
    Posts
    9

    Unanswered: Help with query

    I'm wondering if anyone could help spot where I've gone wrong with this query!
    Basically there's a few tables, but it needs to ask for the member_id, then show the results for that member_id.

    Query
    Code:
    PROMPT enter member ID
    ACCEPT member_activities.member_id PROMPT "Enter member ID or 0: "
    SELECT member_activities.member_id, member_activities.activity_ref, activity.activity_type, activity.activity_title, member_activities.date_activity, supervisor.supervisor_name
    FROM member_activities, activity, supervisor
    WHERE member_activities.activity_ref = activity.activity_ref
    OR &member_activities.member_id = 0;
    I'm getting errors

    *invalid variable name
    *valid characters are alphanumerics
    *invalid relational operator

    HELP!
    Thanks for your time.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.
    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.

  3. #3
    Join Date
    Feb 2011
    Posts
    9
    I'm using command promp which doesn't allow copy and paste... :s

    Is there a way to do copy and paste in command prompt. I'm using oracle 11g

  4. #4
    Join Date
    Feb 2011
    Posts
    9
    Okay, here's the query

    Code:
    PROMPT enter member ID
    ACCEPT member_activities.member_id PROMPT "Enter member ID or 0: "
    SELECT member_activities.member_id, member_activities.activity_ref, activity.activity_type, activity.activity_title, member_activities.date_activity, supervisor.supervisor_name
    FROM member_activities, activity, supervisor
    WHERE member_activities.activity_ref = activity.activity_ref
    WHERE member.activities.member_id = &member.activities.member_id
    OR &member_activities.member_id = 0;
    Here's the result

    Code:
    SQL> @C:\Users\Luke\Desktop\Databases\query2
    enter member ID
    SP2-0668: Invalid variable name
    SP2-0669: Valid characters are alphanumerics and '_'
    Enter value for member: 2
    old   4: WHERE member.activities.member_id = &member.activities.member_id
    new   4: WHERE member.activities.member_id = 2activities.member_id
    Enter value for member_activities: 2
    old   5: OR &member_activities.member_id = 0
    new   5: OR 2member_id = 0
    WHERE member.activities.member_id = 2activities.member_id
    *
    ERROR at line 4:
    ORA-00933: SQL command not properly ended
    
    
    SQL>

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    So many error in so few lines. :-(
    SQL*Plus®
    ACCEPT syntax is wrong
    First make it work, then make it fancy. Make it work with hardcoded name, FIRST.
    SELECT can only contain a single WHERE clause.
    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
    Feb 2011
    Posts
    9
    Okay, I understood a little of what you said and tried this query on just 1 table so it's simpler, but it still doesn't work...

    Code:
    CLEAR SCREEN
    PROMPT Enter required member ID
    Accept member_id PROMPT "Enter required member ID"
    Select member_id, member_name
    from group_members
    where group_members = &group_members
    or &group_members = 0;
    Can you see anything wrong with this?

    Errors

    Code:
    Enter required member ID
    Enter required member ID2
    Enter value for group_members: 2
    old   3: where group_members = &group_members
    new   3: where group_members = 2
    Enter value for group_members: 2
    old   4: or &group_members = 0
    new   4: or 2 = 0
    where group_members = 2
          *
    ERROR at line 3:
    ORA-00904: "GROUP_MEMBERS": invalid identifier
    
    SQL>

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> @sample
    SQL> var dept number
    SQL> def dept=10
    SQL> select count(*) from emp where deptno = &dept;
    old   1: select count(*) from emp where deptno = &dept
    new   1: select count(*) from emp where deptno = 10
    
      COUNT(*)
    ----------
    	 3
    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.

  8. #8
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    The error implies that your column name group_members does not exist in table group_members. If you DESCRIBE GROUP_MEMBERS do you see a column name group_members?

  9. #9
    Join Date
    Feb 2011
    Posts
    9
    Got it working! Thanks!
    Last edited by ls7897; 02-23-11 at 17:35.

Posting Permissions

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