Very Computer

If I try to execute the following, I am prompted for the values of
nMainSeg, nMainSt and nMainLen:

CREATE SQL VIEW "V_TEST" REMOTE CONNECT "conDataset" SHARE AS ;
select cDivCode ;
 FROM gldiv,glcharm  ;
 WHERE Gldiv.esegno = ?m.nMainSeg ;
  AND GLCharM.ePType =  1 ;
  AND cDivCode = UPPER( SUBSTRING( GLCharM.cAcNo, ?m.nMainSt,
?m.nMainLen)) ;
  AND  GLCharM.eAcType IN (  1, 2, 3) ;
  GROUP  BY cDivCode

If I remove the GROUP BY, I am not prompted (as expected). Also, if i
try to trick it by STORE 1 TO m.nMainSeg, m.nMainSt, m.nMainLen before
executing this, then I get a connectivity error indicating "Argument
datatype float is invalid for argument 3 of substring function" -
somehow nMainLen is getting converted to a float when VFP passing it
to SQL Server...

Any ideas on how to get around this issue?

Thanks in advance,

Mark
/\/\/

/\/\/

Mark

The SQL92 syntax of the SUBSTRING function is either
 SUBSTRING(string FROM n1)
or
 SUBSTRING(string FROM n1 FOR n2)

-Anders

| If I try to execute the following, I am prompted for the values of
| nMainSeg, nMainSt and nMainLen:
|
| CREATE SQL VIEW "V_TEST" REMOTE CONNECT "conDataset" SHARE AS ;
| select cDivCode ;
|  FROM gldiv,glcharm  ;
|  WHERE Gldiv.esegno = ?m.nMainSeg ;
|   AND GLCharM.ePType =  1 ;
|   AND cDivCode = UPPER( SUBSTRING( GLCharM.cAcNo, ?m.nMainSt,
| ?m.nMainLen)) ;
|   AND  GLCharM.eAcType IN (  1, 2, 3) ;
|   GROUP  BY cDivCode
|
| If I remove the GROUP BY, I am not prompted (as expected). Also, if i
| try to trick it by STORE 1 TO m.nMainSeg, m.nMainSt, m.nMainLen before
| executing this, then I get a connectivity error indicating "Argument
| datatype float is invalid for argument 3 of substring function" -
| somehow nMainLen is getting converted to a float when VFP passing it
| to SQL Server...
|
| Any ideas on how to get around this issue?
|
| Thanks in advance,
|
| Mark
| /\/\/
|
| /\/\/
|

Anders,
I thought that The SQL92 SUBSTRING function was the same
than the strtran fox function ?

--
Fred - Armoni (France)

(Almost cut my hair)

Fred
STRTRAN( ) is used to replace parts of a string with another string. The SQL
SUBSTRING( ) function is equivalent to VFP's SUBSTR( ) function. It's used
to
access and retrieve parts of string.
-Anders

| Anders,
| I thought that The SQL92 SUBSTRING function was the same
| than the strtran fox function ?
|
|
| --
| Fred - Armoni (France)
|
|
| (Almost cut my hair)

Hi Anders,
Thanks for the reply. However, here's a snip from the SQL 7 Books
Online:

Syntax:
SUBSTRING(expression, start, length)

Arguments:
expression: Is a character string, binary string, text, image, a
column, or an expression that includes a column. Do not use
expressions that include aggregate functions.

start: Is an integer that specifies where the substring begins.

length: Is an integer that specifies

On Wed, 8 Dec 1999 15:45:04 +0100, "Anders Altberg"

/\/\/

Yes, that's a vendor-specific syntax. I'd expect  SS7 to also support the
SQL-92 standard, SUBSTRING(expr FROM .. TO .. )
Have you tried either way in a simplified query?

-Anders

| Hi Anders,
| Thanks for the reply. However, here's a snip from the SQL 7 Books
| Online:
|
| Syntax:
| SUBSTRING(expression, start, length)
|
| Arguments:
| expression: Is a character string, binary string, text, image, a
| column, or an expression that includes a column. Do not use
| expressions that include aggregate functions.
|
| start: Is an integer that specifies where the substring begins.
|
| length: Is an integer that specifies
|
|
|
| On Wed, 8 Dec 1999 15:45:04 +0100, "Anders Altberg"
|
| >Mark
| >
| >The SQL92 syntax of the SUBSTRING function is either
| > SUBSTRING(string FROM n1)
| >or
| > SUBSTRING(string FROM n1 FOR n2)
| >
| >-Anders
|
| /\/\/
|

Hi Anders,

I tried this:

SELECT substring(cacno FROM 1 TO 4) AS foo FROM glcharm

and

SELECT substring cacno FROM 1 TO 4 AS foo FROM glcharm

I keep getting errors in SQL 7. Do I have the syntax correct in either
of these?

Thanks for you help,

Mark
/\/\/

Hi Mark
No, it's FOR not TO.
SELECT SUBSTRING(cacno FROM 1 FOR 4) AS foo
-Anders

| Hi Anders,
|
| I tried this:
|
| SELECT substring(cacno FROM 1 TO 4) AS foo FROM glcharm
|
| and
|
| SELECT substring cacno FROM 1 TO 4 AS foo FROM glcharm
|
| I keep getting errors in SQL 7. Do I have the syntax correct in either
| of these?
|
| Thanks for you help,
|
| Mark
| /\/\/

Hi Anders,

SELECT SUBSTRING(cacno FROM 1 FOR 4) AS foo
FROM glcharm

Still not working on SQL 7. This syntax must not be supported by SQL
Server 7. Thanks for your help, anyway.

Best,

Mark
/\/\/

Hi Mark
Hm, I have only got SS6.5 docs available with SUBSTRING(x,y,z). I just
figured SS7 might have gone full SQL-92 conformant.
What did you get? Null or an error? What datatype is cacno?
-Anders

| Hi Anders,
|
| SELECT SUBSTRING(cacno FROM 1 FOR 4) AS foo
| FROM glcharm
|
| Still not working on SQL 7. This syntax must not be supported by SQL
| Server 7. Thanks for your help, anyway.
|
| Best,
|
| Mark
| /\/\/

Hi Anders,

Quote:> I just figured SS7 might have gone full SQL-92 conformant.

Perhaps in 8 :)

Quote:> What did you get?

I got an error and cAcNo is char (18).

Best,

Mark
/\/\/