> Why do you want to declare a variable? You are not even using it in > your > statement. What is the problem for which you think a variable > declaration is the solution?
Fair question. I'm not even using it in my statement because I can't even declare a variable. If I can't get past step 1, there's no use in trying step 2. I'm sure you've been there. And I don't know that it solves my problem. I've been working on this query forever and had some great assistance from here a few weeks back. I'm trying to evolve that code. Originally, in the inner select, I had: CASE WHEN o.mname is not null THEN o.fname ELSE a.a_mname END as middlename. Then in the outter query (main query?), I substring it and concatenate it with a dot ( || '.'). This works great on first and last name which are required and are sure to have something in either o.fname or a.a_fname. Middle name though is optional and may not have an entry. That code above says if there's a middle name in my people table (o), use it. If not, use whatever is in the authors table (a). But the author's table may be blank too. In that case, I don't want to concatenate a dot. I'd end up with entries like "Abrahms,P.." Originally I tried to add a if in the second part of the statement, after the THEN section. I couldn't figure that out, after lotsa cussin and itchin. So, I thought that maybe I could initialize middlename as '' and maybe that would be of some assistance. In the query that you saw, I tried to use NVL(o.mname, a.a_mname) AS middlename, and also COALESCE(o.mname || '.', a.a_mname || '.', ' ') Both didn't work right, though I don't understand why coalesce didn't work. It seems like the third option would kick in. I'm at the point now that I think I should just figure out a CF solution and forget the whole SQL complexity. I dunno. Either way, I figured it would be good to at least know how to initialize variables in SQL for later use. Sounds like a basic, fundamental tool, right? > Depends on what you're trying to accomplish. Note that using > cfqueryparam, you're essentially creating a bind variable anyway - so > with CF, I don't see what you'd gain. I thought cfqueryparam did that, from what I remember about conversations here. But while I was just looking to have a basic variable, from the text, bind variables seemed like a good thing to know. Alot of the text said that Oracle automatically uses bind variables in most situations anyway, without the coder doing so. -- Daniel Kessler College of Health and Human Performance University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 Phone: 301-405-2545 http://hhp.umd.edu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277395 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
