Skip to Content

How to create calculation view with string_agg function

Is there a possibility to convert a sql query with STRING_AGG to a graphical calculation view?

An example of my sql query that I want to convert is this:

SELECT
	'ORDR' as "TableName",
	"x"."DocEntry" as "Id",
	0 as "DraftId",
	"x"."DocNum" as "DocumentNumber",
	"x"."CardCode" as "CustomerNumber",
	"x"."CardName" as "CustomerName",
	'ReadOnly' as "EditState",
	'Signed' as "OrderState",
	"x"."CANCELED",
	"x"."DocStatus",
	"x"."DocDate" as "OrderDateTime",
	"x"."U_Dso_MinDueDate" as "DeliveryStart",
	"x"."U_Dso_MaxDueDate" as "DeliveryEnd",
	"x"."DocDueDate" as "OrderDelivery",
	"x"."Comments" as "Remark",
	"x"."U_Dso_SalesRep" as "SalesRepCode",
	"x"."U_Dso_SalesOption" as "SalesOption",
	"x"."U_Dso_Notification" as "Notification",
	"x"."CreateTS" as "CreateTimeStamp",
	("x"."VatSum" - "x"."DocTotal") as "TotalPricePriceExclVat",
	"x"."DocTotal" as "TotalPricePriceInclVat",
	"x"."VatSum" as "TotalPriceVatPrice",
	"x"."UserSign",
	"x"."AtcEntry" as "AttachId",
	(select STRING_AGG("c"."Address",', ') from "CRD1" "c" WHERE "c"."CardCode" = "x"."CardCode" GROUP BY "CardCode") as "Address"
FROM
	"ORDR" "x"

I will use this query in the semantic layer of the service layer.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • May 15 at 12:18 AM

    "Is there a possibility to convert a sql query with STRING_AGG to a graphical calculation view?"

    Nope, STRING_AGG is not one of the available functions in graphical calc. views.

    You could, however, put your current statement into a SQL view and use it as a data source for your calc view.

    Add comment
    10|10000 characters needed characters exceeded