Stack Overflow
Stack Overflow

Questions tagged [lateral]

66 questions
3
votes
1 answer

Postgresql - LEFT JOIN LATERAL is too slow than subquery

I have some difficulty using 'LEFT JOIN LATERAL' function with postgresql 9.5. In my table, there are three columns of 'ID', 'DATE', 'CODE'. One person (ID) have multiple rows as below. The number of ID is 362 and total row number is about…
JKim
  • 135
  • 2
  • 7
3
votes
1 answer

How to combine a CASE statement with a LATERAL JOIN in PostgreSQL?

I need to call a function several times for every select row, because function have several OUT parameters, all of which I need E.g. SELECT a, b, (SELECT out1 from func(a)), (SELECT out2 from func(a)) FROM table1 To call this function…
Juri Krainjukov
  • 732
  • 8
  • 27
2
votes
1 answer

Implementation of a Lateral contoller using non linear model predictive control in GEKKO

I am trying to implement a lateral controller for an autonomous vehicle defined by a lateral dynamic model.Well, my problem is that the CVs don't reach the desired reference or target point set by SP. I am using the following equations of motion and…
Ahmed Siam
  • 23
  • 3
2
votes
1 answer

How to get value of a key inside an jsonb array in postgres and JOIN

I have data that looks like inside a json table in postgres with a column called content has data similar to {schd:[{key1:val1, key2:val2}]} The way I extracted the key was jsonb_array_elements('content'->'schd')->'key1' However I cannot use…
user2510479
  • 1,528
  • 13
  • 17
2
votes
1 answer

Postgres Lateral Join Multiple Tables to Limit Results

I have a question regarding lateral joins in Postgres. My use case is I want to return a dataset that combines multiple tables but limits the number of publications and reviews returned. The simplified table schema is below Table…
KukicAdo
  • 43
  • 1
  • 4
2
votes
0 answers

Use cases for lateral that do not involve a set returning function

I was reading this post the other day: http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ I suspected some of the claims in the post may not have been accurate. This one in particular: "Without lateral joins, we would need to…
ajw0100
  • 378
  • 1
  • 8
2
votes
3 answers

Dynamically execute query using the output of another query

I have a function called generate_table, that takes 2 input parameters (rundate::date and branch::varchar) Now I am trying to work on a second function, using PLPGSQL, that will get a list of all branches and the newest date for each branch and pass…
Fulano da Silva Sauro
  • 51
  • 6
2
votes
1 answer

hive - LATERAL VIEW explode xpath

i have the following input XML: Watson 30 johnwatson@sh.com …
Roman Cwalina
  • 61
  • 2
  • 5
1
vote
1 answer

Not able to transform data in expected format in snowflake

I got data in rows for a column like this [ { "value": "A", "path": "nth-child(1)" }, { "value": "K", "path": "nth-child(2)" }, { "value": "C", "path": "nth-child(3)" } ] Need help ..... Want to get data like…
user19131919
  • 13
  • 3
1
vote
1 answer

Correct use of a LATERAL FROM

I am trying to use PostGIS to undertake a number of steps within an SQL statement to basically convert a raster to points, buffer, dissolve and then determine the overall boundary i.e. concave hull based on an attribute. The following is where I am…
Dean Howell
  • 13
  • 2
1
vote
2 answers

LATERAL VIEW explode funtion in hive

I am trying to export data from excel into a hive table, while doing so, i have a column 'ABC' which has values like '1,2,3'. I used the lateral view explode function but it does not does anything to my data. Following is my code snippet : CREATE…
Keerti Dobhal
  • 13
  • 3
1
vote
1 answer

Snowflake XML parse if element doesn't exists

I wrote the following XML query to parse the XML file into my Snowflake database: SELECT XMLGET( prodCstmsHdr.value, 'prodCd' ) FROM (SELECT XMLDOC FROM RAW.WE_BREXIT_TRADE_TERMS WHERE WE_BREXIT_TRADE_TERMS_UID = …
Walter
  • 11
  • 1
1
vote
1 answer

how to split the data in snowflake and put them in respective columns

EXAMPLE: id core primary secondary 101 4355|6755 4355|7866 102 8566|6755 8566 8566 ``````````````````````````````````````````````````````````````````````````````` I need to split the data into another…
sony
  • 41
  • 3
1
vote
1 answer

Lateral Flatten Snowflake from a Variant table

I have a variant table called raw_json, that houses multiple json files, which are unique to the ID but contain similar data points within each json. They live within the jsontext column. Here is a SS for context. I am trying to flatten each row of…
Bigmoose70
  • 453
  • 6
  • 15
1
vote
1 answer

Selecting events from the start of a cycle in Postgres 11.6

Using Postgres 11.6, I'm trying to write a query that filters out events based on their position in a sequence during grouping. We've got a lot of event data grouped in "cycles". Here's the table setup: Here's the table setup: CREATE TABLE IF NOT…
Morris de Oryx
  • 1,857
  • 10
  • 28
1
2
3 4 5