1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

My first attempt at updating fields in an Oracle table from a view in SQL Server

Discussion in 'SQL PL/SQL' started by OverMyHead, Nov 21, 2014.

  1. OverMyHead

    OverMyHead Guest

    So far, I'm not doing a very good job and would appreciate the opportunity to learn how to accomplish this.
    Can you teach me how to get this view’s v."OrderNo” to be in scope for the comparison on the where clause? Am I going about this the right way?

    The field OrderNo exists within the SQL Server view vTaxJournal@vertex6_tax_journal, but I am not planning to update that column in stage.sales_header, so I didn’t select it.
    Either way, I believe it is out of scope by the time I get to the where clause and need to do the comparison.
    I’m attempting to update these 7 fields equal to the values found in this view by matching on corresponding order number fields.
    And FYI, the v."OrderNo" field is 12 characters on the SQL Server side thus the concatenate of our order(8) + ship_to(2) + ship_lvl(2).
    Please help if you can or at least point me in the right direction.

    Thank you!

    UPDATE stage.sales_header sh
    SET (sh.state_tax_rate, sh.county_tax_rate, sh.city_tax_rate, sh.district_tax_rate, sh.combined_tax_rate, sh.tax_exempt_postage_amount, sh.tax_rate_ind)
    = (
    SELECT v."State",
    when trim(v."TaxTypeName") = 'Sales' then 'S'
    else 'U'
    end tax_rate_ind
    FROM vTaxJournal@vertex6_tax_journal v
    WHERE (sh.order_num || sh.ship_to_level || sh.ship_cnt_level) = v."OrderNo")
    WHERE (sh.order_num || sh.ship_to_level || sh.ship_cnt_level) = v."OrderNo" and <will be a date param here to only update recent rows>;
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    Unfortunately you have provided no samples of the data you're using so it's impossible to assist in setting a 'proper' format. Provide samples, plus create table statements, so we can better help you.
  3. ocprep

    ocprep Forum Advisor

    Likes Received:
    Trophy Points:
    Orlando, Florida
    SQL Server allows (and seems to encourage) mixed case in database objects. Oracle strongly discourages it. You can only create mixed-case objects in the Oracle data dictionary by creating them with double-quotes (and then you can only reference them by using double-quotes in all SQL that makes use of the mixed-case names).

    If this is a table in Oracle, the column names are almost certainly upper case in the data dictionary. By querying the columns surrounded by double-quotes, you are forcing SQL Server (and Oracle) to try to locate a column called OrderNo when the column is almost certainly stored as ORDERNO.

    Try your operation (or at least the query portion) without the double-quotes, i.e.:

    Code (Text):
    SELECT v.State,
             WHEN trim(v.TaxTypeName) = 'Sales' THEN 'S'
             ELSE 'U'
           END tax_rate_ind
    FROM  vTaxJournal@vertex6_tax_journal v
    WHERE (sh.order_num || sh.ship_to_level || sh.ship_cnt_level) = v.OrderNo;
    OverMyHead likes this.