Home » SQL & PL/SQL » SQL & PL/SQL » Strange error messages when using inline views and full outer join
Strange error messages when using inline views and full outer join [message #187573] Mon, 14 August 2006 09:13 Go to next message
westuss
Messages: 4
Registered: August 2006
Location: Pittsburgh
Junior Member
I'm writing a query for a report, and have run into a strange error message. I'm using Oracle 9i. I've run these queries using both TOAD and SQL*Plus on windows and SQL*Plus on the UNIX box.

Here's the query. The basic idea is to add up the usage and dollar data for each month, and compute a rate ($ / ton) for the specified location code and product category. This specific query will be used to provide a comparison between the business plan information, and the actual + short range forcast data. Some data might be in the actuals but not business plan, and vice versa, so I'm using a full outer join of two inline views.
SELECT bp.locationcd, bp.cfrpcategory, bp.matusage, bp.matdollars, bp.matrate, 
ytd_fcast.locationcd, ytd_fcast.cfrpcategory, ytd_fcast.matusage, ytd_fcast.matdollars, ytd_fcast.matrate
FROM 
      (SELECT  locationcd,
               cfrpcategory,
               SUM (matusage) matusage,
               SUM (matdollars) matdollars,
               SUM (matdollars) / SUM (matusage) matrate
          FROM vfa_cfrp_all_rawmaterials
         WHERE rptperiod >= :start_yyyymm AND rptperiod <= :end_yyyymm AND rpttype = :rpttype_in
      GROUP BY cfrpcategory,
               locationcd) bp 
	  FULL OUTER JOIN     	  
	  (SELECT   locationcd,
               cfrpcategory,
               SUM (matusage) matusage,
               SUM (matdollars) matdollars,
               SUM (matdollars) / SUM (matusage) matrate
          FROM vfa_cfrp_all_rawmaterials
         WHERE (    (rptperiod >= :start_act_yyyymm AND rptperiod <= :end_act_yyyymm AND rpttype = 'ACT')
                OR (    rptperiod >= :start_fcast_yyyymm
                    AND rptperiod <= :end_fcast_yyyymm
                    AND rpttype = :fcast_type_in) )
      GROUP BY cfrpcategory,
               locationcd) ytd_fcast 			   
			   ON (bp.locationcd = ytd_fcast.locationcd AND bp.cfrpcategory = ytd_fcast.cfrpcategory)


When I run this query, I get an error:
ORA-00904: "MATDOLLARS": invalid identifier

This error shouldn't be occuring, as this column does in fact exist on the view:
CREATE OR REPLACE VIEW afa0001.vfa_cfrp_all_rawmaterials 
(rptperiod,rpttype,rptversion,locationcd,cfrpcategory,matusage,matrate,matdollars)
AS
   SELECT u.rptperiod rptperiod,
          u.rpttype rpttype,
          u.rptversion rptversion,
          u.locationcd locationcd,
          u.cfrpcategory cfrpcategory,
          u.matusage,
          r.matrate,
          ROUND (matusage * matrate, 0) matdollars
     FROM afa0001.vfa_cfrp_all_matusage u,
          afa0001.vfa_cfrp_all_matrate r
    WHERE u.rptperiod = r.rptperiod
      AND u.rpttype = r.rpttype
      AND u.rptversion = r.rptversion
      AND u.locationcd = r.locationcd
      AND u.cfrpcategory = r.cfrpcategory


I thought I should start by simplifying the query to make sure that I had the syntax correct, so I wrote this query, just summing matdollars.
SELECT *
  FROM (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matdollars)
            FROM vfa_cfrp_all_rawmaterials
           WHERE rpttype = 'BP' AND rptperiod BETWEEN '200601' AND '200612'
        GROUP BY locationcd,
                 cfrpcategory) a
       FULL OUTER JOIN
       (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matdollars)
            FROM vfa_cfrp_all_rawmaterials
           WHERE (rptperiod BETWEEN '200601' AND '200606' AND rpttype = 'ACT')
              OR (rptperiod BETWEEN '200607' AND '200612' AND rpttype = 'SR4')
        GROUP BY locationcd,
                 cfrpcategory) b ON (a.locationcd = b.locationcd AND a.cfrpcategory = b.cfrpcategory)
       ;



I still get same error message.

Next, I summed the "matusage" column instead of "matdollars" column - and the query worked without any problems!!

