This discussion is archived
3 Replies Latest reply: Nov 14, 2012 2:14 AM by FrançoisDegrelle RSS

Issue in writing DECODE in Forms

921311 Newbie
Currently Being Moderated
Hi,

I am writing the sample code in Toad its working but when i am using in the POST-QUERY in Forms I am getting compilation errors due to wrong syntax.

I am using the code as

SELECT ename,
DECODE(okl_strm.code='YR' and
okl_strm.chr_id in (SELECT
                         okl_strm_ln.chr_id
                         FROM
                         oks_stream_levels_b okl_strm_ln
                         WHERE okl_strm_ln.uom_code='MTH'),'YR',SUM(cleb.price_negotiated),0)
FROM
okh_k_headers_b okh,
okc_k_lines_b cleb,
oks_stream_levels_b okl_strm
WHERE
okh.id=cleb.chr_id
and okh.id=okl_strm.dnz_chr_id
and cleb.id=okl_strm.cle_id

How can i fulfill the above statement in Forms(I am using version 6i)

Any help will be needful for me
  • 1. Re: Issue in writing DECODE in Forms
    AlexDiniasi Journeyer
    Currently Being Moderated
    Hello,

    Forms requires PL/SQL syntax.You need to "select into a variable"
    Your select should be like this:
    SELECT ename,
    DECODE(okl_strm.code='YR' and
    okl_strm.chr_id in (SELECT
    okl_strm_ln.chr_id
    FROM
    oks_stream_levels_b okl_strm_ln
    WHERE okl_strm_ln.uom_code='MTH'),'YR',SUM(cleb.price_negotiated),0)

    INTO
    v_ename,
    v_decode_result

    FROM
    okh_k_headers_b okh,
    okc_k_lines_b cleb,
    oks_stream_levels_b okl_strm
    WHERE
    okh.id=cleb.chr_id
    and okh.id=okl_strm.dnz_chr_id
    and cleb.id=okl_strm.cle_id


    If your query returns more than one row, then you use a pl/sql cursor.

    Kind regards,
    Alex

    If someone's answer is helpful or correct please mark it accordingly.
  • 2. Re: Issue in writing DECODE in Forms
    921311 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.

    The problem is there in using DECODE Statement

    DECODE(okl_strm.code='YR' and
    okl_strm.chr_id in (SELECT
    okl_strm_ln.chr_id
    FROM
    oks_stream_levels_b okl_strm_ln
    WHERE okl_strm_ln.uom_code='MTH')

    I am using and in DECODE which i think is not permissable.
  • 3. Re: Issue in writing DECODE in Forms
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    The PL/SQL engine, embbeded withing the Forms application is allways of older version that the one used by the database. As you use Forms 6i, I imagine the Forms PL/SQL engine would match a very old version of PL/SQL database.

    Francois

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points