Macro substitutuion(&) in view 
Author Message
 Macro substitutuion(&) in view

Is it possible to have a view similar to this:

select * from myTable;
where &myField == "ABC"

and the value of myField would be "myTable.field1" (or another field of
the table).
This command works fine, but i cant create a view like this.
Im missing something or it isn't possible?

Thanks in advance

RS



Sun, 09 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view

create view test as select * from mytable where &myField = "ABC" works fine
programmatically.
Only the VFP view designer thinks it's chinese...



Quote:
> Is it possible to have a view similar to this:

> select * from myTable;
> where &myField == "ABC"

> and the value of myField would be "myTable.field1" (or another field of
> the table).
> This command works fine, but i cant create a view like this.
> Im missing something or it isn't possible?

> Thanks in advance

> RS



Sun, 09 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view

Have not tried it but I guess the follwing sequence may work.  You have to
make myField visible to the view

private myfield
myfield = 'cust_id'
use myview
--------------


| Is it possible to have a view similar to this:
|
| select * from myTable;
| where &myField == "ABC"
|
| and the value of myField would be "myTable.field1" (or another field of
| the table).
| This command works fine, but i cant create a view like this.
| Im missing something or it isn't possible?
|
| Thanks in advance
|
| RS
|



Sun, 09 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view
Can you create the view this way in code?
(Don't use the view designer...)
Try this:

CREATE SQL VIEW lv_MyView ;
  AS SELECT * FROM mydatabase!myTable ;
  WHERE &?vpcWhere

** test.prg
** use field names and values you can verify the existence of
OPEN DATA mydatabase
vpcWhere = "char_field = 'char_value'"
USE lv_MyView
vpcWhere = "year(date_field)=2000"
REQUERY("lv_MyView")


Quote:
> Is it possible to have a view similar to this:

> select * from myTable;
> where &myField == "ABC"

> and the value of myField would be "myTable.field1" (or another field of
> the table).
> This command works fine, but i cant create a view like this.
> Im missing something or it isn't possible?

> Thanks in advance

> RS



Sun, 09 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view
NO.  The view will be retained as the expanded macro and not &myField.

So you can't pass in different combinations of &myField. and NO you can't
pass in different columns.  I tried this and it does not work.

__Stephen


Quote:
> Is it possible to have a view similar to this:

> select * from myTable;
> where &myField == "ABC"

> and the value of myField would be "myTable.field1" (or another field of
> the table).
> This command works fine, but i cant create a view like this.
> Im missing something or it isn't possible?

> Thanks in advance

> RS



Sun, 09 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view

RS,

was reading the other answers. Seems to be a problem.
Just an idea, what if you put the whole select into a string and executes
it as a macro.

Like this. (untested)

local cMySQL
local myTable
local myField

mytable= "customers"
myField= "City"

cMySQL= "select * from "+myTable+" where "+myField+" == [ABC]"
&cMySQL

Cheers
Carsten

RS schrieb:

Quote:
> Is it possible to have a view similar to this:

> select * from myTable;
> where &myField == "ABC"

> and the value of myField would be "myTable.field1" (or another field of
> the table).
> This command works fine, but i cant create a view like this.
> Im missing something or it isn't possible?

> Thanks in advance

> RS



Mon, 10 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view
Try it with this right here...

Local ji, ldDate
IF !FILE("testme.dbc")
 CREATE DATA testme
 CREATE TABLE testme (cfield c(10), dfield d)
 FOR ji = 1 TO 14
  ldDate = {^2000-09-24}
  INSERT INTO testme VALUES (CDOW(ldDate+ji), ldDate+ji)
 ENDFOR
 CREATE SQL VIEW lv_testme AS SELECT * FROM testme WHERE &?vpcWhere
ELSE
 OPEN DATA testme
ENDIF

vpcwhere = "cfield = 'Sunday'"
USE lv_testme
BROWSE

vpcwhere = "MONTH(dfield)=10"
REQUERY()
BROWSE

? DBGETPROP("lv_testme", "view", "sql")


Quote:
> NO.  The view will be retained as the expanded macro and not &myField.

> So you can't pass in different combinations of &myField. and NO you can't
> pass in different columns.  I tried this and it does not work.

> __Stephen




Quote:
> > Is it possible to have a view similar to this:

> > select * from myTable;
> > where &myField == "ABC"

> > and the value of myField would be "myTable.field1" (or another field of
> > the table).
> > This command works fine, but i cant create a view like this.
> > Im missing something or it isn't possible?

> > Thanks in advance

> > RS



Tue, 11 Mar 2003 13:17:50 GMT  
 Macro substitutuion(&) in view
Hey Trey, that works?  I tried to get this to work and somehow the view was
being saved as the expanded macro????

Thanks.

__Stephen


Quote:
> Try it with this right here...

> Local ji, ldDate
> IF !FILE("testme.dbc")
>  CREATE DATA testme
>  CREATE TABLE testme (cfield c(10), dfield d)
>  FOR ji = 1 TO 14
>   ldDate = {^2000-09-24}
>   INSERT INTO testme VALUES (CDOW(ldDate+ji), ldDate+ji)
>  ENDFOR
>  CREATE SQL VIEW lv_testme AS SELECT * FROM testme WHERE &?vpcWhere
> ELSE
>  OPEN DATA testme
> ENDIF

> vpcwhere = "cfield = 'Sunday'"
> USE lv_testme
> BROWSE

> vpcwhere = "MONTH(dfield)=10"
> REQUERY()
> BROWSE

> ? DBGETPROP("lv_testme", "view", "sql")



> > NO.  The view will be retained as the expanded macro and not &myField.

> > So you can't pass in different combinations of &myField. and NO you
can't
> > pass in different columns.  I tried this and it does not work.

> > __Stephen




- Show quoted text -

Quote:
> > > Is it possible to have a view similar to this:

> > > select * from myTable;
> > > where &myField == "ABC"

> > > and the value of myField would be "myTable.field1" (or another field
of
> > > the table).
> > > This command works fine, but i cant create a view like this.
> > > Im missing something or it isn't possible?

> > > Thanks in advance

> > > RS



Wed, 12 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view
Make sure the where clause is "WHERE &?vpcwhere" - with the ampersand first
then the question mark.
I just tried this and found that
1) just the ampersand will store the expanded macro
2) the question mark first will store the expanded macro, if the macro
variable exists when the view is created
3) the question mark first will throw an error (1493 - SQL parameter is
missing) when you try to use the view if the macro variable does not exist
when the view is created


