SELECT '['||Material->Sifra||'] '||Material->Opis AS Material, SUM(MasaBlago) AS MasaBlago FROM Tehtanje.Dokument WHERE DatumDokumenta BETWEEN '01/01/2023' AND '04/01/2023' AND (Material->Sifra %INLIST $LISTFROMSTRING('5,7',',')) GROUP BY Material ORDER BY %EXACT Material
The query returns all rows where Material->Sifra is 5 or 7. That's OK.
If I want to get all rows where Material->Sifra is NOT 5 or 7 I use query:
So just this morning we started to get this error from all of our SQL-client based tools that connect to our Caché SQL via ODBC:
[%msg: <Error instantiating cached query class in Prepare(): <METHOD DOES NOT EXIST>Prepare+41^%SYS.SQLSRV *%New,%sqlcq>] (State:S1000, Native Code: 190)]
I have a question related to running an SQL query for range of data.
I am running a query like:
Select A,B from table_name where A>=12345 AND A<=12390
- Where A and B are my two of the properties under the mentioned table/class definition
- A is an integer property
Question: The result of the above query does not return me all the values between the mentioned range of integer though I do have all the values in that range. I see that some of the values are missing in result.
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.
I've two columns in my table and the type must be interval of integer values for example : age must be interval between [2 and 6] years and wigth between [10 and 30 ] Kg , how can i define them when creating my table?
Any insights, news, alternatives or experience about sequence pattern querying like with Oracle's MATCH_RECOGNIZE, more declarative, less simple direct COS way? Both obvious solutions (shadowing data to oracle cloud or implementing MATCH_RECOGNIZE compiler in COS from scratch) seem big overkill. Do Intersystems have plans to adopt this "2007 ANSI standard proposal" and "SQL:2016 standard"
I have created a view to stage some data in a different format and then want to reference that view in a SQL query from a table that filters the data from the view using a property of the table.
Example:
select MsgId, FileName, (select ReportName from custom_view where MsgId = ReportId ) as ReportName from main_table
Is this even possible? When I try this, I get an error table not found for the view?
https://www.youtube.com/embed/lyACfOWDFFg [This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]
Is there a way or can it be done to use conditional logic in sql like so
Query Q1(formal as %String) As %SQLQuery [ Final ]
{
SELECT patientnumber,ID, CASE
WHEN ID = 50 THEN "The is 50"
WHEN ID = 30 THEN "This is 30"
ELSE "The quantity is under 30"
END FROM Audit.Table WHERE ID = :formal AND EndDate is null}
I use Documatic a lot to generate class documentation from comments embedded in the code. Is there a good way to create documentation for SQL views as well? Ideally I want to document each column in the view with HTML markup similar to how I document each method of a class with Documatic.
Say I have a property in a persistent class that stores list of colours and I would like to query that field and return a list and be able to loop that list to get individual colours how will l go about achieving this I have tried something like this but its not working as expected
&sql(SELECT colour INTO :colourList FROM favouritecolours)
While (SQLCODE = 0)
{
for i=1:1:$LENGTH($P(colourList,","))
{
set fvalue=$P(colourList,",",i)
write "the first"_fvalue,i,
}
}
The question is about queries on the System->SQL page. I have a class with a few columns on a global and the test output looks as expected, below. This works as expected as well returning a single row: select * from Utils.RoutineAuditReport where counter=4. However, other columns give an odd error like " Field 'AG' not found in the applicable tables^ SELECT * FROM Utils . RoutineAuditReport WHERE UserR = "AG". As you can see below, both the UserR column and the AG entry in it do exist. What could be the problem?
I've been accessing Cache tables from a developer/reporting side, but am now involved in a project to create a data warehouse for our application. I'm trying to find a query I can use to return the sizes of all the tables in the database, so we can identify the largest tables and handle those individually. Can someone give me a query I can run against our Cache database to return the sizes of all the tables from largest to smallest?
https://www.youtube.com/embed/7l-YT5y-nJo [This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]
I am currently using D $SYSTEM.SQL.Shell() command or management portal to retrieve the data from database. I just learned we can also run cache table sql queries in MS access. MS access already installed on my machine.
I need to ask how I can connect my database of USER or sample namespace in MS access to run these SQL queries.
By likes
.png)
