Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2013
    Posts
    10

    Unanswered: Fire query on condition basis.

    Hi,
    I am new to DB2. Please help me to resolve my issue.

    I need to fire sql query in DB2 on condition basis. Like if the value is "All" then I need to get all data from view but if value is something else than data should be on the basis of that value.

    Example:
    SELECT M.Col1, M.Col2, M.Col3 FROM view M WHERE M.Col1 = {?param} and D.Col2 = '{?paramC}'

    Now if the value of ?paramC is 'All' then the then query should be
    SELECT M.Col1, M.Col2, M.Col3 FROM
    view M WHERE M.Col1 = {?param} // means ?paramC is rejected if 'All' is selected

    else
    SELECT M.Col1, M.Col2, M.Col3 FROM view M
    WHERE M.Col1 = {?param}
    and D.Col2 = '{?paramC}' // means if ?paramC is some value like 1,2,3 etc

    Hope I am clear on my question. I am stuck here. Please help as it it is urgent.
    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) What means D.Col2?
    There was no table, view, nor alias named D in your query.

    (2) How to get the values of ?param and ?paramC ?
    Do you want to execute the query in a function or stored procedure?

  3. #3
    Join Date
    Nov 2013
    Posts
    10
    Sorry, that column is M.Col2.

    SELECT M.Col1, M.Col2, M.Col3 FROM view M WHERE M.Col1 = {?param} and M.Col2 = '{?paramC}'

    Now if the value of ?paramC is 'All' then the then query should be
    SELECT M.Col1, M.Col2, M.Col3 FROM
    view M WHERE M.Col1 = {?param} // means ?paramC is rejected if 'All' is selected

    else
    SELECT M.Col1, M.Col2, M.Col3 FROM view M
    WHERE M.Col1 = {?param}
    and M.Col2 = '{?paramC}' // means if ?paramC is some value like 1,2,3 etc
    .........

    I am getting the above parameter ?param and ?paramC from some report and according to that I need to fire above query. I am new to so dont know in how many ways we can do that.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to use IN predicate, like this way

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT parmC
         , deptno
         , deptname
     FROM  department
     CROSS JOIN
           (VALUES 'A00' , 'All' ) AS p(parmC)
     WHERE parmC IN (deptno , 'All')
     ORDER BY
           parmC
         , deptno
    ;
    ------------------------------------------------------------------------------
    
    PARMC DEPTNO DEPTNAME                            
    ----- ------ ------------------------------------
    A00   A00    SPIFFY COMPUTER SERVICE DIV.        
    All   A00    SPIFFY COMPUTER SERVICE DIV.        
    All   B01    PLANNING                            
    All   C01    INFORMATION CENTER                  
    All   D01    DEVELOPMENT CENTER                  
    All   D11    MANUFACTURING SYSTEMS               
    All   D21    ADMINISTRATION SYSTEMS              
    All   E01    SUPPORT SERVICES                    
    All   E11    OPERATIONS                          
    All   E21    SOFTWARE SUPPORT                    
    
      10 record(s) selected.

  5. #5
    Join Date
    Nov 2013
    Posts
    10
    Hi Thanks for reply.
    I have already used Inner Join in query. Can we use crossjoin with that?
    I am giving you full query now. I think It will clear the problem more.

    ?param and ?paramc --> crystal report parameter

    Query:
    SELECT
    M.D1,
    M.D2,
    M.D3,
    M.D4,
    M.D5,
    M.D6
    FROM
    View M
    INNER JOIN
    SomeView S
    ON
    ((D.D1 = S.colname)
    AND
    (D.D4 = S.colname))
    WHERE
    D.D2 = {?param} and
    D.D3 = '{?paramC}'
    ----------------------------------
    Above is the full query..
    Now somehow I want to fire conditional query.
    like

    if('{?paramC}'='All')
    then
    SELECT
    M.D1,
    M.D2,
    M.D3,
    M.D4,
    M.D5,
    M.D6
    FROM
    View M
    INNER JOIN
    SomeView S
    ON
    ((D.D1 = S.colname)
    AND
    (D.D4 = S.colname))
    WHERE
    D.D3 = {?param}

    else
    {
    SELECT
    M.D1,
    M.D2,
    M.D3,
    M.D4,
    M.D5,
    M.D6
    FROM
    View M
    INNER JOIN
    SomeView S
    ON
    ((D.D1 = S.colname)
    AND
    (D.D4 = S.colname))
    WHERE
    D.D3 = {?param} and
    D.D4 = '{?paramC}'
    }
    What is the simple way of doing this.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Read my previous query example.

    Apply the way to your query,
    it might be something like...
    '{?parmC}' IN (D.D4 , 'All')

    If ?parmC was 'All',
    the predicate will be always TRUE unrelated to the value of D.D4.
    If ?parmC was not 'All',
    the predicate is equivalent to ?parmC = D.D4.

    I don't know Crystal report. So, I can't show you exact code.
    Last edited by tonkuma; 11-11-13 at 08:15.

  7. #7
    Join Date
    Nov 2013
    Posts
    10
    I am getting some issues while executing above stuff. Can I use inner join and cross join together in one query. As I am already using Inner join. Please help me out.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can mix any type of joins.

    But, I used cross join to supply test data for parmC.
    So, I don't think you need to use cross join at this time.


    Anyway,
    Please see manuals for more details.

    For example,
    see table-reference and joined-table
    in subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

    joined-table include table-reference.
    table-reference include joined-table.
    So, table-reference in joined-table can be another joined-table.

    table-reference

    Code:
    >>-+-table-name--+------------------------+--+------------------------+---------------+-><
       |             '-| correlation-clause |-'  '-| tablesample-clause |-'               |   
       +-+-+-nickname--+-------------------+--+------------------------+------------------+   
       | | '-view-name-'                   |  '-| correlation-clause |-'                  |   
       | '-+-ONLY--+--(--+-table-name-+--)-'                                              |   
       |   '-OUTER-'     '-view-name--'                                                   |   
       +-TABLE--(--function-name--(--+----------------+--)--)--+------------------------+-+   
       |                             | .-,----------. |        '-| correlation-clause |-' |   
       |                             | V            | |                                   |   
       |                             '---expression-+-'                                   |   
       |                     (1)                                                          |   
       +-xmltable-expression------+------------------------+------------------------------+   
       |                          '-| correlation-clause |-'                              |   
       +-| analyze_table-expression |--+------------------------+-------------------------+   
       |                               '-| correlation-clause |-'                         |   
       +-| nested-table-expression |--+------------------------+--------------------------+   
       |                              '-| correlation-clause |-'                          |   
       +-| data-change-table-reference |--+------------------------+----------------------+   
       |                                  '-| correlation-clause |-'                      |   
       +-| collection-derived-table |--+------------------------+-------------------------+   
       |                               '-| correlation-clause |-'                         |   
       '-joined-table---------------------------------------------------------------------'
    joined-table

    Code:
                          .-INNER-----.                                                
    >>-+-table-reference--+-----------+--JOIN--table-reference--ON--join-condition-+-><
       |                  '-| outer |-'                                            |   
       +-table-reference--CROSS JOIN--table-reference------------------------------+   
       '-(--joined-table--)--------------------------------------------------------'
    outer

    Code:
                  .-OUTER-.   
    |--+-LEFT--+--+-------+-----------------------------------------|
       +-RIGHT-+              
       '-FULL--'

  9. #9
    Join Date
    Nov 2013
    Posts
    10
    Thanks for reply. I tried that but dint solve my issue then I tried the following query and I guess its working.
    I tried the condition like:

    WHERE
    D.D3 = {?param} and
    (D.D4 = '{?paramC}' or '{?paramC}' ='All')


    If paramC is 2 then data will come out on this value based and if 'All' than it send the true for () bracket condition and all data will come out.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... I tried that but dint solve my issue then I tried the following query and I guess its working.
    I tried the condition like:

    WHERE
    D.D3 = {?param} and
    (D.D4 = '{?paramC}' or '{?paramC}' ='All')
    I couldn't beleive
    why '{?paramC}' IN (D.D4 , 'All') didn't work,
    if (D.D4 = '{?paramC}' or '{?paramC}' ='All') worked.

    When you tried like:
    WHERE
    D.D3 = {?param} and
    '{?paramC}' IN (D.D4 , 'All')

    What result did you got?
    Any error messages?
    or were the results different from your expected results?

  11. #11
    Join Date
    Nov 2013
    Posts
    10
    Hi
    when the value is present means not All then it shows the result against that particular value. But when the value is not present then it should go for All but its showing me output like:
    out put : 0 record selected

    D.D3 = {?param} and
    '{?paramC}' IN (D.D4 , 'All')
    Where D.D4 value does not exist in database but still "All" did not work.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand your explanation.

    You didn't answer my question
    When you tried like:
    WHERE
    D.D3 = {?param} and
    '{?paramC}' IN (D.D4 , 'All')

    What result did you got?
    Any error messages?
    or were the results different from your expected results?
    Did you tried '{?paramC}' IN (D.D4 , 'All') with the value 'All' for ?parmC?


    And do you undersand that
    '{?paramC}' IN (D.D4 , 'All')
    is equivalent to
    ('{?paramC}' = D.D4 OR '{?paramC}' = 'All')
    which is equivalent to the condition you tried
    (D.D4 = '{?paramC}' or '{?paramC}' ='All')
    ... I tried the following query and I guess its working.
    I tried the condition like:

    WHERE
    D.D3 = {?param} and
    (D.D4 = '{?paramC}' or '{?paramC}' ='All')

    ...

  13. #13
    Join Date
    Nov 2013
    Posts
    10
    Sorry I was trying something else.
    Yes, You are right. That query you have sent is perfectly working. Thanks..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •