User Views Notes
Previous Topic  Next Topic 

Talons SQL Using the Views some thoughts, a preamble


User Views of Talons SQL have been created that limit company code information to the Company TLA appropriate. These are very basic views of the Data and are under development. It is expected that users will create their own views in some other application, and for this reason only read access to these views is allowed.



To access user level views, ONE method as an example is to use Microsoft Access.

Start Access


Go file NEW, and select the TASK pane option, "Project Using Existing Data.."

You will be prompted to save your project file - place it somewhere you will find it again, it will not get very big and will never hold any data actually in it. (An Access Project file is an empty shell to an SQL database.)



This Screen will Prompt you for the  connection details. These will vary, however given that you will be provided with the Server and Instance Name, to go in section one


this next information is specific to user views


2 Use specfic User Name and Password

¤ Checked

User Name

You Company Code in Uppercase

Password

Your Company Code in Uppercase then no space and the secret password

3 Select Database on Server

¤ Checked


Talons SQL

Click Test Connection

Until you get it right!!!



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

While ostensibly allowing for internationalisation, MS SQL Dates can cause tears. Usually the problem is that either the Workstation is not set to map dates correctly in the Control Panel or the SQL Server instance “locale”  “LocalSystem user” is (or cannot be) set correctly. There will be dry eyes if dates are ALWAYS entered in prompt fields as YYYY-MM-DD HH:MM:SS. This is never interpreted ambiguously It is an ANSI standard format (if used as a text field it also sorts correctly). If you type that into a field, and it then displays back in your correct local form, smile, you have won. The 7th of January and the 1st July have a lot in common! There are problems with MS Office applications and Date formats. That is not a problem CSH can solve. See Dates and Times