Hi Tom,
I take it what you are saying there is that it needs to be dealt with
inside Oracle and outputted to CF as a list or something? Or can I do
it in my cfquery?
Not up on the (PL?)SQL that you have quoted and no access to a DBA at
the mo... my query is aas below and the PARENTANSWER is returning the
varray that is causing the PCODE error
<cfquery name="qCM_GetQuestionAnswers"
datasource="#APPLICATION.sIF_DSN#">
SELECT MCQ_ID, MCI_ID, MCI_FACULTY,
MCI_FACULTYTITLE, MCR_FACULTY, MCR_FACULTYTITLE, MCR_SCHOOL,
MCR_SCHOOLTITLE, MCI_SCHOOL,
MCI_SCHOOLTITLE, MCI_USERPOP_CRS, MCI_USERPOP_MOD, MCI_CRSMOD_NAME,
MCI_ACADEMICYEAR, MCI_CRSBLOCK,
MCI_MODPERIOD, MCL_MCRID, AREA_CAPTION, RESAREA_VALUE, ELEMENT_TYPE_ID,
ELEMENT_ORDER, MCI_NOSTUDENT,
FORM_ID, FORM_TITLE, MCF_ORDER, RESPONSES_ID, NAME, HOURS, MCR_CRSCODE,
ELEMENT_TYPE, PARENTANSWER
<!---- PARENTANSWER IS THE ONE CAUSING THEN HASSLE --->
FROM WEB_QUESTIONS_TOGETHER
WHERE FORM_ID = <cfqueryparam
value="#iCM_CurQues_FormID#" cfsqltype="CF_SQL_INTEGER">
AND ELEMENT_TYPE = <cfqueryparam
value="#iCM_CurQues_ID#" cfsqltype="CF_SQL_INTEGER">
AND ELEMENT_TYPE_ID = <cfqueryparam
value="#iCM_CurQues_Type#" cfsqltype="CF_SQL_INTEGER">
AND LENGTH(RESAREA_VALUE) > 0
#PreserveSingleQuotes(sCM_ReportsWhere)#
</cfquery>
I'm not sure how I can get parent answer to return a (say) list that I
can use in the query using the code below... and it's my ignorance not
your code :-)
Your help is appreciated
David
> On Thursday 29 May 2003 10:58 am, David Collie (itndac) wrote:
> > I am assuming that this is caused by the 'varray' return
> from Oracle
> > (think it is using nested tables) because when I remove
> this column,
> > all is fine
>
> You can use subscript notation to access varray elements,
> something like the
> below.
> I guess there's also a function to return the length of the
> array[*], so you
> should be able to write a procedure to call to do the dirty for you.
>
> DECLARE
> TYPE Staff IS TABLE OF Employee;
> staffer Employee;
> FUNCTION new_hires (hiredate DATE) RETURN Staff IS
> BEGIN ... END;
> BEGIN
> staffer := new_hires('10-NOV-98')(5);
> ...
> END;
>
> [*] If not, you'll need another proc to return the first NULL element.
> --
> Tom C
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
