Link to home
Start Free TrialLog in
Avatar of team2005
team2005

asked on

Using right function sql view...

Hi!

Have this code: (the first lines of the code)

CREATE VIEW
    SALG_STATISTIKK2
    (
        Dimension,
        Description1,
        Dimension2,
        Description2,
        Dimension3,
        Description3,
        Itemid,
        ItemName,
        EDIEpdNo,
        ForsendelsesNr,
        GrossistGLN,
        FakturaNr,
        Fakturadato,
        GrossistKundeNr,
        KundeGLN,
        TSLopenr,
        Kundenavn,
        KundeAdresse,
        RIGHT('0000' + CONVERT(VARCHAR,Kundepostnr), 4),
        KundePoststed,

Open in new window


The problem is this line:
RIGHT('0000' + CONVERT(VARCHAR,Kundepostnr), 4),

Gives me this error message:
 [Error Code: 156, SQL State: S1000]  Incorrect syntax near the keyword 'RIGHT'.

What is wrong ?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of team2005
team2005

ASKER

you are 100% right, need some black coffe :)
Since it is derived column so you might need to alias it. Do like

CREATE VIEW
    SALG_STATISTIKK2
    (
        Dimension,
        Description1,
        Dimension2,
        Description2,
        Dimension3,
        Description3,
        Itemid,
        ItemName,
        EDIEpdNo,
        ForsendelsesNr,
        GrossistGLN,
        FakturaNr,
        Fakturadato,
        GrossistKundeNr,
        KundeGLN,
        TSLopenr,
        Kundenavn,
        KundeAdresse,
        xxxxx,
        KundePoststed,
         ....
)
AS
SELECT ....
          RIGHT('0000' + CONVERT(VARCHAR,Kundepostnr), 4) KundePostNRString,
    . ...
 FROM
  WHERE ...
sachitjain,
  I would say that it's not needed to alias the expression in the SELECT at that place.
   the XXXXX in the create view above handles that part.

of course, you could create the view also like this, in which case you indeed need to alias the expression:
CREATE VIEW  SALG_STATISTIKK2
AS
SELECT ....
          RIGHT('0000' + CONVERT(VARCHAR,Kundepostnr), 4) KundePostNRString,
    . ...
 FROM
  WHERE ...  

Open in new window

CHeers