Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Question Unanswered: Nested SQL & ROWNUM problem

    Hi,

    I'm trying to figure out if something is possible without the use of procedures.

    I have a SQL statement that has to query a table and then return values from a secondary (property table) table in additional columns within the original query.

    The code is as follows:

    Code:
    SELECT *
      FROM (  SELECT o1.OBJNAME AS "Name",
                     o1.OBJUID AS "UID",
                     (SELECT StrValue
                        FROM VW_OBJPR_NT itf
                       WHERE (    (OBJOBID = o1.obid)
                              AND (PROPERTYDEFUID = 'Description')
                              AND ROWNUM = 1))
                        AS "Description",
                     (SELECT StrValue
                        FROM VW_OBJPR_NT itf
                       WHERE (    (OBJOBID = o1.obid)
                              AND (PROPERTYDEFUID = 'ContainerID')
                              AND ROWNUM = 1))
                        AS "ContainerID",
                     (SELECT o.OBJNAME
                        FROM VW_OBJ_NT o, VW_REL_NT r
                       WHERE (    (o.OBJUID = r.UID2)
                              AND (r.UID1 = o1.OBJUID)
                              AND (r.DEFUID = 'SPFSectionFormPurpose')
                              AND ROWNUM = 1))
                        AS "Rel1"
                FROM VW_OBJ_NT o1
               WHERE (o1.OBJDEFUID = 'SPFSection')
            ORDER BY OBJNAME)
     WHERE ("ContainerID" LIKE '%WP%')
    The problem is the "Rel1" subquery. The above works fine for the first row, however I need to also return the 2nd, 3rd etc. rows if there are any (and thus have "Rel2", "Rel3" subqueries etc.)

    I realise this is horribly inefficient, but havent figured out any other way of doing it.

    Tables are as follows:

    VW_OBJ_NT
    Code:
    OBID		OBJUID		OBJNAME		OBJDEFUID 	
    001H9VA		SCT_MergeStatus	MergeStatus	SPFSection
    001H4HA		FP_GetInfo	GetInfo		SPFFormPurpose
    001H4UA		FP_Query	Query		SPFFormPurpose
    VW_REL_NT
    Code:
    				
    OBID		OBJUID				UID1		UID2		DEFUID
    001H9WA		SCT_MergeStatus.FP_GetInfo	SCT_MergeStatus	FP_GetInfo	SPFSectionFormPurpose
    001H9XA		SCT_MergeStatus.FP_Query	SCT_MergeStatus	FP_Query	SPFSectionFormPurpose
    VW_OBJPR_NT
    Code:
    				
    OBID		OBJOBID		PROPERTYDEFUID		STRVALUE	
    00NLAWA		001H9VA		Description		Merge status for objects and documents	
    00NLAXA		001H9VA		ContainerID		Core.Forms

    As you can see the Rel table as 2 rels. The first rel returns "GetInfo" as its the first row from the sub query. My problem is getting back Row 2.

    I have tried this:
    Code:
                     (SELECT OBJNAME
                        FROM (SELECT ROWNUM X, o.OBJNAME
                                FROM VW_OBJ_NT o, VW_REL_NT r
                               WHERE (    (o.OBJUID = r.UID2)
                                      AND (R.UID1 = o1.OBJUID)
                                      AND (r.DEFUID = 'SPFSectionFormPurpose')))
                       WHERE X = 2)
                        AS "Rel2"
    But the table from the parent query (o1) cannot be read by the subquery 2 levels in, so I cant pass the value in. Oracle does not evaluate ROWNUM until the entire query has finished running so I cant have a simple subquery with ROWNUM = 2 as with the "Rel1" column.

    Any help would be appreciated,

    Thanks

  2. #2
    Join Date
    Mar 2010
    Posts
    3
    Fixed...

    Code:
                     (SELECT MAX (DECODE (ROWNUM, 1, o.OBJNAME)) OBJNAME
                        FROM VW_OBJ_NT o, VW_REL_NT r
                       WHERE (    (o.OBJUID = r.UID2)
                              AND (r.UID1 = o1.OBJUID)
                              AND (r.DEFUID = 'SPFSectionFormPurpose')))
                        AS "REL1",
                     (SELECT MAX (DECODE (ROWNUM, 2, o.OBJNAME)) OBJNAME
                        FROM VW_OBJ_NT o, VW_REL_NT r
                       WHERE (    (o.OBJUID = r.UID2)
                              AND (r.UID1 = o1.OBJUID)
                              AND (r.DEFUID = 'SPFSectionFormPurpose')))
                        AS "REL2"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •