11 Replies Latest reply: Jul 16, 2014 3:09 PM by Ashutosh(297484) RSS

    Running a report from a form

    salute-Salem

      hi all ,

      i have an item holds this variable :

      V_WHERE :='AND CLINICAL_ENTITY_NO =NVL('''||:CONTROL.OPERATING_ROOM_ID||''',CLINICAL_ENTITY_NO) ';

       

      i use the item as a lexical parameter , i pass it's value to the report to run it with .

      when i run the report i face this error

      ora-01756: quoted string not properly terminated ............. where status in ('1') and

      clinical_entity_no = nvl ( ',clinical_entity_no)

      when i press ok i return to see the item which holds the condition , and i see it as this

      AND CLINICAL_ENTITY_NO =NVL( ' ',CLINICAL_ENTITY_NO)

      which means that the condition is correct .

      how is it correct in the form , and i pass it as it is , but report builder reads it wrong ?

       

      thanks

        • 1. Re: Running a report from a form
          HamidHelal

          Try

           

           

          DECLARE another vaiable like

           

          QT        varchar2(10) :='''';

          use QT like

           

          V_WHERE :='AND CLINICAL_ENTITY_NO =NVL('||QT||:CONTROL.OPERATING_ROOM_ID||QT||',CLINICAL_ENTITY_NO)';

           

          What is the output ?

          Note : be sure :CONTROL.OPERATING_ROOM_ID value is passing correctly by using

          Message(:CONTROL.OPERATING_ROOM_ID);

          Message(:CONTROL.OPERATING_ROOM_ID);


          Hope this helps


          Hamid

          • 2. Re: Running a report from a form
            salute-Salem

            Note : be sure :CONTROL.OPERATING_ROOM_ID value is passing correctly by using

            i am sure , it is null , i am sure because i see the item i assigned the values to after i press ok to the error .

            this is the value in the item

            AND CLINICAL_ENTITY_NO =NVL(' ',CLINICAL_ENTITY_NO) AND PATIENT_FILE_NO =NVL(' ',PATIENT_FILE_NO) AND TRUNC(ARRIVAL_TIME) BETWEEN '02-JUL-14' AND '02-JUL-14'

            and this is the "v_where" variable

            V_WHERE :='AND CLINICAL_ENTITY_NO =NVL('''||:CONTROL.OPERATING_ROOM_ID||''',CLINICAL_ENTITY_NO) ';

            V_WHERE :=V_WHERE || 'AND PATIENT_FILE_NO =NVL('''||:CONTROL.PATIENT_FILE_NO||''',PATIENT_FILE_NO) ';

            V_WHERE :=V_WHERE || 'AND TRUNC(ARRIVAL_TIME) BETWEEN '''||:CONTROL.DATE_FROM||''' AND '''||:CONTROL.DATE_TO||''''; 

            IF :CONTROL.SURGEON  IS NOT NULL THEN

               V_WHERE :=V_WHERE ||'AND OPERATION_NO IN ( SELECT OPERATION_NO 

                                                          FROM OR_OPERATION_DATA_SURGEONS

                                                          WHERE MAIN_SURGEON =''Y''

                                                          AND SURGEON='''||:CONTROL.SURGEON||''')';

            END IF ;

            IF :PARAMETER.BTN_CHOOSE !='A' THEN

               V_WHERE :=V_WHERE ||'AND STATUS ='''||:PARAMETER.BTN_CHOOSE||'''';

            END IF ;  

             

            :PARAM.AND_CLAUSE := V_WHERE ;

            what is wrong with this ,

            the value is correct in the item which i pass to the report , and the report reads it wrong ? ?

            • 3. Re: Running a report from a form
              HamidHelal

              V_WHERE :='AND CLINICAL_ENTITY_NO =NVL('''||:CONTROL.OPERATING_ROOM_ID||''',CLINICAL_ENTITY_NO) ';

              V_WHERE :=V_WHERE || 'AND PATIENT_FILE_NO =NVL('''||:CONTROL.PATIENT_FILE_NO||''',PATIENT_FILE_NO) ';

              V_WHERE :=V_WHERE || 'AND TRUNC(ARRIVAL_TIME) BETWEEN '''||:CONTROL.DATE_FROM||''' AND '''||:CONTROL.DATE_TO||'''';  

              IF :CONTROL.SURGEON  IS NOT NULL THEN 

                 V_WHERE :=V_WHERE ||'AND OPERATION_NO IN ( SELECT OPERATION_NO  

                                                            FROM OR_OPERATION_DATA_SURGEONS

                                                            WHERE MAIN_SURGEON =''Y'' 

                                                            AND SURGEON='''||:CONTROL.SURGEON||''')';

              END IF ;

              IF :PARAMETER.BTN_CHOOSE !='A' THEN 

                 V_WHERE :=V_WHERE ||'AND STATUS ='''||:PARAMETER.BTN_CHOOSE||'''';

              END IF ;   

               

              :PARAM.AND_CLAUSE := V_WHERE ;


              Replace with


                  V_WHERE :=' AND CLINICAL_ENTITY_NO =NVL('''||:CONTROL.OPERATING_ROOM_ID||''',CLINICAL_ENTITY_NO) ';


                  V_WHERE :=V_WHERE || ' AND PATIENT_FILE_NO =NVL('''||:CONTROL.PATIENT_FILE_NO||''',PATIENT_FILE_NO) ';


                  V_WHERE :=V_WHERE || ' AND TRUNC(ARRIVAL_TIME) BETWEEN '''||:CONTROL.DATE_FROM||''' AND '''||:CONTROL.DATE_TO||'''';


                  IF :CONTROL.SURGEON  IS NOT NULL THEN


                     V_WHERE :=V_WHERE ||' AND OPERATION_NO IN ( SELECT OPERATION_NO


                                                                FROM OR_OPERATION_DATA_SURGEONS


                                                                WHERE MAIN_SURGEON =''Y''


                                                                AND SURGEON='''||:CONTROL.SURGEON||''')';


                  END IF ;


                  IF :PARAMETER.BTN_CHOOSE !='A' THEN


                     V_WHERE :=V_WHERE ||' AND STATUS ='''||:PARAMETER.BTN_CHOOSE||'''';


                  END IF ; 


                  :PARAM.AND_CLAUSE := V_WHERE ;


              Hope it will works :)

              • 4. Re: Running a report from a form
                salute-Salem

                What did you change , it is the same code , same error

                • 5. Re: Running a report from a form
                  HamidHelal

                  Ora-01400 wrote:

                   

                  What did you change , it is the same code , same error

                  Not the same code, I add SPACE before every 'AND... Like '  AND

                  Space before 'AND an issue most of the case.


                  Check your self.


                  And try to use direct code first and test .


                  Hope this helps..

                  • 6. Re: Running a report from a form
                    Ashutosh(297484)

                    Hi Hamid,

                    Is this resolved ?

                    • 7. Re: Running a report from a form
                      salute-Salem

                      No it's not ?

                      • 8. Re: Running a report from a form
                        Andreas Weiden

                        I would go another approach, which (in my eyes) keeps things together which belong together. The report knows it own querysource, so it should be up to the  report to build the WHERE. I would just pass all relevant values from forms to the report and then buildup the WHERE-condition in the report itself.

                        • 9. Re: Running a report from a form
                          salute-Salem

                          do you mean , forget the lexical parameter , and create bind parameters , and pass each value to them ?

                          • 10. Re: Running a report from a form
                            salute-Salem

                            there is another solution , i found ,

                             

                            i can say :

                             

                            if :OPERATING_ROOM_ID is not null

                            then

                            v_where := 'AND CLINICAL_ENTITY_NO = '''||:CONTROL.OPERATING_ROOM_ID||'''' ;

                            • 11. Re: Running a report from a form
                              Ashutosh(297484)

                              be aware to make sure the data types match when passing the values. you may to have the parameter as char and do some sort of conversion on the receiving side before binding it to a sql query.