Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2013
    Posts
    36

    Unanswered: Decode SQL..multiple tables.

    I have a simple decode that works as shown below;

    SELECT membership_type,
    DECODE(membership_type, 'Gold', 'First',
    'Silver', 'Second',
    'Bronze', 'Third',
    'Gateway') Result
    FROM m_membership_type


    What I am trying to do is simply output some columns from another table as well, so I want to output customer_id from my m_customer table, below is what I figured the code is;

    SELECT membership_type, customer_id
    DECODE(membership_type, 'Gold', 'First',
    'Silver', 'Second',
    'Bronze', 'Third',
    'Gateway') Result
    FROM m_membership_type, m_customer

    But I get the: ORA-00923: FROM keyword not found where expected error?
    Still learning..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    not as above, but as below
    Code:
    SELECT membership_type, 
           customer_id, 
           Decode(membership_type, 'Gold', 'First', 
                                   'Silver', 'Second', 
                                   'Bronze', 'Third', 
                                   'Gateway') Result 
    FROM   m_membership_type, 
           m_customer
    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
    Oct 2013
    Posts
    36
    Thank you sir! Didn't realise it has to be on the next line!
    Still learning..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Didn't realise it has to be on the next line!
    the new line is/was not the fix.

    It is a challenge to see an error of omission.
    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.

  5. #5
    Join Date
    Oct 2013
    Posts
    36
    Indeed, it was my error in not knowing that.
    Still learning..

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what is/was wrong with your original code?
    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.

  7. #7
    Join Date
    Oct 2013
    Posts
    36
    Well at the minute there is no join between the two tables in my code,
    so the output is multiplying every column from both tables.
    I can't use query builder on my laptop so once I try this at Uni tomorrow,
    I will know for sure. It's confusing..for me.
    Still learning..

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by london34 View Post
    Well at the minute there is no join between the two tables in my code
    True, but that doesn't cause any syntax issues, it'll just create a cartesian product.
    so the output is multiplying every column from both tables.
    It is more like "every row in table1 is joining to every row in table 2.
    I can't use query builder on my laptop so once I try this at Uni tomorrow,
    Just another reason to start learning to write code rather than relying on query builder.
    I will know for sure.
    You will still have the same issue, i.e. the query builder will build valid code (the same as anacedent has done for you) and you will have to compare it to your original code. The query builder won't help you with the comparison, and you already have the valid code (formatted nicer than the QB will likely do) .
    The difference is tiny, but very significant, and is a very, very common error (I make it on an almost daily basis )
    One more hint, the issue is a single character.

  9. #9
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by pablolee View Post
    True, but that doesn't cause any syntax issues, it'll just create a cartesian product.
    It is more like "every row in table1 is joining to every row in table 2.
    Just another reason to start learning to write code rather than relying on query builder.
    You will still have the same issue, i.e. the query builder will build valid code (the same as anacedent has done for you) and you will have to compare it to your original code. The query builder won't help you with the comparison, and you already have the valid code (formatted nicer than the QB will likely do) .
    The difference is tiny, but very significant, and is a very, very common error (I make it on an almost daily basis )
    One more hint, the issue is a single character.
    Ok that makes sense. I was recently reading up on the different types of joins and realised I was indeed getting a cartesian product as the result because I didn't add a join or where clause. For my current task, it turns out I'm only gathering data from a single table so it seems I don't need any joins yet, therefore there is no error in my code. The decode works fine.

    I am using SQL Commands over Query Builder as much as I can, but when I need to make joins between so many tables, it just seems quicker to use query builder to generate the code, that is until I add more statements and start causing errors..
    Last edited by london34; 11-18-13 at 17:45.
    Still learning..

  10. #10
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by london34 View Post
    Ok that makes sense. I was recently reading up on the different types of joins and realised I was indeed getting a cartesian product as the result because I didn't add a join or where clause. For my current task, it turns out I'm only gathering data from a single table so it seems I don't need any joins yet, therefore there is no error in my code. The decode works fine.
    So you mean that the first piece of code that you posted works, but the second statement still contains incorrect syntax.

    I am using SQL Commands over Query Builder as much as I can, but when I need to make joins between so many tables, it just seems quicker to use query builder to generate the code, that is until I add more statements and start causing errors..
    The problem with that approach is plain to see. You lack the practice (and therefore experience) to spot simple errors (as evidenced by the code above). Efficient coding is as much about being able to spot and remedy errors as it is getting the code down on a page quickly, if not more so (if your code drafts are riddled with as many daft wee mistakes as mine often are ).

    ETA, even though you only need data from one table, get that second piece of code working use it as an exercise, use it to learn.

  11. #11
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by pablolee View Post
    So you mean that the first piece of code that you posted works, but the second statement still contains incorrect syntax.
    Yeah, that's right. I will go back to that code later as I may need it for reference in the future.

    In my project, I need to save any code that didn't initially work but then found the solution as a learning aspect etc.
    Still learning..

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by london34 View Post
    Ok that makes sense. I was recently reading up on the different types of joins and realised I was indeed getting a cartesian product as the result because I didn't add a join or where clause.
    That's precisely the reason why explicit joins using the JOIN keyword are preferred over the (old-style) implicit joins in the where clause.

    Code:
    SELECT ...
    FROM m_membership_type
      JOIN m_customer
    will generate a syntax error because the JOIN condition is missing:
    Code:
    SELECT ..
    FROM m_membership_type mt
      JOIN m_customer mc ON mt.customer_id = mc.customer_id
    You should also prefix your columns with the table name in any query involving more than one table to avoid ambiguity. If you don't want to repeat the long table name, use a table alias like I did in the above statement.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  13. #13
    Join Date
    Jan 2014
    Posts
    1

    Out of Topic (I assume)

    are you referring to "," or comma as the single character which was missing from the first syntax?
    i am also learning ..
    though i assume that it was the first problem that he encountered. aside from the second one (Join)

Posting Permissions

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