Re: How to write cursor?

From: Michael Cretan <mcretan_at_ozemail.com.au>
Date: Sun, 6 Feb 2000 15:50:03 +1100
Message-ID: <F07n4.19385$3b6.81003@ozemail.com.au>

You could also try using a group by expression as follows:

SELECT max(decode(ATTR_NAME,'B_EMAIL' ,VALUE,NULL)) as Email

   , max(decode(ATTR_NAME,'CONTACT_FIRST_NAME' ,VALUE,NULL))||' '||
     max(decode(ATTR_NAME,'CONTACT_LAST_NAME' ,VALUE,NULL)) as Name
   , max(decode(ATTR_NAME,'B_CITY' ,VALUE,NULL)) as City
   , max(decode(ATTR_NAME,'B_ADDR_LINE1' ,VALUE,NULL)) as Address
   , max(decode(ATTR_NAME,'B_POSTAL_CODE' ,VALUE,NULL)) as Postal_Code

  from svc_data
  group by ENTITY_ID;

The max(decode( ... bit basically amalgamates the multiple records into one record for the entitiy ID

If you have an index on ATTR_NAME, ENTITY_ID, the query can fetch all necessary data from the index - so no table access required. Should be nice and quick.

Good luck.

---

Mike Cretan
Oracle Consultant
Tasmania, Australia
email: mcretan_at_ozemail.com.au

---

Craig Alexander <craig_at_itas.net> wrote in message news:XNGm4.914$Iw3.74183_at_sapphire.mtt.net...
> I need to write a cursor and need advice.I'm using PL/SQL Oracle 7 db
> Here is what my SQL statement looks like at the moment;
>
> SELECT distinct G.VALUE as Email, E.VALUE||' '||F.VALUE as Name, H.VALUE
as
> City, J.VALUE as Address, I.VALUE as Postal_Code
> FROM SVC_DATA E, SVC_DATA F, SVC_DATA G, SVC_DATA H, SVC_DATA I, SVC_DATA
J
> WHERE E.ATTR_NAME = 'CONTACT_FIRST_NAME' AND
> F.ATTR_NAME = 'CONTACT_LAST_NAME' AND
> G.ATTR_NAME = 'B_EMAIL' AND
> H.ATTR_NAME = 'B_CITY' AND
> I.ATTR_NAME = 'B_POSTAL_CODE' AND
> J.ATTR_NAME = 'B_ADDR_LINE1' AND
> E.ENTITY_ID = F.ENTITY_ID AND
> F.ENTITY_ID = G.ENTITY_ID AND
> G.ENTITY_ID = H.ENTITY_ID AND
> H.ENTITY_ID = I.ENTITY_ID AND
> I.ENTITY_ID = J.ENTITY_ID
>
> The problem is that all (6) ATTR_NAME have to be present or the query will
> not return the record.
> I need to write a cursor that will return all records even if they do not
> have all (6) ATTR_NAME
>
> I'm very new to cursors and would appreciate anyones help.
>
> Thanks in advance!
> --
> Craig Alexander
> Island Tel Advanced Solutions
>
>
Received on Sat Feb 05 2000 - 22:50:03 CST