10 Replies Latest reply on Aug 15, 2018 5:51 PM by L. Fernigrini

    How to pass a value inside a capsulation

    3193685

      I need to pass the TRANSDATE to union where clauses. Please give me a solution

       

      SELECT * FROM

      (SELECT 

             

              A.TRANS_DATE AS TRANSDATE,

              A.NARRATION,

              A.CUSTOMER_ID,

              A.REF_NO,

              A.DEBIT,

              A.CREDIT,

              A.TOTAL

      FROM FM_DEBTORS_DETAILED_OPEN A

      WHERE A.TRANS_DATE < TRANSDATE

      UNION ALL

      SELECT 

              B.TRANS_DATE AS TRANSDATE,

              B.NARRATION,

              B.CUSTOMER_ID,

              B.REF_NO,

              B.DEBIT,

              B.CREDIT,

              B.TOTAL

      FROM FM_DEBTORS_DETAILED_TRANS B

      WHERE B.TRANS_DATE BETWEEN TO_DATE(TRANSDATE,'DD-MON-YY') AND TO_DATE(SYSDATE,'DD-MON-YY')) C

      WHERE

      C.TRANSDATE = :TRANSDATE

      AND

      C.CUSTOMER_ID = 7;

        • 1. Re: How to pass a value inside a capsulation
          Gaz in Oz

          A few options. Depends on the client running the query.

          Both & and : are understood by sqlplus sqldeveloper sqlcl What ever client you are using may understand these.

          Is the transdate known at runtime or do you want to prompt the user running it?

           

          Here's an example:

          SQL> var transdate varchar2(11)
          SQL> exec :transdate := '01-AUG-2018';
          
          PL/SQL procedure successfully completed.
          
          SQL> SELECT * FROM
            2  (SELECT
            3          A.TRANS_DATE AS TRANSDATE,
            4          A.NARRATION,
            5          A.CUSTOMER_ID,
            6          A.REF_NO,
            7          A.DEBIT,
            8          A.CREDIT,
            9          A.TOTAL
           10  FROM FM_DEBTORS_DETAILED_OPEN A
           11  WHERE A.TRANS_DATE < TRANSDATE
           12  UNION ALL
           13  SELECT
           14          B.TRANS_DATE AS TRANSDATE,
           15          B.NARRATION,
           16          B.CUSTOMER_ID,
           17          B.REF_NO,
           18          B.DEBIT,
           19          B.CREDIT,
           20          B.TOTAL
           21  FROM FM_DEBTORS_DETAILED_TRANS B
           22  WHERE B.TRANS_DATE BETWEEN TO_DATE(:TRANSDATE,'DD-MON-YYYY') AND SYSDATE
           23* C.CUSTOMER_ID = 7;
          

          Always use 4 digit years.

          Don't use TO_DATE() on a DATE datatype. SYSDATE is a DATE so no need for to_date().

           

          You will benifit grately by reading these articles:

          PL/SQL 101 : Substitution vs. Bind Variables

          and

          PL/SQL 101 : DataTypes - DATE

          • 2. Re: How to pass a value inside a capsulation
            mathguy

            It is a particularly bad idea to use the same name for a column (or alias) in a table or a query, and for a bind variable in the same query. The parser will not be confused, but YOU will.

             

            In several places in your code, you have  TRANSDATE where you surely meant :TRANSDATE, the bind variable (the first character must be a colon), for example here:

             

            WHERE A.TRANS_DATE < TRANSDATE         -- should be :TRANSDATE, with a colon, as you have in other places in the query.

             

            Best to use a different name, not TRANSDATE, for the bind variable, as you are using the same name for a column in the result set (by way of an alias). Then remember to use the colon in front of the bind variable name EVERYWHERE, rather than just in some places, like you have it now.

            • 3. Re: How to pass a value inside a capsulation
              mNem
              SELECT * FROM
              (
                SELECT 
                        A.TRANS_DATE AS TRANSDATE,
                
                        A.NARRATION,
                
                        A.CUSTOMER_ID,
                
                        A.REF_NO,
                
                        A.DEBIT,
                
                        A.CREDIT,
                
                        A.TOTAL
                
                FROM FM_DEBTORS_DETAILED_OPEN A
                
                WHERE trunc(A.TRANS_DATE) = to_date(:TRANSDATE, 'DD-MON-YY')
                
                UNION ALL
                
                SELECT 
                
                        B.TRANS_DATE AS TRANSDATE,
                
                        B.NARRATION,
                
                        B.CUSTOMER_ID,
                
                        B.REF_NO,
                
                        B.DEBIT,
                
                        B.CREDIT,
                
                        B.TOTAL
                
                FROM FM_DEBTORS_DETAILED_TRANS B
                
                WHERE trunc(B.TRANS_DATE) = TO_DATE(:TRANSDATE,'DD-MON-YY') 
              
              
              ) C
              
              
              WHERE
              
              
              C.CUSTOMER_ID = 7;
              
              • 4. Re: How to pass a value inside a capsulation
                Ahmed Haroon

                please keep in mind first what Gaz in Oz mentioned in first line of his reply #1, there are other options like SYS_CONTEXT, PIPELINED table function, a package in which you defined variables, procedures, functions to achieve when you are using this type of queries in your application.

                i preferred to use pipelined table functions, these are more efficient in many ways, speed, memory consumption and to have full pl/sql advantages. below is an example using oracle's default HR schema.

                CREATE OR REPLACE PACKAGE pipeline_demo  
                AS  
                  TYPE rtype_employees IS  
                   RECORD 
                  (  
                    dept_id employees.department_id%TYPE,  
                    f_name employees.first_name%TYPE);  
                      
                  TYPE ttype_employees IS  
                   TABLE OF rtype_employees;  
                  
                  FUNCTION pipeline_function(  
                      iv_input IN VARCHAR2)  
                    RETURN ttype_employees pipelined;  
                  
                END pipeline_demo;  
                /  
                
                
                CREATE OR REPLACE PACKAGE body pipeline_demo  
                AS  
                  FUNCTION pipeline_function (  
                      iv_input IN VARCHAR2)  
                    RETURN ttype_employees pipelined  
                  IS  
                    lv_employees ttype_employees;  
                  BEGIN  
                    FOR i IN  
                    (SELECT department_id, first_name FROM employees WHERE department_id = iv_input  
                    )  
                    LOOP  
                      lv_employees.dept_id := i.department_id;  
                      lv_employees.f_name  := i.first_name;  
                      pipe row(lv_employees);  
                    END LOOP;  
                    RETURN;  
                  END pipeline_function;  
                END pipeline_demo;
                /
                
                
                select * from table(pipeline_function (30));
                
                
                • 5. Re: How to pass a value inside a capsulation
                  Etbin

                  Maybe

                   

                  select trans_date as transdate,

                         narration,

                         customer_id,

                         ref_no,

                         debit,

                         credit,

                         total

                    from fm_debtors_detailed_open

                  where trans_date between to_date(:transdate,'dd-mon-yy') and to_date(:transdate,'dd-mon-yy') + 86399 / 86400

                     and customer_id = 7

                  union all

                  select trans_date as transdate,

                         narration,

                         customer_id,

                         ref_no,

                         debit,

                         credit,

                         total

                    from fm_debtors_detailed_trans

                  where trans_date between to_date(:transdate,'dd-mon-yy') and to_date(:transdate,'dd-mon-yy') + 86399 / 86400

                     and customer_id = 7

                   

                   

                  to allow the use of (if existing) index on trans_date

                   

                  Regards

                   

                  Etbin

                  • 6. Re: How to pass a value inside a capsulation
                    Paulzip

                    I

                    mathguy wrote:

                     

                    It is a particularly bad idea to use the same name for a column (or alias) in a table or a query, and for a bind variable in the same query. The parser will not be confused, but YOU will.

                     

                    In several places in your code, you have TRANSDATE where you surely meant :TRANSDATE, the bind variable (the first character must be a colon), for example here:

                     

                    WHERE A.TRANS_DATE < TRANSDATE -- should be :TRANSDATE, with a colon, as you have in other places in the query.

                     

                    Best to use a different name, not TRANSDATE, for the bind variable, as you are using the same name for a column in the result set (by way of an alias). Then remember to use the colon in front of the bind variable name EVERYWHERE, rather than just in some places, like you have it now.

                    I don't agree here. I always name bind variables the same as the column they are binding with. I find it less confusing, it self documents what it is associated with. It also aids schema evolution when developing, if someone changes columns (moves, deletes, etc.) you simply search for all occurrences in the code (client and server) for that column name prefixed with the colon. In fact, having worked on many enterprise systems, bugs often occur when developers take your approach and bind variables get missed and so parse errors occur.

                    • 7. Re: How to pass a value inside a capsulation
                      3193685

                      I cannot use the binding variable because finally i need to put this query to a view. binding variable not supporting the view (:TRANSDATE)

                      • 8. Re: How to pass a value inside a capsulation
                        Sven W.

                        3193685 wrote:

                         

                        I cannot use the binding variable because finally i need to put this query to a view. binding variable not supporting the view (:TRANSDATE)

                        Simply expose the column to the view and pass a where clause to the view. The optimizer will pass this filter to the where clause of the UNIONED select.

                         

                         

                        SELECT *
                        FROM (  SELECT
                                    a.trans_date AS transdate,
                                    a.narration,
                                    a.customer_id,
                                    a.ref_no,
                                    a.debit,
                                    a.credit,
                                    a.total
                                FROM fm_debtors_detailed_open a
                                UNION ALL
                                SELECT
                                    b.trans_date AS transdate,
                                    b.narration,
                                    b.customer_id,
                                    b.ref_no,
                                    b.debit,
                                    b.credit,
                                    b.total
                                FROM fm_debtors_detailed_trans b
                            ) c
                        WHERE trunc(c.transdate) = TO_DATE(:transdate,'DD-MON-YY')
                        and c.customer_id = 7;
                        

                         

                        The inner select would be your view. And the outer where condition would be applied to the view.

                        • 9. Re: How to pass a value inside a capsulation
                          Etbin

                          Take a look at https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5002.htm#SQLRF01202

                           

                          Once created and set to be used as

                          to_date(sys_context(my_context_name,my_trans_date),'dd.mm.yyyy')

                          instead of

                          :transdate

                           

                          Regards

                           

                          Etbin

                          • 10. Re: How to pass a value inside a capsulation
                            L. Fernigrini

                            As Etbin already mentioned, using Contexts is one of simplest ways to apply filters to views based on information that is not known at the moment of creating the view.

                             

                            Think of CONTEXTS as a sets of variables available to your session, they can be set at any moment and used in PL/SQL and SQL code.