RE: count(*) = 0 and I still need the row

From: Javier Morales <Javier_at_infojobs.net>
Date: Thu, 16 Nov 2000 21:23:48 +0100
Message-Id: <10682.122300@fatcity.com>

hmmm...
I think it's simple...

you want the select "inserts" nulls for joining the rows for the other = table
(this is, an outer join! ;-)

so, the PV table will contain null values (you count with the NVL = function!
;-)

so, how about your date compare to NULL ??? ;-))))

        SELECT=20
		hp.URL,
		nvl(count(pv.HTML_PAGE_ID),0)
        FROM PAGE_VIEWS pv, HTMP_PAGE hp
        WHERE hp.PAGE_ID =3D pv.HTML_PAGE_ID (+)
        AND pv.VIEW_DATE > add_months(SYSDATE, -1);
	GROUP BY hp.URL

In the outer join, the field pv.VIEW_DATE is NULL !!! ;-))) If you still want this fields to appear, use the NVL function to return = a
ficticious date (or else! ;-)

Hope this helps you!!=20
good luck,

---

 Javier Morales - javier_at_infojobs.net
 Administraci=F3n Bases de Datos Oracle - InfoJobs.net  Tel. +34 902 10 60 90 Ext. 2067
 Fax. +34 (93) 580 56 60
=20

-----Mensaje original-----
De: Dave Morgan [mailto:dmorgan_at_bartertrust.com] Enviado el: jueves 16 de noviembre de 2000 20:10 Para: Multiple recipients of list ORACLE-L Asunto: count(*) =3D 0 and I still need the row

Hi All,

	Thanks to Javier, Ron, William and Deepak for their efforts
	I have not tried Ron's solution of pre-setting the variable=20
	yet, thats next.

I have tried outer joins:
        SELECT
                hp.URL,
		nvl(count(pv.HTML_PAGE_ID),0)
        FROM PAGE_VIEWS pv, HTMP_PAGE hp
        WHERE hp.PAGE_ID =3D pv.HTML_PAGE_ID(+)
	GROUP BY hp.URL

Returns=20
	The HideAway    	       1
	The Townsend                   0
	Tony's Place                   0=09
	Yet Another                    0
	available                      0
	creepy condo's                 2
	slumpers                       3
	twomonthsago                   2

Add in the date restriction:
        SELECT=20
		hp.URL,
		nvl(count(pv.HTML_PAGE_ID),0)
        FROM PAGE_VIEWS pv, HTMP_PAGE hp
        WHERE hp.PAGE_ID =3D pv.HTML_PAGE_ID (+)
        AND pv.VIEW_DATE > add_months(SYSDATE, -1);
	GROUP BY hp.URL

Returns=20
	The HideAway    	       1
	creepy condo's                 2
	slumpers                       3

And people believe I know what I'm doing????

It does process the row "twomonthsago" semi-properly as those pageviews = are
more than 1 month ago. (Yes the data was created to be meaningful) I say semi-properly because it should display

	twomonthsago                   0

=20
Anyone know why the the date test alters the outer join results?

TIA
Dave
--=20

Dave Morgan
Senior Database Administrator
Internet Barter Inc.
www.bartertrust.com
408-982-8774
--=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--=20

Author: Dave Morgan
  INET: dmorgan_at_bartertrust.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Thu Nov 16 2000 - 14:23:48 CST