So then, I tried using an inline view inside the inline views to select from the vfa_cfrp_all_rawmaterials database view:
SELECT *
  FROM (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matdollars)
            FROM (SELECT * FROM vfa_cfrp_all_rawmaterials)
           WHERE rpttype = 'BP' AND rptperiod BETWEEN '200601' AND '200612'
        GROUP BY locationcd,
                 cfrpcategory) a
       FULL OUTER JOIN
       (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matdollars)
            FROM (SELECT * FROM vfa_cfrp_all_rawmaterials)
           WHERE (rptperiod BETWEEN '200601' AND '200606' AND rpttype = 'ACT')
              OR (rptperiod BETWEEN '200607' AND '200612' AND rpttype = 'SR4')
        GROUP BY locationcd,
                 cfrpcategory) b ON (a.locationcd = b.locationcd AND a.cfrpcategory = b.cfrpcategory)
       ;


This query works! I don't understand why adding the inline view of (select * from vfa_cfrp_all_rawmaterials) changes anything, but it seems to. So then, I went to add this to my main query, so I could get it working:
SELECT bp.locationcd,
       bp.cfrpcategory,
       bp.matusage,
       bp.matdollars,
       bp.matrate,
       ytd_fcast.locationcd,
       ytd_fcast.cfrpcategory,
       ytd_fcast.matusage,
       ytd_fcast.matdollars,
       ytd_fcast.matrate
  FROM (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matusage) matusage,
                 SUM (matdollars) matdollars,
                 SUM (matdollars) / SUM (matusage) matrate
            FROM (SELECT *
                    FROM vfa_cfrp_all_rawmaterials)
           WHERE rptperiod >= :start_yyyymm AND rptperiod <= :end_yyyymm AND rpttype = :rpttype_in
        GROUP BY cfrpcategory,
                 locationcd) bp
       FULL OUTER JOIN
       (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matusage) matusage,
                 SUM (matdollars) matdollars,
                 SUM (matdollars) / SUM (matusage) matrate
            FROM (SELECT *
                    FROM vfa_cfrp_all_rawmaterials)
           WHERE (    (rptperiod >= :start_act_yyyymm AND rptperiod <= :end_act_yyyymm AND rpttype = 'ACT')
                  OR (    rptperiod >= :start_fcast_yyyymm
                      AND rptperiod <= :end_fcast_yyyymm
                      AND rpttype = :fcast_type_in) )
        GROUP BY cfrpcategory,
                 locationcd) ytd_fcast
       ON (bp.locationcd = ytd_fcast.locationcd AND bp.cfrpcategory = ytd_fcast.cfrpcategory)


Now, instead of the previous error, I get this error:
ORA-03113: end-of-file on communication channel

I have no idea where to go from here. I can probably work around this using PL/SQL, seperate selects, and some IF statements, but I'd like to try this in SQL.

Could it have something to do with the number of views accessed? I'm querying the same view with each inline view, and that view queries 2 views, which each query 3 tables.

