13 Replies Latest reply: Dec 12, 2012 3:01 PM by AlbertoFaenza RSS

    How to append input array parameter to a conditon in where clause

    user554531
      CREATE OR REPLACE PROCEDURE file_upload(p_array_code IN DIAG_CODE,p_desc_code IN DIAG_CODE_DESC,p_code_result OUT DIAG_CODE_TABLE) IS
      v_count NUMBER;
      v_range1 VARCHAR2(8);
      v_range2 VARCHAR2(8);
      l_count pls_integer := 0;
      l_diag_code_table1 DIAG_CODE_TABLE;
      l_diag_code_table2 DIAG_CODE_TABLE;

      l_loop_diag_code_table1 DIAG_CODE_TABLE;
      l_loop_diag_code_table2 DIAG_CODE_TABLE;


      BEGIN
      l_loop_diag_code_table1 := diag_code_table();
      l_loop_diag_code_table2 := diag_code_table();

      FOR i IN 1..p_array_code.count
      LOOP

      SELECT diag_code_rec(d.icd9_pcs_text_href,
      d.icd9_procedure_deci_code,
      d.icd9_procedure_long_desc,
      d.icd9_pcs_gem_flag_desc,
      g.gem_icd9_icd10pcs_flag,
      dc.icd10_procedure_code,
      dc.icd10_procedure_long_desc)
      BULK COLLECT INTO l_diag_code_table2
      FROM icd9_procedure_codes d,
      icd10_procedure_codes dc ,
      gem_icd9_icd10pcs g
      WHERE d.icd9_procedure_code=g.gem_icd9_pcs_code
      AND g.gem_icd10_pcs_code=dc.icd10_procedure_code(+)
      AND d.ICD9_PROCEDURE_code like p_array_code(i)||'%' ;


      FOR j IN 1..l_diag_code_table1.count
      LOOP
      l_loop_diag_code_table1.extend;
      l_loop_diag_code_table1 (l_loop_diag_code_table1.last):= l_diag_code_table1(j) ;
      END LOOP;


      END LOOP;
      p_code_result := l_loop_diag_code_table1 ;
      END;
      /

      DIAG_CODE_DESC is declared as type
      create or replace type DIAG_CODE_DESC as table of varchar2(300);

      p_desc_code contains array of keywords

      I need to add one more condition to the above select query using the p_desc_code as
      (regexp_like(S.ICD9_PROCEDURE_LONG_DESC, '(p_desc_code (1)') AND regexp_like(S.ICD9_PROCEDURE_LONG_DESC, '(p_desc_code (2))' and ... upto inpu array limit.

      Could anyone help me through in writing the above the sql with the additional condition?

      I would really appreciate the help.

      Thanks,
      in advance
        • 1. Re: How to append input array parameter to a conditon in where clause
          sb92075
          can only run SQL when we have tables & data which you did NOT provide for us.

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: How to append input array parameter to a conditon in where clause
            user554531
            ICD9_PROCEDURE_CODES _ table Name
            Coulmn name -- All are varchar columns
            I CD9_PROCEDURE_CODE
            ICD9_PROCEDURE_DECI_CODE
            ICD9_PROCEDURE_LONG_DESC
            ICD9_PROCEDURE_SHORT_DESC
            ICD9_VERSION
            ICD9_PCS_TEXT_HREF
            ICD9_PCS_GEM_FLAG_DESC

            Data for ICD9_PROCEDURE_CODES
            0441 04.41 Decompression trigem Scenaroio0 Scenario0:text_href
            0442 04.42 Other carnial Cran Scenario1 Scenario1: text_href


            ICD10_PROCEDURE_CODES - Table Name
            Column Name Varchar columns
            ICD10_PROCEDURE_CODE
            ICD10_PROCEDURE_SHORT_DESC
            ICD10_PROCEDURE_LONG_DESC
            ICD10_PCS_TEXT_HREF
            ICD10_PCS_GEM_FLAG_DESCRIPTION

            Data for icd10_procedure_codes
            00NK0ZZ Release Nerve Scenario0 Scenario0:text_href
            00NK3ZZ Diabetes Neuropathy Scenario11 Scenario11: text_href
            00NK4ZZ Nerve disorder Nerve malfunction Scenario12 Scenario12: text_href

            GEM_ICD9_ICD10PCS_FLAG -- Table name
            Coulmn Name -- Varchar
            GEM_ICD9_PCS_CODE
            GEM_ICD10_PCS_CODE
            GEM_ICD9_ICD10PCS_FLAG

            Data for gem_icd9_icd10pcs
            ('0441','00NK0ZZ','10000');
            ('0441','00NK3ZZ','10000');
            ('0441','00NK4ZZ','10000');

            Please find the above data and table description
            • 3. Re: How to append input array parameter to a conditon in where clause
              Billy~Verreynne
              Ugly code.

              Using a loop to repeatedly execute a SQL is fundamentally wrong ito performance and scalability.

              Using an unconstrained bulk fetch - fetching all the output of the SQL cursor and stuffing this into expensive PGA memory - is also fundamentally wrong ito basic server health. You can do serious "damage" that way and cause severe swap daemon trashing as the kernel struggles to meet unrealistic memory demands.

              Using PL/SQL as a buffer cache for SQL data, is also fundamentally wrong - in all respects.

              I would not add code to this procedure. I would trash it. And then go back to the drawing board with the question - what are the requirements?
              • 4. Re: How to append input array parameter to a conditon in where clause
                user554531
                Below are the requirements:

                A file will be uploaded from front end .
                Data from the file will be sent as arraya from java code:
                Two types of data
                1. values from file
                2. array of string values

                Sample data
                1000
                2000
                3000
                4000

                'abc','def',..upto 5

                I have to run the select query based on each value .

                Both the input values are varchar

                COuld you please advise me how to revise the current code and also how to dynamiically append where clause based on second input string?


                Thanks,
                in advance
                • 5. Re: How to append input array parameter to a conditon in where clause
                  sb92075
                  how is it that you post to forum called SQL & PL/SQL;
                  yet NEVER post any actual code in either language?


                  How do I ask a question on the forums?
                  SQL and PL/SQL FAQ
                  • 6. Re: How to append input array parameter to a conditon in where clause
                    user554531
                    I have posted both code and data format if you see the first 2 posts of this thread
                    • 7. Re: How to append input array parameter to a conditon in where clause
                      AlbertoFaenza
                      user554531 wrote:
                      I have posted both code and data format if you see the first 2 posts of this thread
                      I think that Billy Verreynne has already given you the best suggestion regarding your code:
                      I would not add code to this procedure. I would trash it. And then go back to the drawing board with the question - what are the requirements?
                      Regards.
                      Al
                      • 8. Re: How to append input array parameter to a conditon in where clause
                        user554531
                        Any advise how to rewrite the code will be greatly appreciated.

                        Thanks
                        • 9. Re: How to append input array parameter to a conditon in where clause
                          AlbertoFaenza
                          user554531 wrote:
                          Any advise how to rewrite the code will be greatly appreciated.

                          Thanks
                          What about describing requirements? Please describe what you want to do with this procedure.

                          The code you have posted is always returning an empty collection. So no idea of what you are trying to achieve.

                          Also, as mentioned before, post CREATE TABLE and INSERT statement of some sample data.

                          Regards.
                          Al

                          Edited by: Alberto Faenza on Dec 11, 2012 10:08 PM
                          • 10. Re: How to append input array parameter to a conditon in where clause
                            Billy~Verreynne
                            user554531 wrote:
                            Below are the requirements:

                            A file will be uploaded from front end .
                            Data from the file will be sent as arraya from java code:
                            Two types of data
                            1. values from file
                            2. array of string values
                            Why use Java to process the file? Oracle has SQL*Loader and supports files as external tables. Keep in mind the distance file data needs to travel in your approach. All the way from disk into Java across JDBC into PL/SQL and then to SQL and then finally to disk again.

                            A lot of moving parts. Which means increased complexity and decreased performance.
                            I have to run the select query based on each value .
                            So if there are a million lines to process in the file, you need to execute a million SQLs. Bad idea.

                            This approach has a 1:1 relationship between line loaded from file and making a PL/SQL call and running a SQL statement to process that line. This will never scale.

                            Bigger file? Slower processing. Absolutely no way this approach will be able to handle increased file volumes.

                             
                            The better approach is to eliminate as many moving parts as possible. And that would be to ONLY use SQL. To read the file. To process a line. To insert/update the database. Nothing else.

                            And this approach is possible using external tables, enabling you to read directly from that file using SQL. No PL/SQL needed. No Java required. For what it is worth - I load over 30 files per minutes and with around 150,000 lines (rows) per file. And I don't do this using PL/SQL. Or Java.
                            • 11. Re: How to append input array parameter to a conditon in where clause
                              user554531
                              Thankyou for providing the insight .
                              My application is based on client server architecture where the user will upload the file and click the submit upon which file has to be read and results should display on the screen.

                              We need to pass the results back to the screen.

                              File will have maximum of 3000 rows .

                              Current code will pass the result set to java where the data will read and dispalyed on screen
                              • 12. Re: How to append input array parameter to a conditon in where clause
                                user554531
                                Below are the requirements:

                                A file will be uploaded from front end .
                                Data from the file will be sent as arrays from java code:
                                Two types of data
                                1. values from file
                                2. array of string values

                                data format from file
                                1.Sample data
                                1000
                                2000
                                3000
                                4000
                                array of strings upto 5 strings limiy
                                2.'abc','def',..upto 5

                                Both the input values are varchar.

                                create table ICD9_PROCEDURE_CODES(

                                ICD9_Procedure_Code          Varchar2(6)     Primary Key,

                                ICD9_Procedure_Deci_Code     Varchar2(7),     

                                ICD9_Procedure_Long_Desc     Varchar2(300),     

                                ICD9_Procedure_Short_Desc     Varchar2(100),     

                                ICD9_PCS_Gem_Flag_Desc          Varchar2(500),     

                                ICD9_Version               Varchar2(10));     

                                insert into icd9_procedure_code(ICD9_PROCEDURE_CODE , ICD9_PROCEDURE_DECI_CODE,
                                ICD9_PCS_TEXT_HREF, ICD9_PCS_GEM_FLAG_DESC)
                                values(0441 ,04.41 ,Decompression trigem ,Scenaroio0 ,Scenario0:text_href)
                                insert into icd9_procedure_code(ICD9_PROCEDURE_CODE , ICD9_PROCEDURE_DECI_CODE,
                                ICD9_PCS_TEXT_HREF, ICD9_PCS_GEM_FLAG_DESC)
                                0442, 04.42 ,Other carnial Cran ,Scenario1 ,Scenario1: text_href)

                                create table ICD10_PROCEDURE_CODES(

                                ICD10_PCS_Order_Num               Varchar2(7),     

                                ICD10_Procedure_Code               Varchar2(8)     Primary Key,

                                ICD10_Procedure_Decimal_Code          Varchar2(9),     

                                ICD10_PCS_Code_Header               Number(1),     

                                ICD10_Procedure_Short_Desc     Varchar2(60),

                                ICD10_Procedure_Long_Desc     Varchar2(300),     

                                ICD10_PCS_Gem_Flag_Description          Varchar2(500),     

                                Version               Varchar2(10),

                                );
                                insert into icd10_procedure_codes
                                ( ICD10_PROCEDURE_CODE, ICD10_PROCEDURE_LONG_DESC, ICD10_PCS_TEXT_HREF, ICD10_PCS_GEM_FLAG_DESCRIPTION)
                                values

                                (00NK0ZZ,00N.K0ZZ, Release Nerve, Scenario0, Scenario0:text_href
                                00NK3ZZ, 00N.K3ZZ,Diabetes Neuropathy, Scenario11, Scenario11: text_href
                                00NK4ZZ, 00NK.4ZZ,Nerve disorder, Scenario12 ,Scenario12: text_href


                                create table GEM_ICD9_ICD10PCS(

                                GEM_ICD9_PCS_Code          Varchar2(6),

                                GEM_ICD10_PCS_Code          Varchar2(8),     

                                GEM_ICD9_ICD10PCS_Flag          Number(5),     

                                GEM_Version          Varchar2(10),

                                constaraint GEM_ICD9_ICD10PCS_PK PRIMARY KEY (GEM_ICD9_PCS_Code,GEM_ICD10_PCS_Code,GEM_ICD9_ICD10PCS_Flag))

                                insert into gem_icd9_icd10pcs (GEM_ICD9_PCS_Code,GEM_ICD10_PCS_Code,GEM_Version)
                                values
                                ('0441','00NK0ZZ','10000');
                                ('0441','00NK3ZZ','10000');
                                ('0441','00NK4ZZ','10000');

                                Thanks for looking into this thread
                                • 13. Re: How to append input array parameter to a conditon in where clause
                                  AlbertoFaenza
                                  Sorry, these are just input data and partial requirements. I only see input data, file and tables.
                                  What do you have to do with these?
                                  What is the expected output for the sample data you have provided?

                                  Also be sure that INSERT statements you have posted are valid. I see some missing quotes for varchar2 columns values. Have you tried to execute yourself what you have posted?

                                  Regards.
                                  Al

                                  Edited by: Alberto Faenza on Dec 12, 2012 9:59 PM