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

From: Boris Kesarev <boris.kesarev_at_infopulse.kiev.ua>
Date: Fri, 18 Apr 2003 10:52:59 +0300
Message-ID: <b7o78r$qkj$1@alice.infopulse.kiev.ua>

Hi Phil, you need to use it in this way:

 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
CAST(t.body AS VARCHAR2(4000)) like '%Constants %' OR CAST(t.body AS VARCHAR2(4000)) like '%TypeConstants %'

"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
Received on Fri Apr 18 2003 - 02:52:59 CDT