#SQL

Greetings to all!!! Suppose there is a table Mother (ID, Name) and Child (ID, Name, Mother), Mother in the table Childis a relationship. Let's say the task is to deduce the names of all the children whose their moms' names start with the letter 'A', I can do this in two ways in sql, and I can not understand the difference, the pros and cons that when to use:

1) SELECT Child.Name FROM Mother INNER JOIN Child ON Mother.ID = Child.Mother WHERE Mother.Name LIKE 'A%'
2) SELECT Child.Name FROM Child WHERE Child.Mother->Name LIKE 'A%'

Hi,

I have a class with around 400k lines and 60 columns. Class storage is Cache SQL storage (Mapped from a global).

 I want to create multiple indices on certain fields.

I am familiar with two approaches:

1. Create a new map (Index type) on a pointer global.

2. Create a bitmap index

Which approach is more recommended to be used in the case I described? If there are any other approaches, I will be happy to hear.

Thanks :)

Hi World

I've a problem when in extract a float value from my database

the problem is that i get  an interger instead of  float.

my record is 2,56 but when i do a select , the request extract only 2

can you have a solution  for it.

thank you

HI,

I have a query like below but its syntax is not accepted by Cache.

I would like to perform a left join on two tables first and then make a inner for this result with another table.

Given the constraint that we only allow one SELECT in the query, it is possible to achieve this semantics ?

Thank for your help.

Select * 
FROM ( sample . employee e 
LEFT JOIN  sample . company c 
on c . id = e . id ) g
JOIN sample . vendor v
on v . %id = g . attr 

I am pleased to announce that Release Candidates of Caché and Ensemble 2017.2 are now available.

Many customers have already downloaded the Field Test over the past few months, and we appreciate your feedback.

This release contains significant improvements, including:

• Parallel dejournaling to improve throughput of mirroring and journal restore.
• New iFind and iKnow features like co-occurrence search, performance improvements, and more.
• SQL enhancements in Query Auditing, optional ANSI SQL operator precedence, and Frozen Plan Evolution, along with our normal cast of query optimization improvements.

I have a class that has a list property, which contains a list of other objects, and I want to join against it in SQL.

Class Foo Extends %Persistent
{
Property MyBars As list Of Bar;
}

Class Bar Extends %Persistent { Property Name As %String; }

Simply querying the Foo table, I see that MyBars looks like a $LIST, so I tried using a query with the %inlist operator but that didn't seem to work as expected. The following query produces zero results:

select bar.name from foo join bar on bar.id %INLIST MyBars

Is there some convenient way to do this sort of join?

Hi, folks!

Is there any way to use $CASE or $SELECT functionality in SQL SELECT query?

E.g. something like this:

SELECT product, $CASE(status,"New":field1,"Payed":field2) as data from sales

To see either in data column either field1 or field2 values in regard of status value.

Hi, folks!

When you deploy DeepSee solutions you often do not want grant a User  %All Role to work with a particular Dashboard.

Consider a Dashboard 'Dash' with a few widgets where listings are being used.

If you manage a Role to get access to the Dash you need to grant access to %DB_DBNAME resource to have a database access,  grant access to a Dashboard resource (if any) and ...  grant SELECT accesses to all the tables involved in SQL queries being used in all the listings of widgets.

And every

Hi, i want to extract the timestamp values and order them low to hight , how can i do, you find here a screenchot of my batabase

thank's

Hi , World

this is i field in my global:

{"profile_id":"9XOzzcI8NfSUjxAhEt0cTLRejwmp6HPi","biometrics":[{"timestamp":"2017-05-17T13:45:40","utc_offset":"+02:00","resting_heartrate":120.0,"spo2":98.0,"activity_id":"591c540aac8f295479ee14ce"}]}

for information: it's respective key is "peyload"

my question is :

I want to extract the timestamps value , and the profile_id value , how can i do?

thank's

hi world, i want to browse an sql table on cache, and get min ,max value from one collumn, how can i do it ?

thank's

Hi,

I'm developing an integration between Caché servers by ODBC conection, and I have the following problem.

In this call:

call COSClass_Methode('222169^^98^155^64530^06:30^021542987897458855441112877855^1^0^281992^GC')

the ODBC driver is truncating the string to 50 characters.

If I run this same command with $system.SQL.Shell(), this doesn't occur.
I did a test creating several parameters for COSClass_Methode, and they all have a 50 character limitation.

Does anyone know why the ODBC Cache Driver limits the number of characters per parameter of this type of call?
Or, how can I adjust this?

Is it possible to execute a sql update statement from objectscript? This code isn't working for me.

Set tSQL = "UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)"
Set tStatement = ##class(%SQL.Statement).%New()
Set tSC = tStatement.%Prepare(tSQL)

If I write my dynamic sql to the event log, copy and execute it in the Management Portal, it works fine.

Hi everyone, I have a SQL service that is working fine, except I don't want it to run on a schedule or continuously. I'd like to only run when requested. Run once and then stop until another request. Is there anyway to set up a service like that?

I have a simple SQL service that does a simple select from an SQL database. After the select, I do an update to set the ProcessedFlag to "Y" for yes, so my next pass doesn't select records already processed. The service works fine, except when it's done I get the error below, anybody know what is causing this error?

