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 ?
LVL 2
team2005Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in the place you put the function, it's the column name listing, not the values/expression place.

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),
    . ...
 FROM 
  WHERE ...
                

Open in new window

0
 
team2005Author Commented:
you are 100% right, need some black coffe :)
0
 
sachitjainCommented:
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 ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.