remote views vs spt

I've been trying unsuccessfully to create a SQL Server remote view on a
query like this (simplified):

SELECT events.timeslot, events.leader1,
SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
attendance.amount, $0) AS cashrec
FROM events
LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
GROUP BY events.leader1, events.timeslot

This gets SQL error messages.


I've tried using T-SQL too, like this:

SUM(CASE WHEN attendance.paymethod = 'CASH' AND attendance.reversed = 0 THEN
attendance.amount ELSE $0.00 END) AS cashrec

... but this gets VFP error messages.


Is it possible to make a remote view like this or should I give up and use
SPT?

Rick
Tue Nov 30 13:18:02 CST 2004

Paul,
Because of newsgroup reformating and your simplified version, it's =
difficult to guess what the problem is, especially without knowing the =
actual wording of the error message. Any reason you can't just post the =
whole select AND the error message text?

Rick

"Paul Pedersen" <no-reply@swen.com> wrote in message =
news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
> I've been trying unsuccessfully to create a SQL Server remote view on =
a=20
> query like this (simplified):
>=20
> SELECT events.timeslot, events.leader1,
> SUM(IIF(attendance.paymethod =3D 'CASH' AND NOT attendance.reversed,=20
> attendance.amount, $0) AS cashrec
> FROM events
> LEFT OUTER JOIN attendance ON events.eventid =3D attendance.eventid
> WHERE events.startdt >=3D ?startDate AND events.startdt <=3D ?endDate
> GROUP BY events.leader1, events.timeslot
>=20
> This gets SQL error messages.
>=20
>=20
> I've tried using T-SQL too, like this:
>=20
> SUM(CASE WHEN attendance.paymethod =3D 'CASH' AND attendance.reversed =
=3D 0 THEN=20
> attendance.amount ELSE $0.00 END) AS cashrec
>=20
> ... but this gets VFP error messages.
>=20
>=20
> Is it possible to make a remote view like this or should I give up and =
use=20
> SPT?
>=20
>=20
>=20
>

Paul
Tue Nov 30 13:57:07 CST 2004

The actual view I'm trying to create is more complex than this, but this
contains most of the relevant elements:



SELECT events.timeslot, events.leader1, ;

SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
attendance.amount, $0)) AS cashrec ;

FROM events ;

LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid ;

WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate ;

GROUP BY events.leader1, events.timeslot


gives: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Line 1: Incorrect syntax near '='.


and
SELECT events.timeslot, events.leader1, ;

SUM(CASE WHEN attendance.paymethod = 'CASH' AND attendance.reversed = 0 THEN
attendance.amount ELSE $0.00 END) AS cashrec ;

FROM events ;

LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid ;

WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate ;

GROUP BY events.leader1, events.timeslot

gives: Function name is missing ).


If I run a query similar to the second one (removing semicolons etc.) in the
SQL Query Analyzer, it works fine.


I've heard that there are limits what can be done with remote views. Perhaps
I have reached one of them.


(I'm using VFP9 beta.)





"Rick Bean" <rgbean@unrealmelange-inc.com> wrote in message
news:uSboQFx1EHA.1188@tk2msftngp13.phx.gbl...
Paul,
Because of newsgroup reformating and your simplified version, it's difficult
to guess what the problem is, especially without knowing the actual wording
of the error message. Any reason you can't just post the whole select AND
the error message text?

Rick

"Paul Pedersen" <no-reply@swen.com> wrote in message
news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
> I've been trying unsuccessfully to create a SQL Server remote view on a
> query like this (simplified):
>
> SELECT events.timeslot, events.leader1,
> SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
> attendance.amount, $0) AS cashrec
> FROM events
> LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
> WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
> GROUP BY events.leader1, events.timeslot
>
> This gets SQL error messages.
>
>
> I've tried using T-SQL too, like this:
>
> SUM(CASE WHEN attendance.paymethod = 'CASH' AND attendance.reversed = 0
> THEN
> attendance.amount ELSE $0.00 END) AS cashrec
>
> ... but this gets VFP error messages.
>
>
> Is it possible to make a remote view like this or should I give up and use
> SPT?
>
>
>
>

