Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Syntax help with functions

    This is the code I have for my string so far.
    Code:
    SET @str = 'Select C05_CMM_DESC_S AS Commodity, U09_LOC_DESC_S AS Location, C16_BEGIN_DLV_DT_T AS Beginning, 
    	   c16_SHPMNT_DU_DT_T AS Ending, C16_SCH_UNITS_D AS ALLOCATED, C16_SCH_UNITS_D - C16_APL_UNITS_D AS ToArrive from OpenQuery(linkedservername, ''Select * from C05_Commodity_Code INNER JOIN U09_LOC_CD ON 
    	U09_LOC_CD.U09_LOC_CD_S = C05_LOC_S AND U09_LOC_DESC_S IN (SELECT * dbo.fnSplitString_New(''''' + CONVERT(varchar(8000),@Location) + ''''','''','''')) INNER JOIN C16_PRC_DELIV_SCHD ON C16_CMM_S = C05_COMM_CD_S'')'
    There error is where I am using the function. Here is the error message from query analyzer:

    [OLE/DB provider returned message: [Pervasive][ODBC Client Interface][Pervasive][ODBC Engine Interface]Syntax Error: Select * from C05_Commodity_Code INNER JOIN U09_LOC_CD ON
    U09_LOC_CD.U09_LOC_CD_S = C05_LOC_S AND U09_LOC_DESC_S IN (SELECT * dbo<< ??? >>.fnSplitString_New('Jamestown',',')) INNER JOIN C16_PRC_DELIV_SCHD]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

    If I remove the function and just put 'Jamestown' in the parenthesis then it works.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    The "dbo" context doesn't exist in Pervasive. If you've got a function called fnSplitString_New in your PSQL database, you should be able to call it without the "dbo." portion.
    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
    Jun 2011
    Posts
    4
    Also, I am trying to do a case statement in the open query.
    I was told case statements are not possible in the pervasive.
    Something like this:
    Code:
    CASE DeliveryTypes WHEN ''0,1'' THEN ''Email and Text'' WHEN ''0'' THEN ''Email''
    	    WHEN ''1'' THEN ''Text'' WHEN ''2'' THEN ''Fax'' WHEN ''0,2'' THEN ''Email and Fax'' Else DeliveryTypes END
    Except in open query.
    I hear something about using an IIF statement instead, but I cannot find any information on the IIF statement with open query.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What version of PSQL are you using? "Open query" is just passing the statement to the PSQL engine. I would suggest using the PCC to troubleshoot the query and make sure it works before putting it into the "open query". V8 does not support CASE but V10 and v11 do. I'm not sure about V9.
    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.

Posting Permissions

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