3 Replies Latest reply: Feb 13, 2013 4:42 AM by Igor S. RSS

    Using a array in SQL in

    Igor S.
      Hello experts,

      I got a procedure that accepts a list of clients. From that list I need to build a query that looks like this
      select * 
        from customers
       where customer_id in (i_customer_list);
      Where i_customer_list is the input parameter of a procedure that is a nested table type. What would be the best approach on handling this type of situation?

      Best regards,
      Igor
        • 1. Re: Using a array in SQL in
          jeneesh
          If your table type is defined in SQL (as a DB object), you can do as
          select * 
            from customers
           where customer_id in
               (
               select * from table(cast(i_customer_list as sql_type_name))
              );
          • 2. Re: Using a array in SQL in
            Billy~Verreynne
            Igor S. wrote:

            What would be the best approach on handling this type of situation?
            Depends on the nature of the situation.

            If, for example, the same sets of lists are commonly submitted by clients, then that should be addressed via normalisation. A relation looking something as follows:
            +GROUPS = ( group_id, group_name, ... )+
            +CUSTOMER_GROUPS = ( group_ip, customer_id )+

            The client then submits a group identifier instead of a list of customer identifiers - and the SQL does a join or exists in the customer group relation in order to identify the relevant customers.

            If, for example, the lists are fairly random and large per client session, but re-used by the client session, then a GTT (global temp table) can be considered. This allows the client session to submit its large list of customer identifiers once, and storing that in a GTT. Where SQLs can then use, and re-use, this table via a join or exists.

            What does NOT make sense is the client session submitting a string, containing a comma delimited list of customer identifiers. Software engineering principles say that this approach is wrong.

            Structured data and structured programming are sides of the same coin, that says well designed, consistent, and robust software. Sending structured data (such as a customer id array) as a string is, plain and simple, wrong.
            • 3. Re: Using a array in SQL in
              Igor S.
              Thank you both for your answers.

              Best regards,
              Igor