Quote:
> Hey Trey, that works?  I tried to get this to work and somehow the view
was
> being saved as the expanded macro????

> Thanks.

> __Stephen



> > Try it with this right here...

> > Local ji, ldDate
> > IF !FILE("testme.dbc")
> >  CREATE DATA testme
> >  CREATE TABLE testme (cfield c(10), dfield d)
> >  FOR ji = 1 TO 14
> >   ldDate = {^2000-09-24}
> >   INSERT INTO testme VALUES (CDOW(ldDate+ji), ldDate+ji)
> >  ENDFOR
> >  CREATE SQL VIEW lv_testme AS SELECT * FROM testme WHERE &?vpcWhere
> > ELSE
> >  OPEN DATA testme
> > ENDIF

> > vpcwhere = "cfield = 'Sunday'"
> > USE lv_testme
> > BROWSE

> > vpcwhere = "MONTH(dfield)=10"
> > REQUERY()
> > BROWSE

> > ? DBGETPROP("lv_testme", "view", "sql")



> > > NO.  The view will be retained as the expanded macro and not &myField.

> > > So you can't pass in different combinations of &myField. and NO you
> can't
> > > pass in different columns.  I tried this and it does not work.

> > > __Stephen




- Show quoted text -

Quote:
> > > > Is it possible to have a view similar to this:

> > > > select * from myTable;
> > > > where &myField == "ABC"

> > > > and the value of myField would be "myTable.field1" (or another field
> of
> > > > the table).
> > > > This command works fine, but i cant create a view like this.
> > > > Im missing something or it isn't possible?

> > > > Thanks in advance

> > > > RS



Fri, 14 Mar 2003 03:00:00 GMT  
 Macro substitutuion(&) in view
This is what I did I guess. > 2) the question mark first will store the
expanded macro, if the macro

Quote:
> variable exists when the view is created

It was almost a year and a half ago now.

Thanks again.

__Stephen


Quote:
> Make sure the where clause is "WHERE &?vpcwhere" - with the ampersand
first
> then the question mark.
> I just tried this and found that
> 1) just the ampersand will store the expanded macro
> 2) the question mark first will store the expanded macro, if the macro
> variable exists when the view is created
> 3) the question mark first will throw an error (1493 - SQL parameter is
> missing) when you try to use the view if the macro variable does not exist
> when the view is created



> > Hey Trey, that works?  I tried to get this to work and somehow the view
> was
> > being saved as the expanded macro????

> > Thanks.

> > __Stephen



> > > Try it with this right here...

> > > Local ji, ldDate
> > > IF !FILE("testme.dbc")
> > >  CREATE DATA testme
> > >  CREATE TABLE testme (cfield c(10), dfield d)
> > >  FOR ji = 1 TO 14
> > >   ldDate = {^2000-09-24}
> > >   INSERT INTO testme VALUES (CDOW(ldDate+ji), ldDate+ji)
> > >  ENDFOR
> > >  CREATE SQL VIEW lv_testme AS SELECT * FROM testme WHERE &?vpcWhere
> > > ELSE
> > >  OPEN DATA testme
> > > ENDIF

> > > vpcwhere = "cfield = 'Sunday'"
> > > USE lv_testme
> > > BROWSE

> > > vpcwhere = "MONTH(dfield)=10"
> > > REQUERY()
> > > BROWSE

> > > ? DBGETPROP("lv_testme", "view", "sql")



> > > > NO.  The view will be retained as the expanded macro and not
&myField.

> > > > So you can't pass in different combinations of &myField. and NO you
> > can't
> > > > pass in different columns.  I tried this and it does not work.

> > > > __Stephen




- Show quoted text -

Quote:
> > > > > Is it possible to have a view similar to this:

> > > > > select * from myTable;
> > > > > where &myField == "ABC"

> > > > > and the value of myField would be "myTable.field1" (or another
field
> > of
> > > > > the table).
> > > > > This command works fine, but i cant create a view like this.
> > > > > Im missing something or it isn't possible?

> > > > > Thanks in advance

> > > > > RS



Sun, 16 Mar 2003 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. View with macro susbtitution

2. Macro substitution & Systax error

3. Date & Macro Problems

4. Macro substitution & Systax error

5. strange macro substitution error using '&'

6. Fox 2.6win, Reports & Macros

7. foxpro macro '&name'- anthing comparable in VB

8. When is a Macro not a Macro in VFP3.0a

9. view && grid

10. Sql Pass-through & Views

11. VFP : Parametrized Views & Reports

12. sql views & cdxs

 

 
Powered by phpBB® Forum Software