Conrad,
your question have two parts
a) For queries like
WHERE a = (select ...)
if subquery returns more than one row, Caché will compare left part with first value of subquery.
b) If several people are tied for the minimum age, then following query prints all of them
SELECT Age,Name,home_state
FROM Sample.Person p1
WHERE age = (
SELECT min(age)
FROM Sample.Person p2
WHERE p1.home_state = p2.home_state)
Hi Cheng Cheng.
This doc lists all intrinsic properties: http://docs.intersystems.com/cache20161/csp/docbook/DocBook.UI.Page.cls?KEY=D2RMDX_intrinsic_properties_list
Documentation explains both functions well and with examples, so I encourage you to look into them. Especially first two examples for %ALL function
ALLMEMBERS -- function that returns a set of all members of the given level or hierarchy http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2RMDX_AllMembers
%ALL -- function that enables you to use a member while ignoring any ROW and COLUMN context that uses the hierarchy to which this member belongs. http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2RMDX_percentAll
As to your question about calculating percentage of the top level, here is sample that for each product calculates revenue percentage of total from all products (2nd column) and from category for this product (3rd column).
RevenuePctOfAllProducts and RevenuePctOfParent are calculated measures that defined as follows:
RevenuePctOfAllProducts:
Measures.[Amount Sold] / SUM(Product.[All Product].%ALL, Measures.[Amount Sold])
RevenuePctOfParent:
Measures.[Amount Sold] / SUM(Product.CurrentMember.Parent, Measures.[Amount Sold])
So the full query looks like:
WITH
MEMBER [MEASURES].[RevenuePctOfAllProducts] AS
'Measures.[Amount Sold] / SUM(Product.[All Product].%ALL, Measures.[Amount Sold])'
MEMBER [MEASURES].[RevenuePctOfParent] AS
'Measures.[Amount Sold] / SUM(Product.CurrentMember.Parent, Measures.[Amount Sold])'
SELECT NON EMPTY {[Measures].[Amount Sold],
[MEASURES].[REVENUEPCTOFALLPRODUCTS],
[MEASURES].[REVENUEPCTOFPARENT]} ON 0,
NON EMPTY HEAD(NONEMPTYCROSSJOIN([Product].[P1].[Product Category].Members,[Product].[P1].[Product Name].Members),2000) ON 1
FROM [HOLEFOODS]
As far as I understand such usage of GROUP BY and select columns is not standard in SQL world.
That is -- if we GROUP BY some fields, then in SELECT list we can have either fields we group by or other fields as arguments of aggregate functions.
We can write
SELECT home_state, max(age)
FROM sample.person
GROUP BY home_state
But we cannot write
SELECT home_state, max(age), name
FROM sample.person
GROUP BY home_state
It is not clear -- what name out of all rows that have the same home_state do we want.
Consider following data in Sample.Person:
Age Name Home_State
10 John MA
10 Jim MA
What name will following query return John or Jim?
SELECT Age,Name
FROM Sample.Person
GROUP BY Home_State
HAVING Age = MIN(Age)
I prefer following variant of the query with join:
SELECT Age,Name,home_state
FROM Sample.Person p1
WHERE age = (
SELECT min(age)
FROM Sample.Person p2
WHERE p1.home_state = p2.home_state)
That returns both rows from the sample data above.
I wonder if this extra line is caused by the line break between </script> and the xml header.
Can you try to move
Before <script> ?
Hi Cheng Cheng.
%DeepSee.ResultSet has property %UseCache to make query not to use cache. You can set it if you run using %DeepSee.ResultSet directly.
As far as I know this is not configurable from Analyzer or User Portal. And it cannot be set system- wide or for particular cube.
Regards, Alexander.
Hi Javier.
If you use ExecuteQueryParmArray then I think you should also
Set par = 1
If you use ExecuteQuery then you should call it like
Set tSC = ..Adapter.ExecuteQuery(.QueryResultSet,sql,par)
Also please notice that Base64Encode does not work with Unicode characters: http://docs.intersystems.com/cache20152/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25SYSTEM.Encryption#METHOD_Base64Encode
Do you just run the page or put some value in textarea?
Attila, can you please elaborate on why it is potentially dangerous to refer to data outside of current record unless Calculated is specified?
I personally use Calculated only when property value might change during oref lifetime. If property value is not changed once object is in process memory, then there is no need for Calculated, as I understand -- Transient is enough.
I usually create new empty DB, then ^GBLOCKCOPY globals from old DB. And then replace old DB with the new one.
Yes, Illegal CSP Request usually means that access to this particular class is prohibited.
If your web application named '/csp/healthshare/mhclib/' then you need to enable classes as follows:
set ^SYS("Security","CSP","AllowClass","/csp/healthshare/mhclib/","%SOAP.WebServiceInfo")=1
set ^SYS("Security","CSP","AllowClass","/csp/healthshare/mhclib/","%SOAP.WebServiceInvoke")=1
Notice that calling SOAP Service via test webpage (%SOAP.WebServiceInvoke.cls) is independent from calling web service via SOAP protocol. For that you should check option "Inbound Web Services" in Web application settings.
%SOAP.WebServiceInfo and %SOAP.WebServiceInvoke are just pages to test web services via Browser.
SOAP protocol itself does not use these pages.
Message "An error occurred with the CSP application and has been logged to system error log (^ERRORS)"
means that you can check error in Management Portal -> System Operation -> System Logs -> Application Error Log -> [Namespace]
Or for debugging purposes set error page for web application to %CSP.Error.cls http://docs.intersystems.com/cache20161/csp/docbook/DocBook.UI.Page.cls?KEY=GCNV_R2015_2#GCNV_C140105
and see errors on the page itself
You can create one post that is just an index and reference this post in each new material.
Then just update this index post once after each new posting
What error do you receive?
Hi Randy.
FILTER option in SETTINGS references "Allowed Default Values for Filters" section of doc http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_settings#D2IMP_filter_default_values
You can specify set of members as follows:
"{&[keyval1],&[keyval2],&[keyval3]}"
Then you need to URL encode this value: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_dashboards#D2IMP_dashboard_url_encoding
Particularly by replacing & with %26 and , with %2C
For example, if I want to pass to "Basic Dashboard Demo" several values for Home Zip Code filter I need to create following string:
&SETTINGS=FILTER:[HomeD].[H1].[ZIP].{&[32006],&[32007]}
And then URL encode it to
&SETTINGS=FILTER:[HomeD].[H1].[ZIP].{%26[32006]%2C%26[32007]}
Hi Blaise.
Does following query looks like what you need?
With Member Measures.[RunningTotal] As
'AGGREGATE(%TIMERANGE(DateOfSale.[Actual].[MonthSold].&[201101],DateOfSale.CurrentMember),
MEASURES.[Amount Sold])',FORMAT_STRING='$#,#.#'
SELECT {Measures.[Amount Sold],Measures.[RunningTotal]} ON 0,
[DateOfSale].[Actual].YearSold.&[2016].CHILDREN ON 1
FROM HOLEFOODS
It prints two columns -- Revenue in current month and Total revenue from 2011-01 up to current month. Rows are months of 2016 year.
Yes, seeing comments/answers in compact mode is good feature.
Maybe one column is enough -- with two numbers separated by slash -- "5/10" -- 5 answers and 10 comments and sort by sum of these numbers. But that is just an idea.
Also on some sites (reddit, for example) comments/answers can be sorted not only by date but also by votes.
As an example, see https://www.reddit.com/r/announcements/comments/4mv578/affiliate_links_on_reddit/
Also it would be interesting to sort by Views column to see the most viewed article.
Hi Paul.
Compact view is great. With it I can see sixteen topics on one screen instead of four.
What I would like to see is links instead of ajax buttons.
For example, I want to bookmark compact view of unanswered posts.
I've tried linking to https://community.intersystems.com/?filter=unanswered, but that is just a JSON content.
I think googling "encoding heuristics" should help.
For example, https://gist.github.com/TaoK/945127
As far as I know office365.com requires STARTTLS that is supported as UseSTARTTLS property of %Net.SMTP starting with Cache 2010.2
Adding my vote to fixing this.
Thank you Jose-Tomas.
As I understand (please see last three paragraphs of "Cyrillic4, Cyrillic3, and umlauts" section), Spanish language has some specific collation rules and satisfying these rules require more general (and complex) algorithm than sorting two strings based on individual character codes. That is why Spanish collations are slower then Caché Standard. https://en.wikipedia.org/wiki/Alphabetical_order#Language-specific_conventions
The more processing you application does besides looping with $Order/$Query and inserting data into global the less impact has collation performance. Running ^%SYS.MONLBL with globals/locals with different collation settings should give you more accurate performance data.
strace ccontrol list on my Ubuntu shows that ccontrol looks in file /usr/local/etc/cachesys/cache.reg
2015.2
As far as I know it is not possible to switch it on.
You might find function DESCENDANTS (http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2RMDX_Descendants) helpful.
For time dimension, all members of the level are generated and available to queries that show empty. This means
SELECT DESCENDANTS([DateOfSale].[YearSold].&[2015],DateOfSale.DaySold) ON 1 FROM HoleFoods
will show all days from 2015 on rows, whether there are facts recorded for those days or not.
SQL Adapter does some metadata caching.
See, for example, doc for method EnsLib.SQL.Common:ExecuteProcedure http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=ENSLIB&CLASSNAME=EnsLib.SQL.Common#METHOD_ExecuteProcedure
"Appending a '#' to the pIO argument (or passing only '#') prevents the adapter from using cached DescribeColumns() results for the procedure call output, forcing a fresh call to ODBC DescribeColumns() every time the stored procedure is invoked. This can be necessary if the procedure is capable of returning different sequences of result types from consecutive invocations"
I'm not sure if this is what happened in this case, though.
You can run tPatient.%ValidateObject() and check returned status before doing XMLExport