6 Replies Latest reply: Apr 10, 2013 2:59 AM by Etbin RSS

    Full outer join query giving error

    SnigdhaBhanu
      Hi I have written the below query to get the fields below as an out put but am getting the error missing keyword..am not understanding where did i missed..i have done step by step query analysis..but could not find the error.

      Please help me in resolving the issue.

      Expected output Columns :*

      COUNTRY , TRN_TYPE ,SKU ,BIX_Customer ,PERIOD ,CURRENTSTOCK ,STOCK_VALUE ,SALES ,SALES_VALUE ,TARGET


      Query :_


      select (case when a.country is null then b.country when b.country is null then c.country else a.country end) AS COUNTRY,
      (case when a.Sale_Type is null then b.Stk_type when b.stk_type is null then c.Stk_type else a.Sale_Type end) AS TRN_TYPE,
      (case when a.sku is null then b.sku when b.sku is null then c.sku else a.sku end) AS SKU,
      (case when a.bix_customer is null then b.bix_customer_code when b.bix_customer_code is null then c.bix_customer_code else a.bix_customer end)AS BIX_Customer ,
      (case when a.period is null then TO_number(b.period) when b.period is null then TO_NUMBER(c.period) else a.period end) AS PERIOD,
      nvl(b.CURRENTSTOCK,0) AS CURRENTSTOCK,
      nvl(b.stock_value,0) AS STOCK_VALUE,
      nvl(a.sales,0) AS SALES,
      nvl(a.SALES_VALUE,0) AS SALES_VALUE,
      nvl(c.TARGET_QTY,0) AS TARGET
      from

      (select UPPER(c.cust_country_name) AS COUNTRY,
      DECODE(ds.account_key,7156,'SAMPLE',7157,'BONUS',7485,'SALE') AS Sale_Type,
      substr(i.item_code,7) AS SKU,
      c.bix_customer_code AS BIX_Customer,
      ds.descr as descr ,
      ds.period as period,
      sum(ds.quantity) AS SALES,
      sum(case when ds.local_value is null then ds.euro_value else ds.local_value END) AS SALES_VALUE
      FROM distributor_sales ds, customer c, item i
      where ds.customer_key=c.customer_key
      and ds.item_key= i.item_key
      group by ds.period,
      ds.account_key,
      c.cust_country_name,
      substr(i.item_code,7),
      c.bix_customer_code,
      ds.descr) a
      full outer join
      (SELECT UPPER(b.cust_country_name) AS COUNTRY,
      DECODE(s.stock_type,'SALE','SALE','SALES','SALE','BONUS','BONUS','SAMPLE','SAMPLE') AS Stk_type,
      substr(c.item_code,7) AS SKU,
      s.descr as descr,
      s.period as period,
      b.bix_customer_code,
      sum(s.CLOSING_STOCK) CURRENTSTOCK,
      sum(s.closing_stock*s.cif_price) STOCK_VALUE
      FROM STOCK s, customer b, item c
      WHERE s.customer_key=b.customer_key
      and s.item_key= c.item_key
      group by
      s.descr,
      s.stock_type,
      s.period, b.bix_customer_code,b.cust_country_name,substr(c.item_code,7) ) b
      full outer join
      (SELECT UPPER(cu.cust_country_name) AS COUNTRY,
      DECODE(t.description,'SALES TARGET','SALE') AS Stk_type,
      substr(it.item_code,7) AS SKU,
      t.channel as channel,
      t.period as period,
      cu.bix_customer_code as bix_customer_code,
      sum(t.quantity) TARGET_QTY
      FROM sales_target t, customer cu, item it
      WHERE t.customer_key=cu.customer_key
      and t.item_key= it.item_key
      group by
      t.channel,
      t.description,
      t.period, cu.bix_customer_code,cu.cust_country_name,substr(it.item_code,7) ) c

      on a.SKU=b.SKU
      and a.sku=c.SKU
      and b.sku=c.SKU
      and a.BIX_Customer=b.bix_customer_code
      and a.BIX_Customer=c.bix_customer_code
      and b.bix_customer_code=c.bix_customer_code
      and a.Sale_Type=b.Stk_type
      and a.Sale_Type=c.Stk_type
      and b.Stk_type=c.Stk_type
      and a.descr=b.descr
      and b.descr=c.channel
      and a.descr=c.channel
      and a.country=b.country
      and a.country=c.COUNTRY
      and b.country=c.COUNTRY
      and a.period=b.period
      and a.period=c.period
      and b.period=c.period;
        • 1. Baby Steps
          Frank Kulash
          Hi,

          That looks like over 75 lines of code. You didn't write that much at once, without testing, did you?
          Take baby steps. Write as little code as possible, perhaps 2 or 3 lines, then test that what you wrote does what you expect. If necessry, go back and fix it. When yu have that much working correctly, thn add another 1 or 2 lines, and repeat.

          For example, the first step migt be:
          SELECT       cust_country_name
          FROM       customer
          ;
          Make sure that works, then add another line or two, or maybe not even a complete line. The second step might be
          SELECT       UPPER (cust_country_name)     AS country
          FROM       customer
          ;
          When that is working correctly, then you might ty somehing like:
          SELECT       UPPER (cust_country_name)     AS country
          FROM       customer
          GROUP BY  cust_country_name
          ;
          Then test again.

          If you do get a syntax error, it will almost certainly be in the last 1 or 2 lines that you added, and so it will be much easier to find. If yu can't find it, then post a question, including the query that causes the error, and also the latest prior verson (1 or 2 lines shorter) that did not have an error.

          As always, fomat your code, and provide sample data (CREATE TABLE and INSERT statements) and the results you expect from that data and the query you posted. If you get an error message, post the complete error message, including line numbers.
          See the forum FAQ {message:id=9360002}
          • 2. Re: Full outer join query giving error
            pollywog
            currently you are trying to using full outer join like this
            select * from a full outer join b full outer join c
            on a.id = b.id
            and b.id = c.id;
            need to do it like this
            select * from a full outer join b on (a.id = b.id)
                            full outer join c on (b.id = c.id);
            • 3. Re: Full outer join query giving error
              jeneesh
              pollywog wrote:
              currently you are trying to using full outer join like this
              select * from a full outer join b full outer join c
              on a.id = b.id
              and b.id = c.id;
              That was a good catch..!
              • 4. Re: Full outer join query giving error
                SnigdhaBhanu
                Hi,

                Thank you for the suggestion... it was very helpful..i tried running the queries step by step.. if am executing individually the groups are working fine.. but if i execute the sales,stock and target table queries together am getting SQL not properly ended error.. please help me where am doing the mistake...

                below is the query which i composed

                select (case when x.country is null then y.country else x.country end) AS COUNTRY,
                (case when x.Sale_Type is null then y.Stk_type else x.Sale_Type end) AS TRN_TYPE,
                (case when x.sku is null then y.sku else x.sku end) AS SKU,
                (case when x.bix_customer is null then y.bix_customer_code else x.bix_customer end)AS BIX_Customer ,
                (case when x.period is null then TO_number(y.period) else x.period end) AS PERIOD,
                nvl(x.CURRENTSTOCK,0) AS CURRENTSTOCK,
                nvl(x.stock_value,0) AS STOCK_VALUE,
                nvl(x.sales,0) AS SALES,
                nvl(x.SALES_VALUE,0) AS SALES_VALUE,
                y.target_qty AS TARGET
                from

                ((select UPPER(c.cust_country_name) AS COUNTRY,
                DECODE(ds.account_key,7156,'SAMPLE',7157,'BONUS',7485,'SALE') AS Sale_Type,
                substr(i.item_code,7) AS SKU,
                c.bix_customer_code AS BIX_Customer,
                ds.descr,
                ds.period,
                sum(ds.quantity) AS SALES,
                sum(case when ds.local_value is null then ds.euro_value else ds.local_value END) AS SALES_VALUE
                FROM distributor_sales ds, customer c, item i
                where ds.customer_key=c.customer_key
                and ds.item_key= i.item_key
                group by ds.period,
                ds.account_key,
                c.cust_country_name,
                substr(i.item_code,7),
                c.bix_customer_code,
                ds.descr) a
                full outer join
                (SELECT UPPER(b.cust_country_name) AS COUNTRY,
                DECODE(s.stock_type,'SALE','SALE','SALES','SALE','BONUS','BONUS','SAMPLE','SAMPLE') AS Stk_type,
                substr(c.item_code,7) AS SKU,
                s.descr,
                s.period period,
                b.bix_customer_code,
                sum(s.CLOSING_STOCK) CURRENTSTOCK,
                sum(s.closing_stock*s.cif_price) STOCK_VALUE
                FROM STOCK s, customer b, item c
                WHERE s.customer_key=b.customer_key
                and s.item_key= c.item_key
                group by
                s.descr,
                s.stock_type,
                s.period, b.bix_customer_code,b.cust_country_name,substr(c.item_code,7) ) b
                on a.SKU=b.SKU
                and a.BIX_Customer=b.bix_customer_code
                and a.Sale_Type=b.Stk_type
                and a.descr=b.descr
                and a.country=b.country
                and a.period=b.period) x
                full outer join
                (SELECT UPPER(cu.cust_country_name) AS COUNTRY,
                DECODE(t.description,'SALES TARGET','SALE') AS Stk_type,
                substr(it.item_code,7) AS SKU,
                t.channel as channel,
                t.period as period,
                cu.bix_customer_code as bix_customer_code,
                sum(t.quantity) TARGET_QTY
                FROM sales_target t, customer cu, item it
                WHERE t.customer_key=cu.customer_key
                and t.item_key= it.item_key
                group by
                t.channel,
                t.description,
                t.period, cu.bix_customer_code,cu.cust_country_name,substr(it.item_code,7) ) y
                on x.SKU=y.SKU
                and x.BIX_Customer=y.bix_customer_code
                and x.Sale_Type=y.Stk_type
                and x.descr=y.channel
                and x.country=y.country
                and x.period=y.period
                • 5. Re: Full outer join query giving error
                  SnigdhaBhanu
                  Hi am getting below error message:

                  SQL not properly ended

                  Error at Command Line:51 Column:2
                  • 6. Re: Full outer join query giving error
                    Etbin
                    <tt>Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production</tt>
                    You must be aware that you must cascade full outer joins
                    with
                    t1 as
                    (select 1 key,1.1 data from dual union all
                     select 3,1.3 from dual union all
                     select 6,1.6 from dual union all
                     select 7,1.7 from dual
                    ),
                    t2 as
                    (select 2 key,2.2 data from dual union all
                     select 3,2.3 from dual union all
                     select 4,2.4 from dual union all
                     select 6,2.6 from dual
                    ),
                    t3 as
                    (select 4 key,3.4 data from dual union all
                     select 5,3.5 from dual union all
                     select 6,3.6 from dual union all
                     select 7,3.7 from dual
                    )
                    select nvl(x.key,t3.key) key,
                           x.data_1,
                           x.data_2,
                           t3.data data_3
                      from (select nvl(t1.key,t2.key) key,
                                   t1.data data_1,
                                   t2.data data_2
                              from t1
                                   full outer join
                                   t2
                                on t1.key = t2.key
                           ) x
                           full outer join
                           t3
                        on t3.key = x.key
                     order by key
                    unless you can live with duplicate key rows you cannot avoid whenever all table pairs contain common keys (try the below using data from above)
                    select coalesce(t1.key,t2.key,t3.key) key,
                           t1.data data_1,
                           t2.data data_2,
                           t3.data data_3
                      from t1
                           full outer join
                           t2
                        on t1.key = t2.key
                           full outer join
                           t3
                        on t3.key = t1.key
                       and t3.key = t2.key
                     order by key
                    
                    select coalesce(t1.key,t2.key,t3.key) key,
                           t1.data data_1,
                           t2.data data_2,
                           t3.data data_3
                      from t2
                           full outer join
                           t3
                        on t2.key = t3.key
                           full outer join
                           t1
                        on t1.key = t2.key
                       and t1.key = t3.key
                     order by key
                    
                    select coalesce(t1.key,t2.key,t3.key) key,
                           t1.data data_1,
                           t2.data data_2,
                           t3.data data_3
                      from t1
                           full outer join
                           t3
                        on t1.key = t3.key
                           full outer join
                           t2
                        on t2.key = t1.key
                       and t2.key = t3.key
                     order by key
                    Regards

                    Etbin

                    Edited by: Etbin on 10.4.2013 9:53
                    If you're after
                    select *
                      from (select *
                              from (select *
                                      from t1
                                   ) a
                                   full outer join
                                   (select *
                                      from t2
                                   ) b
                                on t1.key = t2.key
                           ) x
                           full outer join
                           t3
                        on t3.key = x.key
                     order by key
                    you're presently at
                    select *
                      from (
                                   (select *
                                      from t1
                                   ) a
                                   full outer join
                                   (select *
                                      from t2
                                   ) b
                                on t1.key = t2.key
                           ) x
                           full outer join
                           t3
                        on t3.key = x.key
                     order by key
                    ORA-00933: SQL command not properly ended