[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using right function sql view...

Posted on 2012-08-29
4
Medium Priority
?
622 Views
Last Modified: 2012-08-29
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 ?
0
Comment
Question by:team2005
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38344930
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
 
LVL 2

Author Closing Comment

by:team2005
ID: 38344982
you are 100% right, need some black coffe :)
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38344984
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38345029
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question