You will then see a screen like this with nothing in it.

Click the queries bar on the left

If you try to Edit a user view classed as owned by dbo, you may in fact fail.

To see more information, open the SQL pane and change the owner of the table from for example qe2.tblADmDeliveries to dbo.qe2.tblADmDeliveries

you cannot save the view however, nor can you add tables. These restrictions are deliberate. You may only create and save working views using other views to which you have access (no pun). While you may type a proper select statment in and save a view, it will not work unless the objects in the view are allowed to you.

This would work, please save your own created views names like qe2_user_XXXXX. Try to avoide using spaces in the name.

Notes on User Views.

IT MUST BE STRESSED << EPISODES MAY NOT ALWAYS HAVE THE URNO IN THEM as in HBCIS>>  e.g. 123456-1>>

When using a tool to join these tables, to create new views many SQL utilities will select the ID as a default, this is in fact silly.

Always use both COMPANY_TLA and CAMPUS_TLA – and then – either Episode or UR to link tables based on Patient Episode or UR information.

An Example

SELECT TOP 100 rbh_Episode_master.*, rbh_ADM_Details.* 

FROM rbh_Episode_Master INNER JOIN rbhh_ADM_Details

ON

rbh_Episode_Master.Company_TLA = rbh_ADM_Details.Company_TLA

AND

rbh_Episode_Master.Campus_TLA = rbh_ADM_Details.Campus_TLA

AND

rbh_Episode_Master.Episode = rbh_ADM_Details.Episode

In the Case of ORMIS data, there is an ORMIS ID that is used to Link CASE base information and Procedure Detail.

Here is how the zzz_Ormis_Procedures view is created

SELECT fnEPID(tblOrmisCases.In_OR_Date, tblOrmisProcedures.UR,

'THTR', tblOrmisProcedures.Campus_TLA) AS Episode, tblOrmisProcedures.Company_TLA,

tblOrmisProcedures.Campus_TLA,

tblOrmisProcedures.ORMIS_ID,

tblOrmisProcedures.UR,

tblOrmisProcedures.Procedure_Code,

tblOrmisProcedures.Procedure_Index_int, tblOrmisProcedures.Procedure_Start_Date,

tblOrmisProcedures.Procedure_End_date, tblOrmisProcedures.Procedure_duration_int,

tblOrmisProcedures.Procedure_Surgeons_Count_int, tblOrmisProcedures.Procedure_Surgeons_Code,

tblOrmisProcedures.Procedure_Specialties_Code,

tblOrmisProcedures.Procedure_Anaes_Code,

tblOrmisProcedures.Procedure_Nurse_Int

FROM tblOrmisProcedures INNER JOIN tblOrmisCases

ON

tblOrmisProcedures.ORMIS_ID = tblOrmisCases.ORMIS_ID

AND

tblOrmisProcedures.Company_TLA = tblOrmisCases.Company_TLA

AND

tblOrmisProcedures.Campus_TLA = tblOrmisCases.Campus_TLA

WHERE (tblOrmisProcedures.Company_TLA = 'RBH')

In this Instance the EPISODE Key Data of Admit and Discharge Date are in the Episode master Table, Along with The URNO.

In some views, Episode information is Dynamic real time – up_to_date. A dynamic Episode is however, slow, as the stored procedure has to run for every row of a 1 million row query set. Setting a where clause using the Dynamic Episode Function would cause every row in the table to be evaluated, even if you only wanted one record!!, using the UR as a where would be better. You might get more records, but it will be quicker.

A dynamic Episode field is created as follows

SELECT rbh_AuslabData.*,

fnEPID(Service_Date, UR, Creating_Feeder, Campus_TLA) AS EpisodeNew

FROM        rbh_AuslabData

WHERE (Company_TLA = 'RBH')

The Creating_Feeder field needs to match an entry in the Episode Params table that can be viewed. In some cases it may need to be explicitly referenced e.g. ‘THTR’ as this system uses ORMIS as the creating feeder.

In other tables like Util tables the Episode is static, but therefore in light of new information added since it was assigned could be wrong. A process to periodically update and refresh episode information is planned. Stored Procedures do exist for this task and can be manually activated.

Note that the O type Episode in the Episode master, and in the OSIM DATA table my not be the Episode in the OPUtil table as it MAY be linked to an IP episode!!

The Database is very large, and queries need to be carefully targeted to minimise impact on other users and provide appropriate focused information. Use Date Ranges where possible.

Entering Dates