Announcement

Collapse
No announcement yet.

Create View with a Case Statement

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create View with a Case Statement

    I am trying to create a view that has a case statement.

    CREATE OR REPLACE FORCE VIEW RCPTINFO" ("ORDRNO", "LINENO", "InvNO", "RCPTDT" "RCQTY) AS
    SELECT rcord AS ordrNo,
    rclin AS lineNo,
    rcInv AS InvNo,
    rcdat AS rcptDt,
    CASE
    If rclen > 0
    then rclen AS RCQTY
    Else
    rcwgt AS RCQTY
    FROM SalesInfo
    I keep getting an error about From not being found or in right place. Any help would be greatly appreciated.

  • #2
    Re: Create View with a Case Statement

    What database software is this in please?
    You appear to be missing the END keyword when the case has finished, i,e. immediately above the FROM line
    Also missing a close quote after RCQTY in the first line

    Here is an example of a working CASE Statement:
    Code:
    SELECT Top 1000 TOa, CC, "TestCase" =
    CASE 
    	WHEN RFC822_size > 20000 THEN BACKUP_date
    	ELSE PP4_MAILBACKUP_ID
    END
    FROM PP4_MAILBACKUP
    Last edited by Ossian; 14th July 2012, 08:01.
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment

    Working...
    X