locked
How to convert the column names from upercase to lower case RRS feed

  • Question

  • Hello,

     

    I have created my database based on some datasources that had all of the columns in lowercase, now the datasource is implemented in oracle and they changed all the column names to uppercase. Now I have to use this new tables with uppercase column names and change them all in lowercase. Can you please suggest a quick way to convert?  

    SELECT

    guest.CURRENT_CALL_REC.CALL_REC, guest.CURRENT_CALL_REC.DATESTART, guest.CURRENT_GENERAL_FIELDS.STATE,

    guest

    .CURRENT_GENERAL_FIELDS.BURTID, guest.CURRENT_GENERAL_FIELDS.REL_STATE, guest.CURRENT_GENERAL_FIELDS.PUBLIC_STATUS,

    guest

    .CURRENT_GENERAL_FIELDS.SEV, guest.CURRENT_GENERAL_FIELDS.PRI, guest.CURRENT_GENERAL_FIELDS.OWNER,

    guest

    .CURRENT_GENERAL_FIELDS.SUB_BY, guest.CURRENT_GENERAL_FIELDS.IMPACT, guest.CURRENT_GENERAL_FIELDS.PANICSTR,

    guest

    .CURRENT_GENERAL_FIELDS.TYPE, guest.CURRENT_GENERAL_FIELDS.SUBTYPE, guest.CURRENT_GENERAL_FIELDS.SUBTEAM,

    guest

    .CURRENT_GENERAL_FIELDS.LASTMOD_BY, guest.CURRENT_GENERAL_FIELDS.GEN, guest.CURRENT_GENERAL_FIELDS.HAS_ATTACHMENT,

    guest

    .CURRENT_GENERAL_FIELDS.DOC_IMPACT, guest.CURRENT_GENERAL_FIELDS.TITLE, guest.CURRENT_GENERAL_FIELDS.CNT_CALL_REC,

    guest

    .CURRENT_GENERAL_FIELDS.DATE_CREATE, guest.CURRENT_GENERAL_FIELDS.DATE_LASTMOD,

    guest

    .CURRENT_GENERAL_FIELDS.SUBTEAMID, guest.CURRENT_RELEASES.TARGET_RELEASE, guest.CURRENT_RELEASES.OPEN_IN_RELEASE,

    guest

    .CURRENT_RELEASES.FIXED_IN_RELEASE, guest.CURRENT_RELEASES.TARGET_RELEASE_DATES,

    guest

    .CURRENT_RELEASES.TESTME_IN_RELEASE, guest.CURRENT_RELEASES.UNTESTED_IN_RELEASE, guest.CURRENT_CALL_REC.AUTHOR,

    guest

    .CURRENT_CALL_REC.CALLRECID, guest.CURRENT_GENERAL_FIELDS.CNT_DUP, guest.CURRENT_GENERAL_FIELDS.DATE_OWNERMOD,

    guest

    .CURRENT_GENERAL_FIELDS.HAS_CALL_RECS, guest.CURRENT_GENERAL_FIELDS.DATE_LASTMOD_CHANGELOG,

    guest

    .CURRENT_CUSTOMER_CASE.OS, guest.CURRENT_CUSTOMER_CASE.CASEID, guest.CURRENT_CUSTOMER_CASE.CUST_TYPE,

    guest

    .CURRENT_CUSTOMER.CUSTOMER_CASE_SCORE, guest.CURRENT_CUSTOMER.ESCAL_STATUS,

    guest

    .CURRENT_GENERAL_FIELDS_LISTS_K.KEYWORDS
    Friday, February 3, 2012 9:01 PM

Answers

  • The quickest way, I think, will be to select everything from your old table into a new table, e.g.

    SELECT
    
    CALL_REC as call_rec, DATESTART as datestart, STATE as state,
    etc.
    INTO NewGuestInfo
    from ...
    
    


     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, February 3, 2012 9:25 PM
    Answerer

All replies

  • The quickest way, I think, will be to select everything from your old table into a new table, e.g.

    SELECT
    
    CALL_REC as call_rec, DATESTART as datestart, STATE as state,
    etc.
    INTO NewGuestInfo
    from ...
    
    


     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, February 3, 2012 9:25 PM
    Answerer
  • Oracle is case-sensitive. However, it also uppercases non-quoted identifiers before execution. Hence, there is no need to be particular with case, unless your identifiers are quoted.

    If your datasource quoted all identifiers, it is likely a bug in the data source implementation, and should be fixed there. Though, i simply doubt this is the case (pun not intended). IIRC, SSRS did this at first, i reported a bug over it.

    If you determine that your Oracle implementation requires lowercase COLUMN names--which i would strongly recommend against unless you known exactly why this is required--CREATE a second TABLE in Oracle with quoted identifiers, or just use a VIEW for the same.


    Monday, February 6, 2012 1:50 PM
    Answerer
  • Thanks Naomi. This works. But I was hoping that there is a single function or a command I can use to convert all uppercase names to lowercase. I guess there is no such thing. Thanks for your help as always:)
    Monday, February 6, 2012 10:15 PM
  • where u use this coloumns in either grid or any other data control. if u use data coloumn header to format function.
    Tuesday, February 14, 2012 5:41 AM