Post reply

Convert SQL Server query to Oracle query

  • June 27, 2012 at 8:45 am

    #263323

    Hi

    Our Oracle Developer is out for the rest of the week and i need to convert a SQL Server Query into Oracle.

    Any assistance would be greatly appreciated:

    <code>

    update stg_property

    set tag_fama_score = pa.tag_fama_score,

    cat_fama_score_color = pa.cat_fama_score_color

    from stg_property p

    inner join oe_mf_data_mart..mf_loan_perf_quartly_panel Pa

    on p.pk_property_id = pa.fk_property_id

    and

    --Bring back Maximum Year

    Pa.fk_fiscal_year = (select max(qp0.fk_fiscal_year) FROM oe_mf_data_mart..mf_loan_perf_quartly_panel qp0 WHERE qp0.fk_fha_number = pa.fk_fha_number )

    --Bring back Maximum Quarter

    AND Pa.fk_fiscal_quarter = (select max(qp1.fk_fiscal_quarter) FROM oe_mf_data_mart..mf_loan_perf_quartly_panel qp1 WHERE qp1.fk_fha_number = pa.fk_fha_number )

    </code>

  • June 27, 2012 at 9:27 am

    #1506215

    Probably better to post this in an Oracle forum, not SQL Server.

    Jared
    CE - Microsoft

  • June 27, 2012 at 9:33 am

    #1506220

    I'mn out of practice, but it requires a correlated sub query, and what seems like a wierd format;

    this is untested, but it's kind of like this, which looks weird (from a SQL perspective) becaus the set is referenceing more than one column = (SELECT...

    UPDATE stg_property

    SET (

    tag_fama_score,

    cat_fama_score_color

    ) = (

    SELECT pa.tag_fama_score,,

    pa.cat_fama_score_color

    FROM oe_mf_data_mart..mf_loan_perf_quartly_panel Pa

    WHERE property.pk_property_id = pa.fk_property_id

    AND --Bring back Maximum Year

    Pa.fk_fiscal_year = (

    SELECT MAX(qp0.fk_fiscal_year)

    FROM oe_mf_data_mart..mf_loan_perf_quartly_panel qp0

    WHERE qp0.fk_fha_number = pa.fk_fha_number

    ) --Bring back Maximum Quarter

    AND Pa.fk_fiscal_quarter = (

    SELECT MAX(qp1.fk_fiscal_quarter)

    FROM oe_mf_data_mart..mf_loan_perf_quartly_panel qp1

    WHERE qp1.fk_fha_number = pa.fk_fha_number

    )

    )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 28, 2012 at 2:58 pm

    #1506860

    Hi

    thanks, i was referred to a pretty cool tool, sql developer, and then inside of sql developer

    go to Tools

    Migration, select translation scratch editor,

    this allowed me to convert from sql to Oracle Sql.

    you have to hit the blue >> symbols and select the language you want to convert from.

    thanks again

  • July 31, 2012 at 10:36 pm

    #1520071

    Thanks alot for your precious posting. it solves my many problems.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply