Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to write cursor?
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_Codefrom svc_data
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.
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