1 Reply Latest reply: Dec 14, 2012 4:37 AM by VC RSS

    problem in report creation

    Vedant
      Hi,
      I am creating a classical report . it is working fine on sql command prompt. When i enter the code into report form to create the report it is giving the error
      Error ORA-01461: can bind a LONG value only for insert into a LONG column 
      .


      SELECT   Gl_date, Gl_code, Sl_code, Document_no
             , Document_no1
             , DECODE(Module, 'J', V.Description, NULL, NULL
                            ,  RTRIM(LTRIM(INITCAP(SOURCE) ) )
                              || ' - '|| NAME|| ' '|| V.Description) "DESC"
             , Currency_code, Curr_dr, Curr_cr, Func_dr, Func_cr
             , Cc_id, Narration, Ref_number, Act_batch_id
             , Hdr_description, SUM(Op) Op, SUM(ABS(Op) ) Op1
             , Asvm.Description NAME, Op_dr, Op_cr
             , (SELECT Description
                  FROM Act_segment_values_mas
                 WHERE Parent_id IN( SELECT ID
                                       FROM Act_segment_values_mas
                                      WHERE Segment_id LIKE Get_gl_code
                                        AND Segment_value = Gl_code)
                   AND Segment_value = Sl_code) Sl_desc
             , Voucher_no, Tran_number, Line_id, Voucher_no1
      
          FROM (SELECT Gl_date
                     , DECODE(Get_gl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8) Gl_code
                     , DECODE(Get_sl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8) Sl_code
                     , TO_CHAR(Tran_number) Document_no
                     , Tran_id Document_no1, Description
                     , Currency_code, ABS(Amount_dr) Curr_dr
                     , ABS(Amount_cr) Curr_cr
                     , ABS(Accounted_dr) Func_dr
                     , ABS(Accounted_cr) Func_cr
                     , TO_CHAR(V.Cc_id) Cc_id
                     , Description Narration, Ref_number
                     , Batch_id Act_batch_id
                     , DECODE(Description, 'PAYMENTS AP TO GL', 'P'
                                         , 'RECEIVABLE AR TO GL', 'R', 'J') Hdr_description
                     , 0 Op, 0 Op_dr, 0 Op_cr
                     , Module || '-' || Voucher_no Voucher_no
                     , Tran_number, NAME, SOURCE, Module, Line_id
                     , Voucher_no Voucher_no1
                  FROM Ti_je_gl_v V     --, ACT_COMBINATIONS_MAS V
                 WHERE Gl_date >= :P111_from_date
                   AND Gl_date <= :P111_to_date
                   AND NVL(V.Description, 'NA') NOT LIKE
                                                    'Year Ending%'
                   AND DECODE(Get_gl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8)
                           LIKE DECODE(:P111_gl_code, 'ALL', '%', :P111_gl_code)
                   AND DECODE(Get_sl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8)
                           LIKE DECODE(:P111_segment, 'ALL', '%', :P111_segment)
                UNION ALL
                SELECT   TO_DATE(Gl_date, 'DD-MM-RRRR'), Gl_code
                       , Sl_code, Document_no, Document_no1
                       , Description, Currency_code, 0, 0, 0, 0
                       , Cc_id, NULL, NULL, NULL, NULL, SUM(Op)
                       , SUM(Op_dr), SUM(Op_cr), NULL, NULL, NULL
                       , NULL, NULL, NULL, NULL
                    FROM (SELECT   NULL Gl_date
                                 , DECODE(Get_gl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8) Gl_code
                                 , DECODE(Get_sl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8) Sl_code
                                 , NULL Document_no, NULL Document_no1, NULL Description
                                 , NULL Currency_code, NULL Amount_dr, NULL Amount_cr, NULL Accounted_dr
                                 , NULL Accounted_cr, NULL Cc_id, NULL Narration
                                 , NULL Ref_number, NULL Batch_id, NULL Hdr_description
                                 ,   SUM(NVL(Accounted_dr, 0) )- SUM(NVL(Accounted_cr, 0) ) Op
                                 , SUM(NVL(Accounted_dr, 0) ) Op_dr
                                 , SUM(NVL(Accounted_cr, 0) ) Op_cr
                                 , NULL Voucher_no, NULL, NULL
                                 , NULL, NULL, NULL, NULL
                              FROM Ti_je_gl_v V
                             WHERE Gl_date < :P111_from_date
                   AND DECODE(Get_gl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8)
                           LIKE DECODE(:P111_gl_code, 'ALL', '%', :P111_gl_code)
                   AND DECODE(Get_sl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8)
                           LIKE DECODE(:P111_segment, 'ALL', '%', :P111_segment)
                GROUP BY Segment1, Segment2, Segment3
                                 , Segment4, Segment5, Segment6
                                 , Segment7, Segment8
                          UNION ALL
                          SELECT   NULL Gl_date
                                 , DECODE(Get_gl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8) Gl_code
                                 , DECODE(Get_sl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8) Sl_code
                                 , NULL Document_no, NULL Document_no1, NULL Description
                                 , NULL Currency_code, NULL Amount_dr, NULL Amount_cr
                                 , NULL Accounted_dr, NULL Accounted_cr, NULL Cc_id
                                 , NULL Narration, NULL Ref_number, NULL Batch_id
                                 , NULL Hdr_description
                                 ,   SUM(NVL(Accounted_dr, 0) )- SUM(NVL(Accounted_cr, 0) ) Op
                                 , SUM(NVL(Accounted_dr, 0) ), SUM(NVL(Accounted_cr, 0) )
                                 , NULL Voucher_no, NULL, NULL
                                 , NULL, NULL, NULL, NULL
                              FROM Ti_je_gl_v V
                             WHERE Gl_date <= :P111_from_date
                               AND NVL(Description, 'NA') LIKE
                                            'Year Ending Journal%'
                              AND DECODE(Get_gl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8)
                                              LIKE DECODE(:P111_gl_code, 'ALL', '%', :P111_gl_code)
                              AND DECODE(Get_sl_code, 1, V.Segment1, 2, V.Segment2, 3, V.Segment3
                                         , 4, V.Segment4, 5, V.Segment5, 6, V.Segment6
                                         , 7, V.Segment7, 8, V.Segment8)
                                              LIKE DECODE(:P111_segment, 'ALL', '%', :P111_segment)
                              --AND Segment3 LIKE DECODE(NVL(:P111_br_code, 'ALL'), 'ALL', '%', :P111_br_code)
                          GROUP BY Segment1, Segment2, Segment3
                                 , Segment4, Segment5, Segment6
                                 , Segment7, Segment8)
                GROUP BY Gl_date, Gl_code, Sl_code, Document_no
                       , Document_no1, Description, Currency_code
                       , Cc_id) V
              , Act_segment_values_mas Asvm
         WHERE V.Gl_code = Asvm.Segment_value(+)
           AND Asvm.Segment_id(+) = Get_gl_code
      GROUP BY Gl_date, Gl_code, Sl_code, Document_no
             , Document_no1
             , DECODE(Module, 'J', V.Description, NULL, NULL
                            , RTRIM(LTRIM(INITCAP(SOURCE) ) )|| ' - '|| NAME
                              || ' '|| V.Description)
             , Currency_code, Curr_dr, Curr_cr, Func_dr, Func_cr
             , Cc_id, Narration, Ref_number, Act_batch_id
             , Hdr_description, Asvm.Description, Op_dr, Op_cr
             , Voucher_no, Tran_number, Line_id, Voucher_no1
      ORDER BY Gl_date, Voucher_no1
      How can i solve this?


      Thanks & Regards
      Vedant