Re: How can I limit the size of field in this SQL query?

john (uu_gg_at_hotmail.com) wrote:
: Hi,
: I have this query I use to exract data from Oracle8i DB, and one
: field's "ref_text" Datatype is "Long", when I do the query I want to
: limit this data to 2000 Char, how can I do that? I tried to use
: substr() and max(), length(), but no luck so far. Could you please
: show me how to do this, thanks.

In SQL*Plus you can do a SET LONG, which I think defines the maximum length returned from a long. I would read the manual to confirm what I say cause I might not remember this correctly, but also look at all the SET options as others might be relevant.

Of course this only helps if your are using SQL*Plus to pull out the data.

: John

: SELECT crse.local_crse_cd, crse.crse_status_cd, crse.crse_ttl,
: (I need something here to limit this field size to 2000 Char)-->
: ref_material_text.ref_text<--, crse.target_audience,
: crse.default_reg_cost,
: course_delivery_method.crse_delivery_method_cd, crse.crse_cd,
: crse.crse_language_ind,
: crse.crse_site_cd, competency.comp_external_cd
: FROM tedscbm.competency, tedscbm.crse_comp, tedscbm.crse,
: tedscbm.course_delivery_method, tedscbm.ref_material_text
: WHERE ((crse_comp.comp_cd = competency.comp_cd)
: AND (crse.crse_cd = crse_comp.crse_cd)
: AND (crse.crse_rev_cd = crse_comp.crse_rev_cd)
: AND (course_delivery_method.crse_cd = crse_comp.crse_cd)
: AND (course_delivery_method.CRSE_DELIVERY_METHOD_CD != 'D')
: AND (course_delivery_method.crse_rev_cd =
: crse_comp.crse_rev_cd)
: AND (crse.crse_overview_rev_cd =
: ref_material_text.ref_rev_cd)
: AND (crse.crse_overview_cd = ref_material_text.ref_cd)
: AND (crse.crse_status_cd = 'A'))
: ORDER BY crse.local_crse_cd ASC

--
Want to access the command line of your CGI account?  Need to debug your
installed CGI scripts?  Transfer and edit files right from your browser? 

What you need is "ispy.cgi" - visit http://nisoftware.com/ispy.cgi