Re: How to write cursor?

Hi.

 If I understood your problem - then th answer is OUTER JOIN :  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
 E.ENTITY_ID = G.ENTITY_ID(+) AND
 E.ENTITY_ID = H.ENTITY_ID(+) AND
 E.ENTITY_ID = I.ENTITY_ID(+) AND
 E.ENTITY_ID = J.ENTITY_ID (+)

 HTH. Michael

In article <XNGm4.914$Iw3.74183_at_sapphire.mtt.net>,   "Craig Alexander" <craig_at_itas.net> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 05 2000 - 02:59:46 CST