Syntax help with functions

btull89 · #1 (**permalink**) 06-10-11, 12:06

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.

mirtheil · #2 (**permalink**) 06-10-11, 15:30

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.

btull89 · #3 (**permalink**) 06-10-11, 16:34

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.

mirtheil · #4 (**permalink**) 06-10-11, 22:06

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech