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.
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
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 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.
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
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 ...
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.
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.
Reply With Quote
Bookmarks