#SQL

I am trying to make architecture for my project. And for it, it needs to make list property in which there is list of objects of another table, the data must be unique, therefore I make sql-query to check objects of another table if it is used or not, but I can't make sql-query to check elements in list in property with elements of another table. Can you help me? In property ID of object is used.

I have a database which needs to be synced to another system and considering to use the SQL Inbound adapter to 'watch' the database. The other system, however, has a bulk API so I'd prefer to send multiple SQL rows in a single message to that system. The SQL Inbound adapter seems to trigger a single call (to a process or business operation) per SQL-row. However, the documentation here: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY… in example 5 seems to suggest that if I exclude a KeyFieldName I get multiple rows in my

Is it possible to use a class parameter in class query?

Example:

Class SomeClass
{

Parameter SOMENAME = "Name1";

Query SomeQuery() As %SQLQuery
{SELECT ID || ..#SOMENAME  || Name FROM Table}

}

Hello everyone,

I try excuting a SQL Query like this SELECT ID, CompanyName FROM Company WHERE CompanyName LIKE 'condition' 

But if I want to search CompanyName have special char %, ex: '100% Co' , 'Group of Mr.%', 'The %TaxProp'...

How can I take % to query, with  

it'll get all record in table.

Someone give me an idea. Thanks so much!

I have a task that has been working successfully for some time now but when I attempt to recompiled I get this error:

Hi Community,

New video, recorded by @Stefan Wittmann, is already on InterSystems Developers YouTube:

🗣 Dynamic Data with JSON

We have in DB two books, first is loaned because it has the Friend ID, and the other is in the shelf.

I execute a class query to get all loaned books:

    Query BooksLoaned() As %SQLQuery
    {
        SELECT *
        FROM Library.Book
        WHERE Friend IS NOT NULL
    }
 

Which I have tested throught the portal:

My task is to retrieve all the loaned books and return them in an array. Method code:

Greetings community. I would like to know how to migrate a BD in production to a local environment. When I have a system in production (BD Sql Server) what we do is mount a local copy to do the analysis with the data and not occupy resources of the system in production. My question is: How do you do it with Intersystems technology? I already tested the PowerBi connector and it looks great, but that's where the question came up.
 

Hi guys,
I ran into a strange (for me) situation, when I run same query but change the WHERE clause the plan is different and is not connected to the additional condition.
Query that doesn't use the necessary index:

Can anyone tell me how to add a simple running total in Cache SQL.

I'm selecting a quantity in the first column and want a running total in the second:

9 ,   9

2 , 11

7 , 18

Hello,

Is there a way to list all items in a production (incl their status/color) by using a sql query? This is for monitoring purposes. 

Thank you in advance!

Regards,

Luk

When calling the below stored procedure using the management portal (Run Query) no data is being retrieve, but if it is directly executed management portal data will show.

"IN" does not work changing it to "=", "like" the stored procedure will work. Does any one know how to fix this ?

Pass values to code is  'AB','TS','SK','GM'

Query LoadData(code As %Library.String) As %SQLQuery [ SqlProc ]
{

SELECT STRING(Descrtiption,' (',Code,')') as Description,Code FROM Test.Codes 
WHERE Code  IN  (:portCode)
}

Hi, we are a veterinary lab and we use both the LAB and FIN systems of Antrim. Now we are looking to expose the data in a SQL/Object compatible way so we were wondering if same / similar things had been done by other community members already? If so, could you please share your approach / experience / gotchas with us and we are all ears. I can be reached at yang.jiao@antechmail.com . Thank you! 

At LifeLabs, we are focused on our vision of building a healthier Canada!  We are the largest community diagnostics laboratory in Canada with over 350 collection centers, 21 laboratories and service over 19 million patients each year. As the Software developer, you will be concerned with all facets of the software development process.  You will be responsible for the design of application modules, maintain and deploy software applications to meet user and business needs.

I know &SQL returns only one result but is 

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

Hi Community!

Please welcome the new video on InterSystems Developers YouTube Channel:

Optimizing Your SQL Queries

Hi Everyone!

New video, recorded by @Stefan Wittmann, is already on InterSystems Developers YouTube:

SQL and Object Access

When i use ##class(%SQL.Statement).%New() -> .%Execute() and then .%Next() to go through the result set, i could only go through part of the result. And then, i have to call .%NextResult() to go through the next part of the result. BUT, the .%Display() could show all of the result.

How can i go through the full result once,like .%Display()? Or how should i do to go through the full result? Is there anyone can help me? Waitting!!!

I have several BPL's that act like SQL Server Integration Services (SSIS) where they are transferring data. When I do this they are all being written under 1 session id, is it possible to make the BPL create multiple session ID's as it is cycling through a Snapshot, so it is easier to read in the trace?

Thanks

Scott Roth

hi,

i have two where-clauses:

a)  ... WHERE company=1 and product=7

