SQL

Syndicate content 32 

Hi, I want to INSERT a customerID and customerName to a table if the customerID does not exist. Or UPDATE if they've changed their name.
I've reviewed the documentation (https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=RS...) and have confused myself with the INSERT OR UPDATE statement. Rather than using two individual statements , is there a way to get it in one?

What I have at the moment doesn't do anything. In fact it errors.

Last reply 3 days ago
0   0 2
68

views

0

rating

cache 2017.2 windows 10

Is there any way to change the sql Shell interface to output a <TAB> between the fields instead of outputting one or more spaces between columns

 

I needed to run an sql ( that took a while to run) and then  cut and paste the generated output into excel, trouble was the paste into excel was not very good for columns when there's spaces used between the columns

 

kev

Last reply 9 days ago
0   0 1
40

views

0

rating

I'm trying to get a count of specific message type with a specific entry and thought I could build the query in Message Viewer but  this does not provide counts (as far as I am aware). So when I take the SQL from 'Show Query' it omits the segment criteria as the code shows below. 

 I have attached the criteria that has been excluded. Is this possible?

Thank you

Last reply 11 days ago
0   0 3
651

views

0

rating

Hi,

I get this error [Cache ODBC][State : HYC00][Native Code 469] and zero rows returned with the following query.

SELECT "Timestamp","Skillset"  FROM "dbo"."iSkillsetStat" 
WHERE (Timestamp >= '2020-06-29 00:00:00' 
AND Timestamp < '2020-06-30 00:00:00')

I'm using Qlik Attunity Replicate to extract data, the query was formatted  by the tool. The query will return data in excel and SSIS, but not using Replicate.

So far I've tried using two ODBC drivers

-----------------------------------------------------

Last reply 15 July 2020
0   0 1
80

views

0

rating

Hi, I have a timestamp of 201906192359 with a HL7 and I need to add a minute to it to get 201906200000. Is there an easy way within Healthshare to do this?

It seems easy enough within SQL but I cannot get the SQL to work within Healthshare,  this is what I have for SQL which does the job in SQL Server.

 SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(@test,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','');

 

Last reply 22 June 2020
0   0 3
74

views

0

rating

Hello, In the DTL, is there a way to set a value for the HL7 data element in the code section? For example, set target.SetValueAt("PID:3(k1).1)") = mrn (mrn is the value returned from the SQL query) When I ran the test utility, I got this error message. ERROR ErrException: zTransform+27^testclass.TEST.1 *SetValueAt,EnsLib.HL7.Message -- logged as '-' number - @' set target.SetValueAt("PID:3(k1).1)") = mrn' I tried both target.SetValueAt("PID:3(k1).1)") and target.GetValueAt("PID:3(k1).1)"). That didn’t make a difference. The code still error out.

Last reply 11 June 2020
0   0 2
67

views

0

rating

Hello,

I work as a contractor for the Department of Veterans Affairs. I want to read data from an Excel workbook. I searched posts here and saw Apache POI suggested. I believe Apache POI is not approved for use within VA. I also believe we should be able to run SQL query against Excel. Has anybody done such a thing or are there other ways to read data from Excel workbooks?

Thanks

Last reply 29 May 2020
0   0 4
200

views

0

rating

Hey Community,

my Caché Version: 2013.1 and can't update now.

is it possible to highlight SQL Statements like in embedded SQL with all the features from SQL Statements?

Actually I use SQL Statements with a simple string like:

set myquery = "SELECT TOP 5 Name,DOB AS bdate,FavoriteColors FROM Sample.Person"

But when the queries are more complex it will be very cluttered and unstructured.

Last reply 22 April 2020
0   0 1
115

views

0

rating

Hi ,

 

I have a code written in cache  sql and trying to understand it, below is the code , can anyone help me understand what does that mean 

ex - !! dosage_unit !!

 

 

SELECT (CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) !! (CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN '' ELSE (', ' !! dosage !! ' ' !! dosage_unit !!

 

Thank You in advance.

Last reply 19 March 2020
+ 1   0 3
159

views

+ 1

rating

Is there a way to query the database structure?  In SSMS there are queries for finding tables with a column with a certain name (using LIKE).  And there is the redgate tool SQL Search.  But I'm not sure how to go about looking for columns that have say a value of 'PATID' and returning all tables that match.  Does anyone know?

Last reply 15 March 2020
0   0 3
186

views

0

rating

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ?    Thank you

KR,

Amine

 

 

 

 

Last reply 12 March 2020
0   0 3
146

views

0

rating

Let's say I start with a date range of '1-5-2019' to '5-25-2019' that occurs on one row.  I'd like to ultimately have this show as 5 rows in Crystal Reports as shown below

Result

1-5-2019  1-31-2019

2-1-2019 2-28-2019

3-1-2019 3-31-2019

4-1-2019 4-30-2019

5-1-2019 5-25-2019

 

I found a result that worked in T-SQL, but I'm not sure how to translate it to Cache SQL.  The T-SQL code is

Last reply 3 March 2020
0   0 1
98

views

0

rating