I'd appreciate any help with this. Thanks.
Re: Strange error messages when using inline views and full outer join [message #187980 is a reply to message #187573] Wed, 16 August 2006 10:34 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Maybe very basic, but did you try giving each column a table alias with it?


...
SUM (a.matdollars) / SUM (a.matusage) matrate
FROM vfa_cfrp_all_rawmaterials a
...
FULL OUTER JOIN     	  
 (SELECT   ...
  SUM (b.matdollars) / SUM (b.matusage) matrate
  FROM vfa_cfrp_all_rawmaterials b
...


Regards,
Sabine
Re: Strange error messages when using inline views and full outer join [message #187985 is a reply to message #187980] Wed, 16 August 2006 10:41 Go to previous messageGo to next message
westuss
Messages: 4
Registered: August 2006
Location: Pittsburgh
Junior Member
skooman wrote on Wed, 16 August 2006 11:34

Maybe very basic, but did you try giving each column a table alias with it?


Thanks for the reply, but I've tried it with aliases and I still get the message, this time as
ORA-00904: "A"."MATDOLLARS": invalid identifier


I am now working around this using PL/SQL. However, if anyone has any ideas to get the query working, I'd appreciate it, as an academic exercise.
Re: Strange error messages when using inline views and full outer join [message #188100 is a reply to message #187573] Thu, 17 August 2006 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did you try creating the original view without the line in the CREATE VIEW part that specifies the column names?

ie
CREATE OR REPLACE VIEW afa0001.vfa_cfrp_all_rawmaterials 
AS
   SELECT u.rptperiod rptperiod,
          u.rpttype rpttype,
          u.rptversion rptversion
...
Re: Strange error messages when using inline views and full outer join [message #188148 is a reply to message #188100] Thu, 17 August 2006 05:29 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Suddenly, I recall (vaguely) that there was a bug with the new (ansi) syntax for joins, returning an ora-00904 falsely when the total number of columns exceeds a 1000 or something.

I couldn't find any proof of this, but I'm pretty sure about it. Full outer join is some specific ANSI feature, but could you please try writing something very similar using the original syntax to see if you still get the 00904?

Regards,
Sabine
Re: Strange error messages when using inline views and full outer join [message #188422 is a reply to message #188148] Fri, 18 August 2006 08:38 Go to previous message
westuss
Messages: 4
Registered: August 2006
Location: Pittsburgh
Junior Member
skooman wrote on Thu, 17 August 2006 06:29

Suddenly, I recall (vaguely) that there was a bug with the new (ansi) syntax for joins, returning an ora-00904 falsely when the total number of columns exceeds a 1000 or something.

I couldn't find any proof of this, but I'm pretty sure about it. Full outer join is some specific ANSI feature, but could you please try writing something very similar using the original syntax to see if you still get the 00904?



I rewrote the query using the oracle join operator (+) and a UNION (for the full outer join), and the query returns what I'm expecting. I was hoping to avoid the large query as it makes maintenance a hassle, the FULL OUTER JOIN syntax is so much more readable (at least to me).

Oh well, the problem has been solved in PL/SQL. Next time I'll just use the oracle join syntax.

Thanks for your help.

SELECT bp.locationcd,
       bp.cfrpcategory,
       bp.matusage,
       bp.matdollars,
       bp.matrate,
       ytd_fcast.locationcd,
       ytd_fcast.cfrpcategory,
       ytd_fcast.matusage,
       ytd_fcast.matdollars,
       ytd_fcast.matrate
  FROM (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matusage) matusage,
                 SUM (matdollars) matdollars,
                 (SUM (matdollars) / SUM (matusage) ) matrate
            FROM vfa_cfrp_all_rawmaterials
           WHERE rptperiod >= :start_yyyymm AND rptperiod <= :end_yyyymm AND rpttype = :rpttype_in
        GROUP BY cfrpcategory,
                 locationcd) bp,
       (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matusage) matusage,
                 SUM (matdollars) matdollars,
                 SUM (matdollars) / SUM (matusage) matrate
            FROM vfa_cfrp_all_rawmaterials
           WHERE (    (rptperiod >= :start_act_yyyymm AND rptperiod <= :end_act_yyyymm AND rpttype = 'ACT')
                  OR (    rptperiod >= :start_fcast_yyyymm
                      AND rptperiod <= :end_fcast_yyyymm
                      AND rpttype = :fcast_type_in) )
        GROUP BY cfrpcategory,
                 locationcd) ytd_fcast
 WHERE bp.locationcd = ytd_fcast.locationcd(+) AND bp.cfrpcategory = ytd_fcast.cfrpcategory(+)
UNION
SELECT bp.locationcd,
       bp.cfrpcategory,
       bp.matusage,
       bp.matdollars,
       bp.matrate,
       ytd_fcast.locationcd,
       ytd_fcast.cfrpcategory,
       ytd_fcast.matusage,
       ytd_fcast.matdollars,
       ytd_fcast.matrate
  FROM (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matusage) matusage,
                 SUM (matdollars) matdollars,
                 (SUM (matdollars) / SUM (matusage) ) matrate
            FROM vfa_cfrp_all_rawmaterials
           WHERE rptperiod >= :start_yyyymm AND rptperiod <= :end_yyyymm AND rpttype = :rpttype_in
        GROUP BY cfrpcategory,
                 locationcd) bp,
       (SELECT   locationcd,
                 cfrpcategory,
                 SUM (matusage) matusage,
                 SUM (matdollars) matdollars,
                 SUM (matdollars) / SUM (matusage) matrate
            FROM vfa_cfrp_all_rawmaterials
           WHERE (    (rptperiod >= :start_act_yyyymm AND rptperiod <= :end_act_yyyymm AND rpttype = 'ACT')
                  OR (    rptperiod >= :start_fcast_yyyymm
                      AND rptperiod <= :end_fcast_yyyymm
                      AND rpttype = :fcast_type_in) )
        GROUP BY cfrpcategory,
                 locationcd) ytd_fcast
 WHERE bp.locationcd(+) = ytd_fcast.locationcd AND bp.cfrpcategory(+) = ytd_fcast.cfrpcategory

Previous Topic: WHats next
Next Topic: stored proc > error doesn't fall in the exception block
Goto Forum:
  


Current Time: Fri Dec 09 06:05:09 CST 2016

Total time taken to generate the page: 0.08591 seconds