New post
Question
· Jul 16, 2021

First Script Accessing Trackcare Code Table Element

#SQL #TrakCare

Dear Experts,

 

I have to pull out a report of certain code tables and this is my first script and I need to write many.

1. If I have to refer to the table, seeing the reference in the components with reference to SQL table and column, 

can I use Select < What I need from the table>

Inner join with reference table? 

I tried, I used the code table reference in the components, but I am getting errors, 

 

Code Table
Code    CT_abc
Loc ID
Type

P: Public

Pv: Private

C: Community
code CT_loc.edit
Loc Location Id
Hopital  

Description

C: Cardiology                                 

p: Pathology

I tried this 

SELECT 

CT_abc.Type as "Hospital Type", CT_abc.Loc as " Location Type"

From ( CT_abc

INNER JOIN Loc ON CT_loc.edit.loc = CT_abc.loc);

But no matter how I try, I am getting errors then, I have seen some developed tables, could not relate to the tables and table parameters.

How can I use select where table.parameter = x AND table.parameter = Y etc 

Please can you help me point to relevant material which can explain how these code tables are designed, relationship between them and how to access each element or update them.

Product version: HealthShare 2020.1
Discussion (3)1
Log in or sign up to continue
Steven Potashnyk · Jul 18, 2021 
Select FACIL_DESC as "Hospital Type"
From SQLUser.CT_Hospital
JOIN SQLUser.PAC_FacilityType on (HOSP_FacilityType_DR=HOSP_FacilityType_DR)

output :

Private Hospital
Private Day Facility
Other
Public Hospital 
Public Day Facility

or the easier approach is using the arrow syntax

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_implicitjoins

Select HOSP_FacilityType_DR->FACIL_DESC
From SQLUser.CT_Hospital

Hope this helps

2 0
reach.gr G · Aug 2, 2021

Hi @Steven Potashnyk 
Firstly thank you a ton for answer. Finally, I got response to a question here (my first). Please can you give me the updated reference to the link you have provided, says link cannot be reached. Is there any updated link, (Actually I am not able to access even the parent link the Documentation Tab) 

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_implicitjoins
Also thank you for the response. Much appreciated. I am not sure, how the way JOINS used is different to standard ANSI SQL standards, but this code and the working examples should give me data to co-relate and understand more.

Does this apply to tables as links, example: If PAC_FacilityType is linked to table CT_Hospital  ? There are some tables show linked to code tables and are highlighted when there a data in the CT_Hospital. 

PAC_FacilityType component Items does not have any related or common Primary key to CT_Hospital except layout says, Parent link that is a string.  Even Select * from PAC_FacilityType throws error even though, component Items clearly has that table.  Thank you very much again.

0 0