I worked through the Community for proposals to provide end users
in an easy way with data formatted as EXCEL sheet.

There is a great article Tips & Tricks - SQL to Excel

there's an important message embedded: "EXCEL can interpret HTML tables and display them as usual"

Where's the light weight export to EXCEL ?
Good old CSP is well equipped to produce HTML tables accepted from EXCEL as input.
With modern Browsers you don't even need <head> and  <body> tags.
So the required code around your SQL result set is really slim.
And you are free to add any formatting you need either by HTML or in SQL.

In $system.SQL.Shell(), it would also report the number of global references for a given query.

Is it possible that I could also get this statistics from the %SQL.Statement interface when I launch a dynamic sql?

Thanks.

I use %SQL.Statement. The original column names are not contained in the metadata.

Sample query:

SELECT
 column1->name as name,
 column2 age
FROM sample.table

How to get:

•  column1->name
•  column2 

I am currently working on a issue with WRC on one of my Inbound SQL Adapters not returning all the records it should be. If I looked at the count of the records in Ensemble and compare it to that of a Microsoft SQL View, Ensemble seems to be off by a few records here and there. I am using a full dynamic select statement in my settings of the adapter.

In a previous exercise, I was able to show the power of Caché.
A medium-designed set of interdependent tables with some GB of data.
URLs cross reference over some million pages resulting in ~3 billion records

Competition was between

• Caché
• PostgreSQL
• MySQL

Criteria were Speed + Storage consumption
I composed  a customized loader fed over a "raw" TCP connection
Mapping the "objects" into the final table by directly writing to Global Storage.

Is it possible to import a Cube from SQLServer (format .abf) in a Cache?

I have an  .abf file and I want to open it in Cache. Is it possible?

Thanks

hi

  I execute sql  like this:  select * from DHC_PatBillDetails where PBD_PBO_ParRef>='2046121'

error message:

but   the sql:   select * from DHC_PatBillDetails where PBD_PBO_ParRef='2046121'   can be executed successfully

why?

I have a query string that I am creating programmatically, based on some user inputs.  The user might search on 5 fields, or 8 fields, or no fields.

In my sql statment, some of these fields require parameters in the %Execute statement.

For example:

if user picks lastname, sql = "select * from person where lastname = ?"

if user also picks age, sql = "select * from person where lastname=? and age > ?"

I then have these lines of code to create my result set:

set statement = %SQL.Statement

statement.%Prepare

resultset = statement.%Execute(param1, param2)

-- but it might be 

resultset = statement.

Hi,

Is it possible to use the value of a column that is populated by its own subquery,  in the WHERE clause of the outer-query ?

The following fails (it does not parse syntactically):

SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1 WHERE C>10

I guess I could wrap it up as in inner query of it's own - this way (which works) :

Select * from 
(SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1)
where C>10

but I was wondering if there was some syntax in the original snippet that I could do instead.

Thanks - Steve

Hello! So, my knowledge on the Cache database is extremely limited, and I was hoping I could find some assistance here. I'm connecting to the DB via ODBC. The table(s) I'm interested in are named as such nameYYYYMMDD. So each day, a new table is created with logs. We'd like to grab these records each day, for the previous day's logs. 

My question is, since the table name changes every day, how can I go about automating this? Can I craft a variable that is the table name plus some date functions and use that?

Hi everyone

Is there any way to change a class definition (especifically a query definition during the compilation time)?
The idea is: 
    I have an abstract class with a parameter where I will define the ROWSPEC of a query and some methods to populate e temporary table
The implementation class will override the parameter, specifying the ROWSPEC of this implementation, and the methods will populate the rows in the same format as the ROWSPEC.

I want to change the ROWSPEC of the inherit query according to the implementation of the overriden parameter.

I have a query text that I need to execute and return results as xml.

It may be an SQL query or an MDX query. Is there some easy way to determine which one is it?

Any ideas?

Hi!

I have a global-mapped class that I want to add a transient property through (or at least, not stored on the database). This property is the sum total of various charge classes that are linked to the class via child relationships.

I want this new transient property to be visible at least via SQL.

My current approach is to total it up in a ClassMethod called via SqlComputeCode. It seems to work, but feels clunky. I wonder if there is a way without passing the %%ID through? I tried it with $this but that only returns the object name in a ClassMethod.

Greetings,

I am working on the first of many triggers which will have identical code upon row insertion or update of a single column.  According to the document I should be able to define a multiple-event trigger using Cache SQL/DDL.

Here is a link to the current CREATE TRIGGER documentation.  Within the description section is the following paragraph:

A single-event trigger is triggered by a specified INSERT, DELETE, or UPDATE operation. A multiple-event trigger is defined to execute when any one of the specified events occurs on the specified table.

Quotes (1NF/2NF/3NF)^ru:

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else). The same value can be atomic or non-atomic depending on the purpose of this value. For example, “4286” can be

• atomic, if its denotes “a credit card’s PIN code” (if it’s broken down or reshuffled, it is of no use any longer)
• non-atomic, if it’s just a “sequence of numbers” (the value still makes sense if broken down into several parts or reshuffled)

This article explores the standard methods of increasing the performance of SQL queries involving the following types of fields: string, date, simple list (in the $LB format), "list of <...>" and "array of <...>".