Neophyte: sub-select returning multpile rows
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Neophyte: sub-select returning multpile rows

  1. #1
    Join Date
    Apr 2003
    Posts
    20

    Neophyte: sub-select returning multpile rows

    Keeping in mind that I'm still quite the Oracle neophyte, I'm having the following problem with a

    select, sub-select query.
    First let me layout the 2 tables involved.
    Code:
    Table 1 :  t215
    Column1 :  C1
    Column2 :  C7
    
          C1           C7   
    ---------------- ------
    John                  0
    Anne                  3
    
    
    Table 2 : FIELD_ENUM_VALUES
    Column1 : SCHEMAID
    Column2 : FIELDID
    Column3 : ENUMID
    Column4 : VALUE
    
     SCHEMAID    FIELDID      ENUMID           VALUE
    ---------- ------------ ---------- --------------------
           215            7          0 Active
           215            7          1 Expired
           215            7          2 Sponser Emailed
    What I need to do is return the data from field_enum_values.value for each single row in t215

    For example:
    (select statement here)
    Code:
    c1         field_enum_value.value
    --------- ------------------------
    John      Active
    Anne      Sponser Emailed
    Instead I'm getting:
    Code:
    c1         field_enum_value.value
    --------- ------------------------
    John      Active
    John      Sponser Emailed
    Anne      Active
    Anne      Sponser Emailed
    I thought the following would work, but I have since found out that it doesn't
    Code:
    SELECT  a.c1,
    	b.curr_status
    	   
    FROM T215 a,
    	 (SELECT enumval.value curr_status FROM FIELD_ENUM_VALUES enumval, T215 status 
    	 	  	  WHERE enumval.schemaid = 215
    			  		 AND enumval.fieldid = 7
    					 	 AND enumval.enumid = status.c7) b
    I think I understand that the subselect will return all the rows (which it is doing),
    but I'm not able to currently comprehend how to make it only return one value for each row in t215.

    I hope that this issue is clear.

    Thank you in advance.

    Mark Nelson

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You've got no where clause joining a and b together.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The reason why you are getting a Cartesian product is because there is no condition relating "T215 a" to the sub query "(SELECT . . . ) b" - you would have to add another WHERE clause at the end.

    But more simply, why not soemthing like:
    Code:
    SELECT  a.c1, enumval.value
    FROM  T215 a, FIELD_ENUM_VALUES enumval
    WHERE enumval.schemaid = 215
    AND   enumval.fieldid = 7
    AND   enumval.enumid = a.c7

  4. #4
    Join Date
    Apr 2003
    Posts
    20
    Originally posted by marist89
    You've got no where clause joining a and b together.
    Thank you for your response.

    Perhaps I'm not understanding, but the only common column between a and b is t215.C7 to field_enum_values.values

    Thank you for your patience with me on this.

    Mark

  5. #5
    Join Date
    Apr 2003
    Posts
    20
    Originally posted by DaPi
    The reason why you are getting a Cartesian product is because there is no condition relating "T215 a" to the sub query "(SELECT . . . ) b" - you would have to add another WHERE clause at the end.

    But more simply, why not soemthing like:
    Code:
    SELECT  a.c1, enumval.value
    FROM  T215 a, FIELD_ENUM_VALUES enumval
    WHERE enumval.schemaid = 215
    AND   enumval.fieldid = 7
    AND   enumval.enumid = a.c7
    Wow.. Okay that worked very nicely.
    Thank you. Now to change the rest of the monstrosity that I called a query to fit this.

    I might be back

  6. #6
    Join Date
    Apr 2003
    Posts
    20
    I am back...
    The reason I think the previous suggestion won't work for the full query is because I actually have to pull multiple values from the enumval.value field in the complete query. I didn't post the complete query here before for a few reasons, 1. It's alot to post and 2. well to be frank, I'm rather embarassed by my lack of knowledge in what has become my job function, 3. I was trying not to be to huge of a pain.
    But here it goes:
    Code:
    SELECT a.c1, '|',
    	   b.mod_date, '|',
    	   f.curr_status, '|',
    	   a.c8, '|',
    	   a.c536870913, '|',
    	   a.c536870914, '|',
    	   a.c536870916, '|',
    	   a.c536870919, '|',
    	   c.start_date, '|',
    	   d.expire_date, '|',
    	   a.c536870922, '|',
    	   a.c536870923, '|',
    	   a.c536870924, '|',
    	   a.c536870925, '|',
    	   a.c536871105, '|',
    	   a.c536871106, '|',
    	   a.c536871107, '|',
    	   a.c536871108, '|',
    	   a.c536871109, '|',
    	   a.c536871111, '|',
    	   a.c536871112, '|',
    	   a.c536871113, '|',
    	   g.competitor_co, '|',
    	   h.us_citizen, '|',
    	   i.perma_res, '|',
    	   a.c536871117, '|',
    	   e.resident_expire
    FROM T215 a,
    /* data is stored in the DB as unix Epoc */ 
    	 (SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c6/86400
    	   ,'MM-DD-YYYY') mod_date FROM T215) b, 
    	 (SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c536870920/86400 
    	   ,'MM-DD-YYYY') start_date FROM T215) c,
    	 (SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c536870921/86400 
    	   ,'MM-DD-YYYY') expire_date FROM T215) d,
    	 (SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c536871118/86400 
    	   ,'MM-DD-YYYY') resident_expire FROM T215) e,
    
    	 (SELECT enumval.value curr_status FROM FIELD_ENUM_VALUES enumval, T215 status 
    	 	  	  WHERE enumval.schemaid = 215
    			  		 AND enumval.fieldid = 7
    					 	 AND enumval.enumid = status.c7) f,
       	 (SELECT enumval.value competitor_co FROM FIELD_ENUM_VALUES enumval, T215 status 
    	 	  	  WHERE enumval.schemaid = 215
    			  		 AND enumval.fieldid = 7
    					 	 AND enumval.enumid = status.c536871114) g,
       	 (SELECT enumval.value us_citizen FROM FIELD_ENUM_VALUES enumval, T215 status 
    	 	  	  WHERE enumval.schemaid = 215
    			  		 AND enumval.fieldid = 7
    					 	 AND enumval.enumid = status.c536871115) h,
       	 (SELECT enumval.value perma_res FROM FIELD_ENUM_VALUES enumval, T215 status 
    	 	  	  WHERE enumval.schemaid = 215
    			  		 AND enumval.fieldid = 7
    					 	 AND enumval.enumid = status.c536871116) i
    I know this is a mess, but I'm trying to gain an understanding so I can A: Make it work, B: work as effiecently as possible and C: learn something.

    I'm right now breaking it down to just a few fields at a time to try to rework this from the ground up to see if I can discover my errors.
    Any input and/or suggestions are greatly appreciated.

    Mark
    Last edited by marknel; 11-03-2003 at 02:05 PM.

  7. #7
    Join Date
    Apr 2003
    Posts
    20
    Well, some of this is sinking in finally today.

    I now understand that what was meant about relating the a and b tables.
    Code:
    FROM T215 a,
    	 FIELD_ENUM_VALUES enumval, 
    	 (SELECT c1,TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c6/86400
    	   ,'MM-DD-YYYY') mod_date FROM T215) b
    WHERE enumval.schemaid = 215
    	   AND   enumval.fieldid = 7
    	   AND   enumval.enumid = a.c7
    	   AND		 a.c1 = b.c1
    I'm still working on this thing, and I still welcome any and all suggestions.
    Mark

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by marknel

    I know this is a mess, but I'm trying to gain an understanding so I can A: Make it work, B: work as effiecently as possible and C: learn something.
    You're right, this is a mess...

    You know you can:
    Code:
    SELECT a.c1, '|',
           TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c6/86400,'MM-DD-YYYY') mod_date, 
    .
    .
    .
    FROM T215 a
    WHERE ...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Apr 2003
    Posts
    20
    Thanks marist89. I thought I had tried that but I'll certianly try it again.

    I've got the query working overall now in Freetoad, but when I run it from command line in sqlplus (v8.1.7) I get errors:
    Code:
    SP2-0734: unknown command beginning "WHERE enum..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND   enum..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND   enum..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    SP2-0044: For a list of known commands enter HELP
    and to leave enter EXIT.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    SP2-0044: For a list of known commands enter HELP
    and to leave enter EXIT.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    SP2-0734: unknown command beginning "AND		 a.c1..." - rest of line ignored.
    Input truncated to 9 characters
    I'm searching this site and the net for what might be wrong now.

    Thank you for all your input!

    Mark

  10. #10
    Join Date
    Apr 2003
    Posts
    20
    retyped it all again in dos and it seems to be working.

    Thanks again Jeff for your input. I realize it must be painful seeing sql hacked out the way I did, but I learned a bit and hopefully someone will learn from my stumbles as well.



    Mark
    Last edited by marknel; 11-04-2003 at 10:47 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width