3 Replies Latest reply: Nov 14, 2012 4:14 AM by François Degrelle RSS

    Issue in writing DECODE in Forms

    921311
      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
          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
            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çois Degrelle
              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