New post

#SQL

13

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

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'

0 1
0 617

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!

0 1
0 320

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?

0 7
0 397

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.

2 3
0 426
Question
· Sep 5, 2019
Ambiguous sort column error

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:

0 4
0 209

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:

0 1
0 319

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.

0 1
0 313

I'm having trouble accessing the snapshots EnsLib.SQL.Snapshot in %Library.ListOfObjects that are returned from method ExecuteProcedure in EnsLib.SQL.OutboundAdapter. The Microsoft SQL stored procedure I am executing returns multiple resultsets.

The issue I am having is that my code works fine when executed in the business operation (commented out in the code below) but when it is executed in the business process it errors. Any ideas as to why this happens? The error I get is:

0 2
0 644

The select query here "select Settings from Ens_Config.Item" in the SQL Query of management portal returns this:

ValidationHost 7ActOnTransformErrorHost1 ReplyCodeActionsHostE=D AlertOnErrorHost1 9 AlertGroupsHost$LabAlertGroup,1-CriticalAlertGroup BusinessPartnerHost

How do I isolate just one of the settings e.g. ReplyCodeActions or LocalInterface

For example, i want to search for all entries that have a value for ReplyCodeActions

I realize i can open the production in studio and search for those setting values.

0 5
0 1.7K

Hi guys!

As you know there are two (at least) ways to get the stored value of the property of InterSystems IRIS class if you know the ID of an instance (or a record).

1. Get it by as a property of an instance with "Object access":

ClassMethod GetPropertyForID(stId As %Integer) As %String

{

set obj=..%OpenId(stId)

return obj.StringData

}

2. Get it as a value of a column of the record with "SQL access":

0 18
0 1.2K

Hello,

When i click on the menu to run the Data import wizard from MP, i receive following CSP error

<UNDEFINED>zOnPageHEAD+229^%cspapp.exp.utilsqleximwizardcontent.1 *schemaname : CSP Error

It is happening for all the namespaces. Looks like some permission issue. Same issue with Data Export wizard. Help to resolve this will be appreciated.

I am using

Cache for Windows (x86-64) 2017.2.2 (Build 865_0_18763U)

Thanks,

0 3
0 318

Hello all,

I have a Recordset object which contains data from a table "XYZ".

Currently i use this object to extract data using %Get(COL1,COL2...) in a loop and than pass it to a function which inserts the data into another dynamically created Table "ABC" for each record. This takes a lot of time when 100's of records.

Is there a way i can directly copy a RecordSet to a dynamic table without looping through..?

Something like copy Recordset (COL1,COL2..)--> "ABC"

Thanks,

Jimmy

0 6
0 931

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, 
} 
}
0 1
0 555

Hi everyone,

Im new in cache, i came from Java and im missing some features that i couldn't find in the documentation, I hope you can help me with this questions.

Just a brief introducction:

- Im in a project with old cache version, so saddly i can't use Eclipse + Atelier, so im using Studio.

- Currently im in a project with persistent classes, we want to turn apart the globals and focus on tables.

The questions:

0 5
0 450

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

0 2
0 523

here is my trigger:

CREATE TRIGGER "SQLUser".SYM_ON_U_FOR_SYM_CHNNL_125123 AFTER 
UPDATE
   ON "SQLUser"."sym_channel" REFERENCING OLD ROW AS OLD NEW ROW AS NEW FOR EACH ROW 
   DECLARE @var_row_data VARCHAR(16336);
WHEN
   (
      1 = 1 
      and 1 = 1
   )
   -- I will use OLD/NEW alias here set @var_row_data = ''; LANGUAGE sql insert into mytable (id, data) values(1, @var_row_data);

here is error message:

0 1
0 269
Question
· May 7, 2019
Linked Tables and Dialects

Hi! I've been fiddling with linked tables to get data from other servers, and I encountered a problem that I'm curious about. Maybe I'm not using these tools as intended or there's more going on, so I'm asking here.

I'm running a query on linked table A, something simple like this:

select name from A where id = 5983658923646

And I get this error:

[SQLCODE: <-400>:<Fatal error occurred>]

[%msg: <>]

0 6
0 605

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns.

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code:

1 5
0 693