Paul
Tue Nov 30 17:45:45 CST 2004

I never did get the remote view to work, so I went with SPT. Works fine.


"Paul Pedersen" <no-reply@swen.com> wrote in message
news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
> I've been trying unsuccessfully to create a SQL Server remote view on a
> query like this (simplified):
>
> SELECT events.timeslot, events.leader1,
> SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
> attendance.amount, $0) AS cashrec
> FROM events
> LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
> WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
> GROUP BY events.leader1, events.timeslot
>
> This gets SQL error messages.
>
>
> I've tried using T-SQL too, like this:
>
> SUM(CASE WHEN attendance.paymethod = 'CASH' AND attendance.reversed = 0
> THEN attendance.amount ELSE $0.00 END) AS cashrec
>
> ... but this gets VFP error messages.
>
>
> Is it possible to make a remote view like this or should I give up and use
> SPT?
>
>
>
>

Anders
Tue Nov 30 17:59:52 CST 2004

There's no IIF() function in SQL Server.

TEXT TO lcSQL NOSHOW
SELECT col1, col2,
SUM(
CASE attendence.paymethod
WHEN 'CASH'
THEN
CASE attendence.reversed
WHEN True
THEN attendance.amount
ELSE 0
END
ELSE NULL
END ) As sumamount
FROM xxx GROUP BY col1, col2
ENDTEXT

SQLEXEC(h, lcSQL, 'cursor1')

ELSE NULL isn't strictly necessary; it's the default. You may want ELSE 0


-Anders


"Paul Pedersen" <no-reply@swen.com> wrote in message
news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
> I've been trying unsuccessfully to create a SQL Server remote view on a
> query like this (simplified):
>
> SELECT events.timeslot, events.leader1,
> SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
> attendance.amount, $0) AS cashrec
> FROM events
> LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
> WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
> GROUP BY events.leader1, events.timeslot
>
> This gets SQL error messages.
>
>
> I've tried using T-SQL too, like this:
>
> SUM(CASE attendance.paymethod when 'CASH' AND attendance.reversed = 0 THEN
> attendance.amount ELSE $0.00 END) AS cashrec
>
> ... but this gets VFP error messages.
>
>
> Is it possible to make a remote view like this or should I give up and use
> SPT?
>
>
>
>

Anders
Tue Nov 30 18:09:14 CST 2004

You'll have to get the SQL Server syntax right first. Remote views are not
written in VFP SQL, Just like SPT queries a remote view can only use the
syntax of the remote SQL database. If the View Designer isn't capapbel
you'll have to enter the view definition with a command:
CREATE VIEW REMOTE CONNECTION xx AS <remote database specific SELECT
statement >
-Anders

"Paul Pedersen" <no-reply@swen.com> wrote in message
news:uSkw2az1EHA.1192@tk2msftngp13.phx.gbl...
> I never did get the remote view to work, so I went with SPT. Works fine.
>
>
> "Paul Pedersen" <no-reply@swen.com> wrote in message
> news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
> > I've been trying unsuccessfully to create a SQL Server remote view on a
> > query like this (simplified):
> >
> > SELECT events.timeslot, events.leader1,
> > SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
> > attendance.amount, $0) AS cashrec
> > FROM events
> > LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
> > WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
> > GROUP BY events.leader1, events.timeslot
> >
> > This gets SQL error messages.
> >
> >
> > I've tried using T-SQL too, like this:
> >
> > SUM(CASE WHEN attendance.paymethod = 'CASH' AND attendance.reversed = 0
> > THEN attendance.amount ELSE $0.00 END) AS cashrec
> >
> > ... but this gets VFP error messages.
> >
> >
> > Is it possible to make a remote view like this or should I give up and
use
> > SPT?
> >
> >
> >
> >
>
>

Paul
Tue Nov 30 20:35:57 CST 2004

Yes, it appears to be a problem with the view designer. I can create the
view in code and it seems to work fine, but if I then open it in the view
designer, I get "Syntax error".