b)  ... WHERE product=7 and company=1 

with other words the position of the where fields are swapped.

now the question is:

bring the where clauses a) and b) the same performance(queryplan) or do i have to write it in a special order???

in my point of view, there is a  parser/optimizer who cares about this, so i don't have to care about. 

Hello,

I have a small SQL question.  

Running an example queries in our Samples Namespace: 

1. select top 5 Description,Category from Cinema.Film order by Category - runs fine no issues and returns 2 columns as expected

2. select top 5 * from Cinema.Film order by Category - runs fine no issues and returns 8 columns as expected

3. select top 5 Description,Category,* from Cinema.Film order by ID - runs fine no issues, and returns 10 columns, with my first 2 repeated

4. When I try to combine the first 2 queries: 

select top 5 Description,Category,* from Cinema.Film order by Category

I get an error:

Hi, I have used CSP to exec SQL selects from  any own NAMESPACE. But in our servers we have many SQL GATEWAY CONNECTIONS.

I'd like to create a CSP page that could use these gateway to exec SQL using these gateway connections, only Administrators will use that page to launch many select at many dsn. I'm not sure if we must deploy that CSP on %SYS namespace and how to use DSN(SQL Gateway connections) that are defined on server.

Anoyone has made that?

For example:

DSN CLIENT ONE

DSN CLIENT ONE

CSP Webpage:

TEXTBOX: Introduce your select:

select count(*) from product where stock<100 

OPTION BUTTON:

To request a copy of the examples only – please see me, will email them out for free.  pmkadow@gmail.com

To request a copy of the examples only – please see me, will email them out for free.  pmkadow@gmail.com

To request a copy of the examples only – please see me, will email them out for free.  pmkadow@gmail.com

Hi, I’m trying to setup a Business Operation where the Property is a drop down list of Strings.  I was able to produce the desired effect but only using a hard coded list like Property LookupProp As %String(VALUELIST = ",value1,value2").  Instead I would like it to be populated from lookup table so I created a method to use SQL to fetch the result set from a Lookup table and then return it as the desired string. But doesn't allow me to do declare the property using the method like this: Property LookupProp As %String(VALUELIST = ##class(MyClass).GetLookupValues());

Our team is reworking an application to use REST services that use the same database as our current ZEN application. One of the new REST endpoints uses a query that ran very slowly when first implemented. After some analysis, we found that an index on one of the fields in the table greatly improved performance (a query that took 35 seconds was now taking a fraction of a second).

We saw this improvement on our development system and our test system. However, when we moved the code to the production system, the query still took “forever”. What went wrong?

I have created store procedure using management portal.

how to alter or modify SP using SQL in management portal.

Please refer below screen.

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet:

SQLCONNECT1 = tableUsers

SQLCONNECT2=tableLogEntries

select * from tableusers u, tablelogentries e where e.userid = u.userid

I keep getting the error References to an SQL Connection must constitute a whole subquery

I've setup ODBC connection so I can access Cache data within SQL Server.

I want to be able to write SQL queries for internal monitoring purposes, similar to what's possible with SQL Server.  Specifically I want to be able to check mirroring status (i.e. check which is the current primary mirror member), check the status of any Ensemble productions (started/stopped), check the status of business hosts etc.  I want to do all of this from SQL Server to go with our other system monitoring solutions.

Hello community! I have to work with queries using all kinds of methods like embedded sql and class queries. But my favorite is dynamic sql, simply because of how easy it is to manipulate them at runtime. The downside to writing a lot of these is the maintenance of the code and interacting with the output in a meaningful way. In an effort to have as much dynamism as possible so that we're not rewriting code constantly, and so that we have as little code possible (while still making sense and getting the job done), I made a function that takes in any dynamic query and outputs a dynamic object.