Sage 100

 View Only
Back to discussions
Expand all | Collapse all

ProvideX SQL Syntax Regarding the WHEN Statement

  • 1.  ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-04-2023 17:06

    I'm used to using the IIF command in MS Access queries for conditional field definitions. I see the ProvideX SQL has a "WHEN expression THEN expression" and also an "Else expression" statement, but I can't get to first base with this simple query:

    SELECT CustomerNo, Balance, WHEN ARDivisionNo = '01' THEN 'One' ELSE 'Two' as Division from AR_OpenInvoice


    I get this message:


    Any ideas?

    Dan



    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    ------------------------------


  • 2.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-04-2023 20:31

    It looks like the interpreter is looking for a select from.  Provider has a " select from where ":

    https://manual.pvxplus.com/?odbc/using_odbc_driver/example_sql.htm



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------



  • 3.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-04-2023 23:35

    Thanks Doug, but I was looking for the "WHEN" keyword. Looking at an archived 4.X ProvideX manual I see that it isn't supported in our version (4.4) of the ODBC driver. I was looking at version 6 like what your link points to.



    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    ------------------------------

    Original Message


  • 4.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-04-2023 23:54

    What???  Sage is several version behind (4.4 vs. version 6)????  What is the significance of this?



    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    (540) 221-4444
    ------------------------------

    Original Message


  • 5.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 00:14

    LOL.... ProvideX version 4 = 2001, version 9 = 2010. 



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 6.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 08:49

    is ChatGPT even close here? 



    ------------------------------
    Wayne Schulz
    wayne@s-consult.com
    Schulz Consulting
    (860) 516-8990
    Moodus, CT
    ------------------------------



  • 7.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 10:28
    Edited by Dan Burleson 05-05-2023 11:06

    It appears to be "dead on" as in "we're screwed" ;-) at least for the later version of the PxPlus. I haven't downloaded version 6, but it appears to have corrected my syntax at that level. That solution will not work for version 4.X. I could find no examples of the "ELSE" keyword and I'm wondering how ChatGBT did. Anyone know where it found this example as it wasn't in the current manual at "https://manual.pvxplus.com". 



    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    ------------------------------

    Original Message


  • 8.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 10:48

    ChatGPT claims that it found the manual at https://www.sage.com/en-user/products/providex-sql/documentation/

    Which does not seem to be a valid link



    ------------------------------
    Wayne Schulz
    wayne@s-consult.com
    Schulz Consulting
    (860) 516-8990
    Moodus, CT
    ------------------------------

    Original Message


  • 9.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 15:09

    That link doesn't work for me either, but the one's Doug and I found have examples that are close, but none that show the ELSE keyword. I wonder if you added a search criteria to ChatCPT of Version 4.x or Sage 100, whether it would give the answer I was looking for. That the WHEN keyword was not yet supported.



    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    ------------------------------

    Original Message


  • 10.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 15:45
      |   view attached

    Instead of using ELSE you may have to use a separate query for each "CASE".. like this person did:
    https://stackoverflow.com/questions/23065695/providex-pvx-sql-case-statement

    Also found this version 4 ODBC installation and general info manual (attached).



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Attachment(s)

    pdf
    PVX-ODBC4x.pdf   872 KB 1 version
    Original Message


  • 11.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 15:14

    Why not do a straight table query, and add your condition in Access using a view / query of that result (which wouldn't be limited by Providex ODBC syntax)?



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------



  • 12.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-05-2023 16:06

    @Kevin Moyes The reason that I was looking to use the ProvideX SQL in a Crystal Stored Procedure (aka Command) was so that I could eliminate a dependency on an MS Access query. 

    @Doug Higgs Substituting the ELSE for specific WHEN conditions is mute because our version 4.4 doesn't support any of the CASE, ELSE, or WHEN keywords.



    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    ------------------------------

    Original Message


  • 13.  RE: ProvideX SQL Syntax Regarding the WHEN Statement

    Posted 05-09-2023 11:25

    It's going to sound like overkill, but can you set up a UDT, and join to it, to get your values?  I believe the older PVX SQL, or maybe the ODBC driver, subscribed to SQL ANSI-89 (as in 1989) at that point.  The Case type statements like CASE, COALESCE, NULLIF showed up in ANSI 92.  So sad.  :( 



    ------------------------------
    Chris St. Amand
    Arizona Accounting & Information Systems
    ------------------------------



Related Content