Here is another example of a very complex view using INNER JOIN.
The R:BASE XE Cloud application runs on Secure Microsoft Azure Cloud.
https://rbase360.com
----------------------------------------------------------------------------------------------------------------
SET ERROR MESSAGE 677 OFF
DROP VIEW WebUpcomingRides
SET ERROR MESSAGE 677 ON
CREATE VIEW `WebUpcomingRides` +
(WPProvNo,WPMostRecentEdit,WPBrokerServiceType,WPRideCallRecordID,+
WPRideAuthno,WPRideNo,WPLastName,WPFrstName,WPMiddleI,WPClientNameLastFirst,+
WPClientNameFirstLast,WPIDNo,WPAppointmentDate,WPAppointmentTime,+
WPReturnPickupTime,WPWillCall,WPProvPickupTime,WPSingleRideTime,+
WPMobilityStatus,WPWheelchairSize,WPAttendants,WPAddPass,WPPassengerCount,+
WPOrigFacility,WPOrigAddress,WPOrigCity,WPOrigState,WPOrigPostal,WPOrigPhone,+
WPDestFacility,WPDestAddress,WPDestCity,WPDestState,WPDestPostal,WPDestPhone,+
WPProviderNotes,WPSharedRide,WPSharedRideID,WPAuthorizedRate,+
WPDrivingDistance,WPDrivingTime,WPOrigLatitude,WPOrigLongitude,+
WPDestLatitude,WPDestLongitude,WPOutcome,WPActive) AS +
SELECT RDProvno,WPEdDateTime,RDBrokerServiceType,RDRideCallRecordID,+
RDRideAuthno,Rideno,RDLastName,RDFrstName,RDMiddleI,(RDLastName + ',' & +
RDFrstName & RDMiddleI),(IFEQ(RDMiddleI,NULL,(RDFrstName & RDLastName),+
(RDFrstName & RDMiddleI & RDLastName))),IDNo,RDAppointmentDate,+
RDAppointmentTime,RDReturnPickupTime,RDWillCall,RDProvPickupTime,+
(IFEQ(RDAppointmentTime,NULL,(IFEQ(RDReturnPickupTime,NULL,'WillCall',+
((FORMAT(RDReturnPickupTime,'HHMM AP')) & 'READY'))),+
((FORMAT(RDAppointmentTime,'HHMM AP')) & 'APPT'))),(SSUB('Ambulant,WCmanual,+
WCpower,WCwide,Walker,Crutches,Cane,Oxygen,Carseat,Scooter,Stretcher',+
RDMobilityStatus)),(SSUB('<29",29",29.5",30",30.5",31",31.5",32",32.5",33",+
33.5",34",34.5",35",35.5",36",>36"',((ICHAR(RDWCType))-64))),RDAttendents,+
RDAddPass,(IFEQ(RDAttendents,0,NULL,RDAttendents)),(RDOrigName & +
RDOrigName2),(RDOrigAdd & RDOrigAdd2),RDOrigCity,RDOrigState,RDOrigPostal,+
RDOrigPhone,(RDDestName & RDDestName2),(RDDestAdd & RDDestAdd2),RDDestCity,+
RDDestState,RDDestPostal,RDDestPhone,RDProviderNotes,(CTXT(RDSharedRide)),+
RDSharedRideID,RDRateCode,RDDrivingDistance,RDDrivingTime,RDOrigLatitude,+
RDOrigLongitude,RDDestLatitude,RDDestLongitude,RDOutcome,RDActive FROM Rides +
T1 INNER JOIN WebEdTrackMostRecentDate T2 ON T1.RideNo = T2.WPEdRideNo WHERE +
RDActive = 'ACT' AND RDOutcome IS NULL
COMMENT ON VIEW `WebUpcomingRides` IS 'Web Portal View of Upcoming Rides'
RETURN
----------------------------------------------------------------------------------------------------------------
FWIW ...
Complex views can and do make temporary views with names like #1, #2,
etc. when optimizing the underlying query.
When the query is closed, those temporary views are all supposed to
be dropped. After exploring all details, we
found one place where they are dropped, but the "message" flag was
not temporarily turned off to hide the deleted
rows messages. Upcoming update of R:BASE X and R:BASE X Enterprise
will take care of such display.
Have you ever seen a complex view? Take a close look at the view
definition below.
Just imagine how R:BASE X Enterprise is handling complex views for
our corporate customers around the world.
----------------------------------------------------------------------------------------------------------------
SET ERROR MESSAGE 677 OFF
DROP VIEW WebUpcomingBidRides
SET ERROR MESSAGE 677 ON
CREATE VIEW `WebUpcomingBidRides` +
(WPProvNo,WPBrokerServiceType,WPRideCallRecordID,WPRideAuthno,WPRideNo,+
WPLastName,WPFrstName,WPMiddleI,WPIDNo,WPAppointmentDate,WPAppointmentTime,+
WPReturnPickupTime,WPWillCAll,WPProvPickupTime,WPSingleRideTime,+
WPMobilityStatus,WPWheelchairSize,WPAttendants,WPAddPass,WPPassengerCount,+
WPOrigFacility,WPOrigAddress,WPOrigCity,WPOrigState,WPOrigPostal,WPOrigPhone,+
WPDestFacility,WPDestAddress,WPDestCity,WPDestState,WPDestPostal,WPDestPhone,+
WPXOrigFacility,WPXOrigAddress,WPXOrigCity,WPXOrigState,WPXOrigPostal,+
WPXOrigPhone,WPXDestFacility,WPXDestAddress,WPXDestCity,WPXDestState,+
WPXDestPostal,WPXDestPhone,WPProviderNotes,WPSharedRideID,WPAuthorizedRate,+
WPDrivingDistance,WPDrivingTime,WPOrigLatitude,WPOrigLongitude,+
WPDestLatitude,WPDestLongitude,WPOutcome,WPActive) AS +
SELECT RDProvno,RDBrokerServiceType,RDRideCallRecordID,RDRideAuthno,Rideno,+
RDLastName,RDFrstName,RDMiddleI,IDNo,RDAppointmentDate,RDAppointmentTime,+
RDReturnPickupTime,RDWillCall,RDProvPickupTime,(IFEQ(RDAppointmentTime,NULL,+
(IFEQ(RDReturnPickupTime,NULL,'WillCall',((FORMAT(RDReturnPickupTime,'HHMM +
AP')) & 'READY'))),((FORMAT(RDAppointmentTime,'HHMM AP')) & 'APPT'))),+
(SSUB('Ambulant,WCmanual,WCpower,WCwide,Walker,Crutches,Cane,Oxygen,Carseat,+
Scooter,Stretcher',RDMobilityStatus)),(SSUB('<29",29",29.5",30",30.5",31",+
31.5",32",32.5",33",33.5",34",34.5",35",35.5",36",>36"',+
((ICHAR(RDWCType))-64))),RDAttendents,RDAddPass,(IFEQ(RDAttendents,0,NULL,+
RDAttendents)),(RDOrigName & RDOrigName2),(RDOrigAdd & RDOrigAdd2),+
RDOrigCity,RDOrigState,RDOrigPostal,RDOrigPhone,(RDDestName & RDDestName2),+
(RDDestAdd & RDDestAdd2),RDDestCity,RDDestState,RDDestPostal,RDDestPhone,+
(IFEQ(RDWillCall,'Y',(RDDestName & RDDestName2),(IFEQ(RDAppointmentTime,NULL,+
(RDDestName & RDDestName2),(RDOrigName & RDOrigName2))))),(IFEQ(RDWillCall,+
'Y',(RDDestAdd & RDDestAdd2),(IFEQ(RDAppointmentTime,NULL,(RDDestAdd & +
RDDestAdd2),(RDOrigAdd & RDOrigAdd2))))),(IFEQ(RDWillCall,'Y',RDDestCity,+
(IFEQ(RDAppointmentTime,NULL,RDDestCity,RDOrigCity)))),(IFEQ(RDWillCall,'Y',+
RDDestState,(IFEQ(RDAppointmentTime,NULL,RDDestState,RDOrigState)))),+
(IFEQ(RDWillCall,'Y',RDDestPostal,(IFEQ(RDAppointmentTime,NULL,RDDestPostal,+
RDOrigPostal)))),(IFEQ(RDWillCall,'Y',RDDestPhone,(IFEQ(RDAppointmentTime,+
NULL,RDDestPhone,RDOrigPhone)))),(IFEQ(RDWillCall,'Y',(RDOrigName & +
RDOrigName2),(IFEQ(RDAppointmentTime,NULL,(RDOrigName & RDOrigName2),+
(RDDestName & RDDestName2))))),(IFEQ(RDWillCall,'Y',(RDOrigAdd & RDOrigAdd2),+
(IFEQ(RDAppointmentTime,NULL,(RDOrigAdd & RDOrigAdd2),(RDDestAdd & +
RDDestAdd2))))),(IFEQ(RDWillCall,'Y',RDOrigCity,(IFEQ(RDAppointmentTime,NULL,+
RDOrigCity,RDDestCity)))),(IFEQ(RDWillCall,'Y',RDOrigState,+
(IFEQ(RDAppointmentTime,NULL,RDOrigState,RDDestState)))),(IFEQ(RDWillCall,+
'Y',RDOrigPostal,(IFEQ(RDAppointmentTime,NULL,RDOrigPostal,RDDestPostal)))),+
(IFEQ(RDWillCall,'Y',RDOrigPhone,(IFEQ(RDAppointmentTime,NULL,RDOrigPhone,+
RDDestPhone)))),RDProviderNotes,RDSharedRideID,RDRateCode,RDDrivingDistance,+
RDDrivingTime,RDOrigLatitude,RDOrigLongitude,RDDestLatitude,RDDestLongitude,+
RDOutcome,RDActive FROM Rides WHERE RDActive = 'ACT' AND RDOutcome IS NULL +
AND RDProvno in (7777,8888)
COMMENT ON VIEW `WebUpcomingBidRides` IS 'Upcoming Bid Ride Detail'
RETURN
----------------------------------------------------------------------------------------------------------------
Very Best R:egards,
Razzak
At 11:16 AM 1/16/2017, 'Lawrence Lustig' via RBASE-L wrote:
I have a fairly complex form. The form creates some temporary views
and uses them to supply a variety of list views.
When I close the form, I see the message 1 row(s) have been deleted
from SYSTMP_VIEWS repeated 24 times. It takes about ten seconds for
all these messages to display during which time R:Base is not
accessible. These messages are not coming from the views I create
in code (I don't drop those views when I close the form) but rather
some strange views that seem to be internally created by R:Base. I
note that the time of about ten seconds is consistent if I do EDIT
USING MyForm from the R> prompt. But if I do EDIT USING MyForm from
inside a PRG file and run the PRG file, the messages display much
faster (maybe 4 seconds). But even 4 seconds is a delay I don't
want my users to suffer through.
I can find out what views are being dropped by R:Base by including
the command sele LISTOF(sys_table_name) from sys_tables where
sys_table_id in (select sys_table_id from systmp_views) in my ON
CLOSE EEP and then repeating the same command after my form has closed.
These views exist before the form is closed: #1, #11, #15, #16,
tvwPOHdrEx, tvwPOItems, tvwPOReqDtlDates, #17, tvwPOReqHdrDisp,
tvwPOAttachments, #22, tvwItemDisplay, #25, #26, #27, #28, #29, #30,
#31, #32, #33, #34, #35, #36, #37, #38, #39, #40, #41, #42, #43,
#44, #45, #46, #47, tvwPOReceiveHdrEx, #48, #49, #50
The views with names like "tvw. . ." are the ones I create, and I
expect those to exist. It's the views with the number signs that I
can't account for.
And these are all that's left after the form has finished: #1, #11,
#15, #16, tvwPOHdrEx, tvwPOItems, tvwPOReqDtlDates, #17,
tvwPOReqHdrDisp, tvwPOAttachments, #22, tvwItemDisplay, tvwPOReceiveHdrEx.
My questions are:
Where are these views coming from and how can I avoid creating and
dropping them? Or, at least, not paying the performance penalty I'm seeing.
If R:Base is making them, how come it's not cleaning up all of
them? Note that six of the numbered views remain after the form is closed.
Additional info: Database is running with STATICDB ON.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
