I am going batty looking at this! I keep getting ORA-00907:Missing Right parenthesis when I have the 2nd case statement in this SQL (in bold). If I remove that line, it works just fine. As I don't see any left ( w/o a right ), I have no idea what the real issue is. (this is against a Oracle DB)

select rownum as IndexNum,
lm.hostlocid "Loc. Code",
pm.hostpartid "Comcode",
pm.partname "Description",
pc.hostplannercodeid "Plnr Code",
pc.codedescription "PC Descr",
sa.ohneworig "On Hand New Orig",
sa.ohfixedorig "On Hand Fixed Orig",
sa.ohbadorig "On Hand Bad Orig",
sa.onorderorig "On Order Orig",
sa.backorderorig "Backorder Orig",
sa.inrepairorig "In Repair Orig",
sa.inreturnorig "In Return Orig",
sa.allocatedorig "Allocated Orig",
sa.amountcustom1 "Amt Cus 1",
sa.amountcustom2 "Amt Cus 2",
sa.amountcustom3 "Amt Cus 3",
sa.amountcustom4 "Amt Cus 4",
sa.amountcustom5 "Amt Cus 5",
upper(ip.isaslitem) "Is ASL Item",
upper(ip.aslstatus) "ASL Status",
ip.stockmax "Stock Max",
ip.rop "Reorder Point",
pm.price "Std Cost",
fd.ForecastAmount,
to_char(fd.ForecastSliceDate,'MM-YYYY') "Current Period",
case when (sa.ohfixedorig + sa.ohneworig)=0 or fd.ForecastAmount=0 then null else round(((sa.ohfixedorig + sa.ohneworig)/(fd.ForecastAmount/30))) end Out_Of_Stock_Days,
pcd.ChainParentID,
pcd.RelationType,
case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type
from
stock_amount sa,
part_master pm,
loc_master lm,
loc_type lt,
stock_level ip,
planner_codes pc,
Forecasted_data fd,
part_chain_details pcd
where
sa.partid=pm.partid and
sa.locid=lm.locid and
lm.loctypeid=lt.loctypeid and
ip.partid=sa.partid and
pm.plannercodeid=pc.PLANNERCODEID and
ip.locid=sa.locid and
fd.partid = sa.partid and
fd.locid = sa.locid and
lm.hostlocid = 'B014' and
pcd.partid (+) = pm.partid and
(pc.hostplannercodeid = 'C50' or pc.hostplannercodeid = 'C51' or pc.hostplannercodeid = 'C52' or pc.hostplannercodeid = 'ZZ5') and
fd.ForecastSliceDate >ADD_MONTHS(SYSDATE,-1) and
fd.ForecastSliceDate<ADD_MONTHS(SYSDATE,0)


Any help showing the evil of my ways would be greatly appreciated!

case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type

Your problem, is that both (N'Alt') and ('N'Rep') are invalid. The syntax to append text to a column is, (column||' text');

try it without the Ns


Edit: there's no concatenation; ChainType is a column alias

Thanks R123456 and R937.

For some reason I had in my head that when using a case statement to output a text value I had to preced the 'text' with (N'my text').

No I was not trying to append to a column! Big OOPS!

I decided to try a DECODE statement that works. (I also was not taking into account nulls!)

With decode( pcd.relationtype,1,'Alt',0,'Rep','') as Chain_Type, the output is what I expect.

Hopefully I can be back to the dynamic SQL issue I posted earler this week!

Thanks for the slap in the face to wake me up! :-)