SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is wrong my script? Please take a look. Thanks

    What is wrong with my syntax? It keeps failing at th first comparison operator.

    >=
    Here is the query
    -----------------------------------------------------------------------------
    SELECT Sales.SalesID, Sales.SalesName, Sales.SalestartDate, Sales.SalesEndDate, Count(DistinctSales.Products)
    AS

    CountOfSales, Sales.Salesactive, (Sales.SalesEndDate>=getDate() And Sales.SalesActive And Sales.SalesStartDate<=getDate())
    AS

    BigSale, Sales.SalesEndDate<=getDate() AS InStoreSale
    FROM AllSales
    RIGHT JOIN Sales ON AllSales.SaleWeek=Sales.SaleID
    GROUP BY Sales.SaleID, Sales.SaleWeek, Sale.SaleStartDate, Sale.SaleEndDate, Sale.SaleActive
    ---------------------------------------------------------------------
    Here is the error
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '>'.
    ---------------------------------------------------------------------
    Maybe I have looked at it for too long, but i don't see anything wrong with it.
    Thanks for your help.
    assigned

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    (Sales.SalesEndDate>=getDate() And Sales.SalesActive And Sales.SalesStartDate<=getDate())
    SQL server does not support boolean expressions nor a boolean datatype.

    Code:
    case when 
    Sales.SalesEndDate>=getDate() 
    And Sales.SalesActive = 'Yes'  --- whatever you have to signify true
    And Sales.SalesStartDate<=getDate() then 'True' else 'False' end as BigSale
    A missing space here

    Code:
    Count(Distinct Sales.Products)

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, thats interesting, are saying that's not a perfectly correct SQL syntax?

    I am simply trying to run a report on the DB and I an not using Yes and No.
    All I want to do is get a repport of Active, ongong, etc sales.
    So you you mean as structured it will never work?
    Thanks
    assigned

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Your syntax is valid SQL but it is not supported by SQL server.

    Code:
    Sales.SalesEndDate>=getDate() 
    And Sales.SalesActive 
    And Sales.SalesStartDate<=getDate()
    What results are you expecting to get by using the above expression?
    (It would return a value of TRUE or FALSE, if supported by SQL server and the case expression is a way of mimicking that behaviour.)

    You need to show some sample data and the expected result.

    Also, the columns in your group by clause does not match the columns in the select clause.

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are correct about the typo in my GROUP by Clause.

    Ok what I am expecting to get is:
    <= some numbers
    => some numbers.
    Thanks,
    assigned


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
  •