Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    Question Unanswered: Need 2nd set of eyes on a CASE statement

    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",
    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,
    case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type
    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
    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

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

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    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');
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    try it without the Ns

    Edit: there's no concatenation; ChainType is a column alias | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    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! :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts