Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Sales_history_detail.number

    Hello, I'm trying to build a query accessing Sage Business Vision database.
    In the help file it says that NUMBER (the number of the invoice) is a 10 character String.
    When I do some query in Pervasive SQL Data Manager, SALES_HISTORY_DETAIL.NUMBER returns data like this:
    0000162330
    However for a query like this:
    Code:
    SELECT "SALES_HISTORY_DETAIL"."NUMBER", "SALES_HISTORY_DETAIL"."CODE"
    FROM "SALES_HISTORY_DETAIL"
    WHERE "SALES_HISTORY_DETAIL"."NUMBER" = 0000162330
    I don't get any results unless I change the 'where' clause to this:
    WHERE "SALES_HISTORY_DETAIL"."NUMBER" LIKE '%162330'
    All this variations fail
    WHERE "SALES_HISTORY_DETAIL"."NUMBER" = '0000162330'
    WHERE "SALES_HISTORY_DETAIL"."NUMBER" = '162330'
    WHERE "SALES_HISTORY_DETAIL"."NUMBER" = 162330
    Why can't I just use the '=' operator?

    Thanks,
    Eduardo

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    If NUMBER is a string, then you have to treat it as a string. The proper way to do it would be:
    Code:
    WHERE "SALES_HISTORY_DETAIL"."NUMBER" = '0000162330'
    You said that this fails. What do you mean? Is it returning an error or just not returning results? If it's an error, what error. If it's no results, then it's possible that what's actually in the file is different. I would suggest contacting Sage Business Vision to find out how they are putting the value into the field so you can send in the proper formatted value.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    May 2009
    Posts
    4
    Thank you, Mirtheil.
    When I said fail, I meant that it returned nothing, but didn't give me any error.
    Only with the "Like" operator am I successful to get the right data returned.
    I will look for help in sage's forum

    Eduardo
    www.expresssignproducts.com

Posting Permissions

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