Re: Advanced SQL statements writing

From: ddf <oratune_at_msn.com>
Date: Thu, 16 Apr 2009 10:34:53 -0700 (PDT)
Message-ID: <04e70c25-6dec-427b-9d86-5ec5034192fb_at_k27g2000vbb.googlegroups.com>

On Apr 16, 9:11 am, Phper <hi.steven..._at_gmail.com> wrote:
> I wrote this SQL statement:
>
> SELECT *
> FROM  ((SELECT piecework_id,1+count(*)
> FROM view
> GROUP BY piecework_id) AS view1
> LEFT OUTER JOIN
> (SELECT piecework_id, sum(status)
> FROM submission
> GROUP BY piecework_id) AS submission1
> on view1.piecework_id=submission1.piecework_id);
>
> It can be executed successfully.
> This is the result.
> piecework_id    1+count(*)      piecework_id    sum(status)
> 8                             2                    8                      NULL
> 989                           2                    NULL                    NULL
>
> But I want the result to be
> piecework_id    1+count(*)      sum(status)
> 8                             2                  NULL
> 989                           2                  NULL
>
> How can I achieve this?

By actually learning the tool you're attempting to use? The carelessness of this query indicates your lack of knowledge of Oracle and your continued posting of RTFM questions indicates your serious lack of concern for the group members by treating this forum as your own, personal tutorial service. Possibly you're taking a course at some community college or trade school and the instructor you've drawn isn't the sharpest tool in the shed; that still does not excuse your willingness to pelt this newsgroup with your homework questions expecting others to jump in and dish out the answer. That being said let's look at this monstrosity; I am forced to create the tables using the minimal column definitions you've provided. I'll also change object names to not use reserved words:

SQL> create table myview(

  2          piecework_id varchar2(12) not null,
  3          piecework_name varchar2(40),
  4          manuf_dt        date

  5 );

Table created.

SQL>
SQL> create table submission(

  2          piecework_id varchar2(12) not null,
  3          status  number

  4 );

Table created.

SQL>
SQL> insert all
  2 into myview
  3 values('8','WhatAWork',sysdate-9)
  4 into myview
  5 values('989','Wow', sysdate-4)
  6 into submission
  7 values('8', null)
  8 into submission
  9 values('989', null)
 10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> Now let's run your query, modified to actually execute:

SQL> SELECT *
  2 FROM (SELECT piecework_id,1+count(*)

  3                  FROM myview
  4                  GROUP BY piecework_id) view1
  5          LEFT OUTER JOIN
  6          (SELECT piecework_id, sum(status)
  7                  FROM submission
  8                  GROUP BY piecework_id) submission1
  9          on (view1.piecework_id=submission1.piecework_id);

PIECEWORK_ID 1+COUNT(*) PIECEWORK_ID SUM(STATUS) ------------ ---------- ------------ -----------

989                   2 989          NULL
8                     2 8            NULL

And we get the result you've posted, but don't want. This is one bad aspect of being lazy and coding 'select * from ...'. Also you didn't proviode column aliases for the count(*) and sum() columns; you should always provide aliases for columns generated by function calls. We've dumped the data you've asked for, but not in the record format you really wanted, so how do we fix this? By coding the columns you DO want explicitly in the SELECT list:

SQL> select v.piecework_id, 1+count(v.piecework_id) piece_ct, sum (s.status) gen_status
  2 from myview v left outer join submission s on s.piecework_id = v.piecework_id
  3 group by v.piecework_id
  4 order by 1;

PIECEWORK_ID PIECE_CT GEN_STATUS
------------ ---------- ----------

8                     2 NULL
989                   2 NULL

SQL> Notice the left outer join (which is likely unnecessary since, as you've posted, you have no missing join key values in either table) does NOT involve inline views and also notice that only the columns you wanted have been returned. And note that all derived columns have aliases. And, gee, whiz, it required a lot less typing than your attempt. Why on earth you're adding 1 to the count(*) eludes me as it's the usual course of action to return the count(*) unadulterated. Such an act leads me to believe this is nothing more than a homework assignment; at least you've done SOME work (although far more than necessary) to complete the assigned task.

There is a WEALTH of information available at

http://tahiti.oracle.com

and I STRONGLY suggest you use it and stop posting your basic, RTFM questions here. You're welcome to return when you cannot understand the manuals or you're unable to get a query working because, provided you supply the work you've done, someone will help because this is a help forum. It is NOT a personal tutoring service, and if you don't want your posts ignored in the future you need to stop treating it like one.

David Fitzjarrell Received on Thu Apr 16 2009 - 12:34:53 CDT