SUBSTR to view table columns

35 pts.
Tags:
Oracle
Oracle Views
SUBSTR
How can I use SUBSTR within my view table columns to drop only right part of the string? Thank you.

Software/Hardware used:
Oracle Developer

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Subhendu Sen
    What query are you writing for? Is there any error in your query? You can back with your query that can help to resolve this issue.
    82,140 pointsBadges:
    report
  • ToddN2000
    Post your code so far and the results. This way we can better understand what you are trying to do and help you fix your code. When using the SUBSTR are you using variables for the staring position and length or are they fixed values?
    77,810 pointsBadges:
    report
  • pgams2017

    Thank you for responding quickly to my question.


    CREATE VIEW reg_state AS
    SELECT * FROM ORIGINAL_TBL;

    UPDATE reg_state
    SET REGZIP= SUBSTRING(REGZIP,1,5);
    commit;

    --------------------------------------------------------------

    ----------------------------------------------------------------

    Error report -

    SQL Error: ORA-00904: "SUBSTRING": invalid identifier

    00904. 00000 -  "%s: invalid identifier"

    *Cause:   

    *Action:

     

     

    Commit complete.

    35 pointsBadges:
    report
  • pgams2017
    Something else to add: REGZIP is a variable I am using as a starting point. I do have a table with the full zip code, and want to create a view with only 5 digits of the zip. Thank you for your help and feedback on this.
    35 pointsBadges:
    report
  • carlosdl
    In Oracle, the function is called SUBSTR, not SUBSTRING.
    84,195 pointsBadges:
    report
  • carlosdl
    Also, when you update a view, you are actually updating the base table, because the view doesn't store any data.

    What you need to do, is to create the view retrieving only the desired part of the zip code.

    No need for updates in this case.
    84,195 pointsBadges:
    report
  • pgams2017
    Thanks all for the response. I have resolved this by using carlosdl's suggestion.
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: