Neophyte: sub-select returning multpile rows

[marknel]

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.

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)

c1         field_enum_value.value
--------- ------------------------
John      Active
Anne      Sponser Emailed

Instead I'm getting:

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

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

[marist89]

You've got no where clause joining a and b together.

[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:

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

[marknel]

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

[marknel]

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:

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

[marknel]

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:

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

[marknel]

Well, some of this is sinking in finally today.

I now understand that what was meant about relating the a and b tables.

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

[marist89]

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:

SELECT a.c1, '|',
       TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + c6/86400,'MM-DD-YYYY') mod_date, 
.
.
.
FROM T215 a
WHERE ...

[marknel]

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:

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

[marknel]

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