Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: Please Help for crystal reports

    Hi All,

    I am using crystal reports with my sql.I have 2 command objects and i need to apply full outer join between those two But as we no crystal reports links tab won't support for full outer join.So i gave full outer join in data base level by using left outer join union right outer between those 2 command objects.

    It is working fine.but i have many common columns in both the queries .Take an example there is one column called Till Number.There is a case that if command1.tillnumber is null then i need to display command2.tillnumber but now we are applying full outer join,so it is giving all the data.So how can i get query 1 data if it is not null.


    Please suggest

  2. #2
    Join Date
    Mar 2013
    Posts
    25

    Full Outer Join Help please

    Hi All,

    I am using my sql in my crystal reports
    I have 2 queries and i need to apply full outer join between those two But as we no my sql won't support for full outer join.So i gave full outer join by using left outer join union right outer between those 2 command objects.

    It is working fine.but i have many common columns in both the queries .Take an example there is one column called Till Number.There is a case that if command1.tillnumber is null then i need to display command2.tillnumber but now we are applying full outer join,.I mean we have only one query.so it is giving all the data.So how can i get query 1 data if it is not null.


    The query for full outer join is below:


    SELECT q1.pos_no
    , q1.pos_name
    , q1.start_trans_id
    , q1.end_trans_id
    , q1.EodDeposit
    , q2.net
    FROM ( <<<QUERY:1>>> ) AS q1
    LEFT OUTER JOIN
    ( <<<QUERY:2>>> ) AS q2
    ON q2.pos_no = q1.pos_no
    UNION
    SELECT q2.pos_no
    , q2.pos_name
    , q1.start_trans_id
    , q1.end_trans_id
    , q1.EodDeposit
    , q2.net
    FROM ( <<<QUERY:1>>> ) AS q1
    RIGHT OUTER JOIN
    ( <<<QUERY:2>>> ) AS q2
    ON q1.pos_no = q2.pos_no
    ;


    Please suggest
    Last edited by divyavani; 07-01-13 at 04:23.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's some SQL code (MSSQL) to illustrate:
    Code:
    DECLARE @x table (
       a int
     , x int
    )
    
    DECLARE @y table (
       b int
     , y int
    )
    
    INSERT INTO @x (a, x)
      VALUES (1, 1)
           , (2, 2)
           , (3, 3)
           , (4, 4)
    
    INSERT INTO @y (b, y)
      VALUES (5, 5)
           , (2, 2)
           , (3, 3)
           , (4, 4)
    
    SELECT *
    FROM   @x As x
     FULL
      JOIN @y As y
        ON y.b = x.a
    ORDER
        BY x.a
    
    SELECT Coalesce(z.a, z.b)
         , *
    FROM   (
            SELECT *
            FROM   @x As x
             LEFT
              JOIN @y As y
                ON y.b = x.a
    
            UNION
    
            SELECT *
            FROM   @x As x
             RIGHT
              JOIN @y As y
                ON y.b = x.a
           ) As z
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Threads merged.


    Incidentally, the logic for the query will be the same in MySQL
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2013
    Posts
    25
    Hi George,

    I am new to MySQL. I am unable to understand the code that you written.Can you please explain the code with the columns that i have in my query please.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is your query. My changes have been highlighted in red
    Code:
    SELECT q1.pos_no As pos_no_q1
    , q2.pos_no As pos_no_q2
    , q1.pos_name
    , q1.start_trans_id
    , q1.end_trans_id
    , q1.EodDeposit
    , q2.net
    FROM ( <<<QUERY:1>>> ) AS q1
    LEFT OUTER JOIN
    ( <<<QUERY:2>>> ) AS q2
    ON q2.pos_no = q1.pos_no
    UNION
    SELECT q1.pos_noAs pos_no_q1
    , q2.pos_no As pos_no_q2
    , q2.pos_name
    , q1.start_trans_id
    , q1.end_trans_id
    , q1.EodDeposit
    , q2.net
    FROM ( <<<QUERY:1>>> ) AS q1
    RIGHT OUTER JOIN
    ( <<<QUERY:2>>> ) AS q2
    ON q1.pos_no = q2.pos_no
    Imagine this whole thing is <<<QUERY:3>>>
    Try something like this:
    Code:
    SELECT Coalesce(pos_no_q1, pos_no_q2)
         , *
    FROM   ( <<<QUERY:1>>> ) As q3
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2013
    Posts
    25
    Hi George,

    I tried what you said for first part i am getting some result and in below query query1 means the above whole query or query1 in above query and please tell what is q3 also

    SELECT Coalesce(pos_no_q1, pos_no_q2)
    , *
    FROM ( <<<QUERY:1>>> ) As q3

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, copy-paste error
    Code:
    SELECT Coalesce(pos_no_q1, pos_no_q2)
         , *
    FROM   ( <<<QUERY:3>>> ) As q3
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2013
    Posts
    25
    Hi George,

    Small help,

    I want both queries common columns only with nulls also.Then in my report level i will apply if else conditions like below.So i think for this Coalesce is not required only 1st query is enough ?.Please suggest

    if pos_no q1 is null then pos_no q2

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Coalesce applies your logic in the query. It takes the first non-null value
    i.e. if pos_no_q1 is not null, use that. If it is, use pos_no_q2.

    Have you build and run the query to see the results?
    George
    Home | Blog

Posting Permissions

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