SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Keyword Not Supported

    Can anyone help me with this query, I am trying to load a report accessing a MS SQL database.
    Please help me, thanks in advance.

    Code:
    Keyword not supported: 'select (customerlist.lastname & ', ' & customerlist.firstname & ' ' + customerlist.middlename) as customername, salesorder.invoiceno,salesorder.barcode, salesorder.itemqty, salesorder.itemprice,products.description, (salesorder.itemqty * salesorder.itemprice) as totalpurchase from customerlist inner join salesorder on salesorder.customerid'.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your sql is really hard to read so i reformatted it for you --
    Code:
    select (customerlist.lastname & ', ' & 
            customerlist.firstname & ' ' + 
            customerlist.middlename) as customername
         , salesorder.invoiceno
         , salesorder.barcode
         , salesorder.itemqty
         , salesorder.itemprice
         , products.description
         , (salesorder.itemqty * salesorder.itemprice) as totalpurchase 
      from customerlist 
    inner 
      join salesorder 
        on salesorder.customerid
    there are two problems that i noticed

    first, string concatenation in microsoft sql server uses the plus sign -- the ampersand is used in microsoft access

    second, your ON clause looks incomplete
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, here is the correct query.

    Code:
    select 
           (customerlist.lastname + ', ' + customerlist.firstname + ' ' +
           customerlist.middlename) AS customername, 
           salesorder.invoiceno,
           salesorder.barcode, 
           salesorder.itemqty, 
           salesorder.itemprice,
           products.description, 
           (salesorder.itemqty * salesorder.itemprice) AS totalpurchase 
    from customerlist 
    INNER JOIN salesorder
    on salesorder.customerid=customerlist.customerid 
    INNER JOIN products 
    ON products.barcode=salesorder.barcode
    And still, I am encountering the same problem.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your query now looks okay

    when i did a quick search on "keyword not supported" i found a lot of different resourses, and also this --

    http://support.microsoft.com/kb/2431362

    could you please confirm all the software you are using?

    as i said, your query looks okay, at least for sql server, so maybe you are using data warehouse?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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