Remote View prompting for parameters on CREATE SQL VIEW

Remote View prompting for parameters on CREATE SQL VIEW

Post by Mark A. Nadi » Thu, 09 Dec 1999 04:00:00



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
/\/\/

/\/\/


 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Anders Altber » Thu, 09 Dec 1999 04:00:00


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
| /\/\/
|
| /\/\/
|


 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Fred Armon » Thu, 09 Dec 1999 04:00:00


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

--
Fred - Armoni (France)

(Almost cut my hair)

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Anders Altber » Fri, 10 Dec 1999 04:00:00


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)

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Mark A. Nadi » Fri, 10 Dec 1999 04:00:00


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

/\/\/


 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Anders Altber » Fri, 10 Dec 1999 04:00:00


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
|
| /\/\/
|

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Mark A. Nadi » Sat, 11 Dec 1999 04:00:00


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
/\/\/

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Anders Altber » Sat, 11 Dec 1999 04:00:00


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
| /\/\/

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Mark A. Nadi » Tue, 14 Dec 1999 04:00:00


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
/\/\/

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Anders Altber » Tue, 14 Dec 1999 04:00:00


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
| /\/\/

 
 
 

Remote View prompting for parameters on CREATE SQL VIEW

Post by Mark A. Nadi » Wed, 15 Dec 1999 04:00:00


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
/\/\/

 
 
 

1. Remote view prompting for parameters on CREATE SQL VIEW

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
/\/\/


2. Problems with Revoke all permisions

3. How can I create a view with parameter input prompt

4. using sequences in sql server

5. Creating a View using a remote view to Oracle

6. Please HelP!

7. SQL Server Remote View ALWAYS Prompting for Password

8. Application Server vs. Servlet

9. Creating a local view using CREATE SQL VIEW

10. Views with Prompts & Parameter markers

11. CREATE SQL VIEW REMOTE (gets all records, Why?)

12. Is it possible to combinbe remote views into one view

13. view designer says error when using mssql functions in remote views