I"m not sure how to set view properties though - I'll have to play with it.
This one's read only, so I can probably just leave the defaults.



"Anders Altberg" <x_pragma@telia.com> wrote in message
news:OkC$unz1EHA.2644@TK2MSFTNGP11.phx.gbl...
> You'll have to get the SQL Server syntax right first. Remote views are not
> written in VFP SQL, Just like SPT queries a remote view can only use the
> syntax of the remote SQL database. If the View Designer isn't capapbel
> you'll have to enter the view definition with a command:
> CREATE VIEW REMOTE CONNECTION xx AS <remote database specific SELECT
> statement >
> -Anders
>
> "Paul Pedersen" <no-reply@swen.com> wrote in message
> news:uSkw2az1EHA.1192@tk2msftngp13.phx.gbl...
>> I never did get the remote view to work, so I went with SPT. Works fine.
>>
>>
>> "Paul Pedersen" <no-reply@swen.com> wrote in message
>> news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
>> > I've been trying unsuccessfully to create a SQL Server remote view on a
>> > query like this (simplified):
>> >
>> > SELECT events.timeslot, events.leader1,
>> > SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
>> > attendance.amount, $0) AS cashrec
>> > FROM events
>> > LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
>> > WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
>> > GROUP BY events.leader1, events.timeslot
>> >
>> > This gets SQL error messages.
>> >
>> >
>> > I've tried using T-SQL too, like this:
>> >
>> > SUM(CASE WHEN attendance.paymethod = 'CASH' AND attendance.reversed = 0
>> > THEN attendance.amount ELSE $0.00 END) AS cashrec
>> >
>> > ... but this gets VFP error messages.
>> >
>> >
>> > Is it possible to make a remote view like this or should I give up and
> use
>> > SPT?
>> >
>> >
>> >
>> >
>>
>>
>

Paul
Tue Nov 30 20:39:17 CST 2004

Well, there ought to be! There ought to be a date data type, too.

They could learn a thing or two from FoxPro. ;-)


Thanks.



"Anders Altberg" <x_pragma@telia.com> wrote in message
news:uV6pkiz1EHA.3616@TK2MSFTNGP11.phx.gbl...
> There's no IIF() function in SQL Server.
>
> TEXT TO lcSQL NOSHOW
> SELECT col1, col2,
> SUM(
> CASE attendence.paymethod
> WHEN 'CASH'
> THEN
> CASE attendence.reversed
> WHEN True
> THEN attendance.amount
> ELSE 0
> END
> ELSE NULL
> END ) As sumamount
> FROM xxx GROUP BY col1, col2
> ENDTEXT
>
> SQLEXEC(h, lcSQL, 'cursor1')
>
> ELSE NULL isn't strictly necessary; it's the default. You may want ELSE 0
>
>
> -Anders
>
>
> "Paul Pedersen" <no-reply@swen.com> wrote in message
> news:edgiB6w1EHA.1292@TK2MSFTNGP10.phx.gbl...
>> I've been trying unsuccessfully to create a SQL Server remote view on a
>> query like this (simplified):
>>
>> SELECT events.timeslot, events.leader1,
>> SUM(IIF(attendance.paymethod = 'CASH' AND NOT attendance.reversed,
>> attendance.amount, $0) AS cashrec
>> FROM events
>> LEFT OUTER JOIN attendance ON events.eventid = attendance.eventid
>> WHERE events.startdt >= ?startDate AND events.startdt <= ?endDate
>> GROUP BY events.leader1, events.timeslot
>>
>> This gets SQL error messages.
>>
>>
>> I've tried using T-SQL too, like this:
>>
>> SUM(CASE attendance.paymethod when 'CASH' AND attendance.reversed = 0
>> THEN
>> attendance.amount ELSE $0.00 END) AS cashrec
>>
>> ... but this gets VFP error messages.
>>
>>
>> Is it possible to make a remote view like this or should I give up and
>> use
>> SPT?
>>
>>
>>
>>
>