Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Exclamation Unanswered: Convert SQL from Oracle to DB2 Problem !!!!

    Hi All !
    I have a problem to convert sql from Oracle 8i to DB2 8.1.
    It works fine on oracle but some thing wrong with a sql for DB2.

    ***ORACLE***
    SELECT
    ap.appl_id ,
    ap.name "Name",
    ap.acronym "Acronym",
    ap.alias "Alias",
    oc.name "Owning Company"
    FROM appl_profile ap,
    company oc,
    appl_status aps,
    company sc
    WHERE ap.company_id = oc.company_id (+)
    AND ap.appl_status_id = aps.appl_status_id (+)
    AND UPPER(aps.descr) not like 'RETIRED'
    AND UPPER(aps.descr) not like 'DELETED'
    AND ap.main_support_company_id = sc.company_id (+)
    AND UPPER(oc.name) like UPPER('%A%')
    ORDER BY ap.name

    ***DB2***
    SELECT
    ap.appl_id ,
    ap.name "Name",
    ap.acronym "Acronym",
    ap.alias "Alias",
    oc.name "Owning Company"
    FROM appl_profile ap
    LEFT OUTER JOIN company oc ON
    ap.company_id = oc.company_id LEFT OUTER JOIN
    appl_status aps ON ap.appl_status_id = aps.appl_status_id
    LEFT OUTER JOIN company sc ON
    ap.main_support_company_id = sc.company_id
    WHERE UCASE(aps.descr) not like 'RETIRED'
    AND UCASE(aps.descr) not like 'DELETED'
    AND UCASE(oc.name) like UCASE('%A%')
    ORDER BY ap.name

    Thanks !

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    hi,

    what´s the problem? why the syntax is correct.
    that it means (+)?

    Abel.

  3. #3
    Join Date
    Oct 2003
    Posts
    15

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by achiola
    hi,

    what´s the problem? why the syntax is correct.
    that it means (+)?

    Abel.
    Db2 part does not work.
    I've put select for Oracle just for example, what I want to do.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by pavelbelousov
    Db2 part does not work.
    I've put select for Oracle just for example, what I want to do.
    Pavel,

    It would help if you described in more detail what exactly the problem is. Is it a syntax error? Are the results not what you expect? Care to give an example of what you get and what you expect?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2003
    Posts
    15

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by n_i
    Pavel,

    It would help if you described in more detail what exactly the problem is. Is it a syntax error? Are the results not what you expect? Care to give an example of what you get and what you expect?
    When I run sql for Oracle witch on the top of page I have result back.
    I whant to do the same select for DB2. It is a next sql after oracle.
    When I run sql ifor DB I have error message:
    PPORT_COMPANY_ID" is not valid in the context where it is used. SQLSTATE=42703.

    I thnk it is a syntax error.

    Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by pavelbelousov
    When I run sql for Oracle witch on the top of page I have result back.
    I whant to do the same select for DB2. It is a next sql after oracle.
    When I run sql ifor DB I have error message:
    PPORT_COMPANY_ID" is not valid in the context where it is used. SQLSTATE=42703.

    I thnk it is a syntax error.

    Thanks.
    I guess you have checked spelling of your table and column names? You can also try grouping JOINs with parentheses, eg:

    ...FROM (((appl_profile ap
    LEFT OUTER JOIN company oc ON
    ap.company_id = oc.company_id) LEFT OUTER JOIN
    appl_status aps ON ap.appl_status_id = aps.appl_status_id)
    LEFT OUTER JOIN company sc ON
    ap.main_support_company_id = sc.company_id )
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Oct 2003
    Posts
    15

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by n_i
    I guess you have checked spelling of your table and column names? You can also try grouping JOINs with parentheses, eg:

    ...FROM (((appl_profile ap
    LEFT OUTER JOIN company oc ON
    ap.company_id = oc.company_id) LEFT OUTER JOIN
    appl_status aps ON ap.appl_status_id = aps.appl_status_id)
    LEFT OUTER JOIN company sc ON
    ap.main_support_company_id = sc.company_id )
    You are right I had error in column names.
    I've fix it. I've put parentheses around JOINs.

    But this select does not return nothing back.
    The same type of select for Oracle return about 300 rows.

    SELECT
    ap.appl_id ,
    ap.name "Name",
    ap.acronym "Acronym",
    ap.alias "Alias",
    oc.name "Owning Company"
    FROM (((appl_profile ap
    LEFT OUTER JOIN company oc ON
    ap.company_id = oc.company_id) LEFT OUTER JOIN
    appl_status aps ON ap.appl_status_id = aps.appl_status_id)
    LEFT OUTER JOIN company sc ON
    ap.MAINSUPPORTCOMPANYI = sc.company_id)
    WHERE UCASE(aps.descr) not like 'RETIRED'
    AND UCASE(aps.descr) not like 'DELETED'
    AND UCASE(oc.name) like UCASE('%A%')
    ORDER BY ap.name

    Thanks!

  8. #8
    Join Date
    Oct 2003
    Posts
    15

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by pavelbelousov
    You are right I had error in column names.
    I've fix it. I've put parentheses around JOINs.

    But this select does not return nothing back.
    The same type of select for Oracle return about 300 rows.

    SELECT
    ap.appl_id ,
    ap.name "Name",
    ap.acronym "Acronym",
    ap.alias "Alias",
    oc.name "Owning Company"
    FROM (((appl_profile ap
    LEFT OUTER JOIN company oc ON
    ap.company_id = oc.company_id) LEFT OUTER JOIN
    appl_status aps ON ap.appl_status_id = aps.appl_status_id)
    LEFT OUTER JOIN company sc ON
    ap.MAINSUPPORTCOMPANYI = sc.company_id)
    WHERE UCASE(aps.descr) not like 'RETIRED'
    AND UCASE(aps.descr) not like 'DELETED'
    AND UCASE(oc.name) like UCASE('%A%')
    ORDER BY ap.name

    Thanks!
    P.S.
    I think the problem is :
    WHERE UCASE(aps.descr) not like 'RETIRED'
    AND UCASE(aps.descr) not like 'DELETED'
    AND UCASE(oc.name) like UCASE('%A%')

    But it works fine for Oracle.
    How can I change this select to make it work for DB2 ?????

    Thanks.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by pavelbelousov
    P.S.
    I think the problem is :
    WHERE UCASE(aps.descr) not like 'RETIRED'
    AND UCASE(aps.descr) not like 'DELETED'
    AND UCASE(oc.name) like UCASE('%A%')

    But it works fine for Oracle.
    How can I change this select to make it work for DB2 ?????

    Thanks.
    The construct,

    like UCASE('%A%')

    doesn't make a lot of sense to me. By the way, your "not like 'RETIRED'" is equivalent to " != 'RETIRED'".

    Try re-writing the WHERE clause like this:

    WHERE UCASE(aps.descr) not in ('RETIRED', 'DELETED')
    AND UCASE(oc.name) like '%A%'

    and see what happens.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Oct 2003
    Posts
    15

    Re: Convert SQL from Oracle to DB2 Problem !!!!

    Originally posted by n_i
    The construct,

    like UCASE('%A%')

    doesn't make a lot of sense to me. By the way, your "not like 'RETIRED'" is equivalent to " != 'RETIRED'".

    Try re-writing the WHERE clause like this:

    WHERE UCASE(aps.descr) not in ('RETIRED', 'DELETED')
    AND UCASE(oc.name) like '%A%'

    and see what happens.
    Thanks for you message.

    I did this change. It does not return nothing for DB2, but return for Oracle.

    SELECT
    ap.appl_id ,
    ap.name "Name",
    ap.acronym "Acronym",
    ap.alias "Alias",
    oc.name "Owning Company"

    FROM appl_profile ap
    LEFT OUTER JOIN company oc
    ON ap.company_id = oc.company_id
    LEFT OUTER JOIN appl_status aps
    ON ap.appl_status_id = aps.appl_status_id
    LEFT OUTER JOIN company sc
    ON ap.MAINSUPPORTCOMPANYI = sc.company_id
    WHERE UPPER(aps.descr) not in ('RETIRED', 'DELETED')
    AND UPPER(oc.name) like '%A%' ORDER BY ap.name

    This database does not have any FOREIGN or PRIMARY KEYS. It is a test database. May be that gives me a problem on DB2 ??

    Thanks.

Posting Permissions

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