Daniel, Yes, you can't declare your variable within a sub-select statement. Declare it before you start the first query and you should be fine.
Dean On 5/8/07, daniel kessler <[EMAIL PROTECTED]> wrote: > > I get "missing right parenthesis" as an error. Maybe that's because it's an > inside query that is queried by an outside query? I didn't paste the whole > thing cause I was trying to minimize the possible confusion. The whole thing > is: > > SELECT publication_name,publicationa_id,publication_id,dept,middlename, > lastname || ',' || substr(firstname,1,1) || '.' as the_name, > firstname || lastname as the_full_name, > CASE WHEN author_link is not null then '<a href="' || author_link > || '">' || lastname || ',' || substr(firstname,1,1) || '.' || '</a>' > ELSE lastname || ',' || substr(firstname,1,1) || '.' || middlename > END as author_formatted > FROM ( > DECLARE @find varchar(30); > SET @find = ''; > SELECT p.publication_name, p.publication_id, > o.dept,o.link AS > author_link, > NVL(o.lname, a.a_lname) AS > lastname, > NVL(o.mname, a.a_mname) || '.' AS > middlename, > NVL(o.fname, a.a_fname) AS > firstname, > a.publicationa_id > FROM publications p > INNER JOIN publications_authors a > ON p.publication_id = a.publication_id > LEFT JOIN people o > ON a.id = o.id > ORDER BY publicationa_id asc > ) > > Without the declare/set, I get no error. > -- __________________________________________ Dean Lawrence, CIO/Partner Internet Data Technology 888.GET.IDT1 ext. 701 * fax: 888.438.4381 http://www.idatatech.com/ Corporate Internet Development and Marketing Specialists ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277285 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
