SQL Query a Data Manager Object

post/datamart.sqlquery

Processes and queries data from Price Analyzer or Price Optimizer. The queries can be aggregated, do various computations including statistical projections. Supports filtering.

Securitybasic or X-PriceFx-jwt
Request
query Parameters
timeout
string
Default: "60"

Set this parameter to override the default timeout (60 seconds) of the query. The maximum allowed timeout is 300 seconds. The default timeout value and the maximum timeout value can be configured in the backend. Setting higher timeout can be useful, for example, when a query takes long time (e.g., when processing large tables).

Example: timeout=20
Request Body schema: application/json

sources that SQL can use are query definitions. The sources become CTEs (Common Table Expression) in the final SQL. These are then used as a reference in the main query instead of referring to the actual tables directly. The request example compares the volume by month 2019 to 2020.

operationType
string
startRow
integer
endRow
integer
object
object
object
object
object
object
object
w0
string
sql
string
Responses
200

OK

Response Schema: application/json
object
node
string non-empty
startRow
number
Array of objects non-empty unique
Array (non-empty)
bandBy
string non-empty
bubbleSize
number
bubbleSize_n
number
groupBy
string non-empty
x
number
y
number
summary
string non-empty
endRow
number
totalRows
number
status
number
Request samples
application/json
{
  • "operationType": "fetch",
  • "startRow": 0,
  • "endRow": 10000,
  • "data": {
    • "sources": {
      • "s0": {
        • "query": {
          }
        },
      • "s1": {
        • "query": {
          }
        }
      },
    • "with": {
      • "w0": "SELECT Month FROM (VALUES ('M01'), ('M02'), ('M03'), ('M04'), ('M05'), ('M06'), ('M07'), ('M08'), ('M09'), ('M10'), ('M11'), ('M12')) AS t(Month)"
      },
    • "sql": "SELECT w0.Month \"Month\", SUM(s0.quantity) \"Volume 2019\", SUM(s1.quantity) \"Volume 2020\", 100 * (SUM(s1.quantity) / SUM(s0.quantity) - 1.0) \"VolumeΔ%\" FROM w0 LEFT OUTER JOIN s0 ON w0.Month = RIGHT(s0.Month,3) LEFT OUTER JOIN s1 ON w0.Month = RIGHT(s1.Month,3) GROUP BY w0.Month ORDER BY w0.Month"
    }
}
Response samples
application/json
{
  • "response": {
    • "node": "e2e-staging-node",
    • "startRow": 0,
    • "data": [
      • {
        • "bandBy": "CD-0004",
        • "bubbleSize": 548,
        • "bubbleSize_n": 0.292108362779741,
        • "groupBy": "Europe",
        • "x": 12075200,
        • "y": 220350.364963504,
        • "summary": "{\"projections\":{\"y\":{\"LRa\":239348.912312387,\"ERa\":237200.359912615,\"Sum\":15041879.5665948,\"Avg\":250697.992776579,\"Mean\":250697.992776579,\"Min\":93904.7619047619,\"Max\":353076.923076923,\"Std\":46309.6235068131,\"Q.05\":166714.083007813,\"Q.1\":196816.897460938,\"Q.2\":219184.366210938,\"Q.25\":226486.880371094,\"Q.3\":236223.565917969,\"Q.4\":243526.080078125,\"Q.5\":249915.779968262,\"Q.6\":258131.108398438,\"Q.7\":271519.051025391,\"Q.75\":280038.650878906,\"Q.8\":290262.170703125,\"Q.9\":311682.878906250,\"Q.95\":331156.250000000,\"Hist\":{\"118174.538085938\":1,\"154579.196777344\":2,\"178848.969238281\":1,\"193900.376464844\":2,\"199733.418457031\":1,\"205566.460449219\":2,\"211399.502441406\":1,\"217967.280517578\":3,\"225269.794677734\":3,\"232572.308837891\":2,\"238049.194458008\":2,\"241700.451538086\":4,\"245351.708618164\":2,\"248090.151428223\":3,\"249915.779968262\":2,\"254479.851318359\":5,\"261782.365478516\":1,\"267259.251098633\":3,\"270910.508178711\":3,\"276387.393798828\":2,\"283689.907958984\":2,\"294643.679199219\":5,\"309248.707519531\":3,\"323853.735839844\":2,\"342116.593750000\":3}},\"x\":{\"LRb\":0.000564991849158590,\"ERb\":2.44510211980881E-9,\"Sum\":1118727900.00000,\"Avg\":18645465.0000000,\"Mean\":18645465.0000000,\"Min\":1183200.00000000,\"Max\":41407200.0000000,\"Std\":12268723.3236501,\"Q.05\":2081450.00000000,\"Q.1\":3481156.25000000,\"Q.2\":5028200.00000000,\"Q.25\":6796250.00000000,\"Q.3\":8564300.00000000,\"Q.4\":12954533.3333333,\"Q.5\":16979450.0000000,\"Q.6\":22194221.8750000,\"Q.7\":25844716.6666667,\"Q.75\":28035013.5416667,\"Q.8\":30955409.3750000,\"Q.9\":36650181.2500000,\"Q.95\":39951862.5000000,\"Hist\":{\"1260425.00000000\":1,\"1414875.00000000\":1,\"2376125.00000000\":3,\"3702162.50000000\":4,\"4586187.50000000\":3,\"5912225.00000000\":3,\"7238262.50000000\":1,\"8122287.50000000\":2,\"9149450.00000000\":1,\"10942075.0000000\":4,\"13357025.0000000\":3,\"15168237.5000000\":2,\"16375712.5000000\":2,\"18491687.5000000\":2,\"21099073.4375000\":4,\"23289370.3125000\":4,\"25479667.1875000\":3,\"27669964.0625000\":3,\"30955409.3750000\":4,\"35336003.1250000\":5,\"38496525.0000000\":1,\"40436975.0000000\":4}}},\"Count\":60}"
        },
      • {
        • "bandBy": "CD-0008",
        • "bubbleSize": 355,
        • "bubbleSize_n": 0.178445229681979,
        • "groupBy": "Asia",
        • "x": 11279100,
        • "y": 296039.37007874
        },
      • {
        • "bandBy": "CD-0001",
        • "bubbleSize": 405,
        • "bubbleSize_n": 0.207891637220259,
        • "groupBy": "Europe",
        • "x": 10922500,
        • "y": 269691.358024691
        }
      ],
    • "endRow": 3,
    • "totalRows": 3,
    • "status": 0
    }
}