Re: I need to do LIKE comparison of LONG datatype field

"Phil Powell" <soazine_at_erols.com> wrote in message news:1cdca2a7.0304161138.5f55fc1b_at_posting.google.com...
> SELECT t.ID, t.TITLE, tp.PATHNAME, CAST(t.body AS VARCHAR2(4000)) AS
> templateBody
> FROM vigaudi.template t, vigaudi.template_path tp, vigaudi.vgn_pr pr,
> vigaudi.vgn_ci ci
> WHERE t.ID = tp.ID
> AND TO_CHAR(t.ID) = ci.RECORDID
> AND ci.PROJECT = pr.PARENTPROJECT
> AND ci.PROJECT IN ('/pr/64', '/pr/79', '/pr/7b', '/pr/7c', '/pr/7e',
> '/pr/80', '/pr/81', '/pr/82', '/pr/83', '/pr/84', '/pr/85', '/pr/8c',
> '/pr/90', '/pr/93', '/pr/97', '/pr/99', '/pr/9a', '/pr/9b', '/pr/9e',
> '/pr/9f', '/pr/a0', '/pr/a3', '/pr/a4', '/pr/a5', '/pr/63')
> AND templateBody like '%Constants %' OR templateBody like
> '%TypeConstants %'
>
> The following SQL statement produces the following error:
>
> Invalid column name 'templateBody'
>
>
> How can I rewrite this SQL statement to work, every criteria clause is
> required for the proper return.
>
> Thanx
> Phil

You can't use comparison functions on LONG columns. Change to CLOBs instead. If you *really* want to use lower case columns, you'll need to enclose *every* lower case columns in double quotes.

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address