Stack Overflow
Stack Overflow

Questions tagged [lateral]

66 questions
0
votes
0 answers

Understanding LATERAL in Postgresql

Would like to know what is LATERAL and why LATERAL in SQL? Have few question to understand more on LATERAL. I searched in google and not getting clear understanding of LATERAL. Why Lateral Advantage of Lateral Lateral View Vs Lateral Join. What…
Learn Hadoop
  • 2,760
  • 8
  • 28
  • 60
0
votes
3 answers

Why LATERAL not works with values?

It not make sense, a literal is not a valid column? SELECT x, y FROM (select 1 as x) t, LATERAL CAST(2 AS FLOAT) AS y; -- fine SELECT x, y FROM (select 1 as x) t, LATERAL 2.0 AS y; -- SYNNTAX ERROR! Same if you use CASE clause or x+1 expression or…
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
0
votes
1 answer

Postgres - How to use a LATERAL SELECT CASE to return a unique value instead of a list?

Considering the following table signatures referencing the signature date of a document by 2 persons id p1_signed_at p2_signed_at 1 NULL NULL 2 01/01/2022 NULL 3 NULL 07/08/2022 4 03/04/2022 04/04/2022 I want to identify the next…
EricD
  • 587
  • 6
  • 22
0
votes
1 answer

Query an array element in an JSONB Object

I have a jsonb column called data in a table called reports. Here is what report.id = 1 looks like [ { "Product": [ { "productIDs": [ "ABC1", "ABC2" ], …
J Developer
  • 5
  • 2
0
votes
1 answer

Lateral Flatten , Outer on XML file does not work

I have the below xml that i loaded in to a table with variant datatype …
SKandu
  • 1
0
votes
2 answers

Is there any way to optimize the lateral flatten json query in Snowflake? My query is taking too much time to execute

I have json with nested arrays data vary for different jsons. I am putting my json parsing code and sample json file here. Query only got slow when there are many objects for rows tags as you can see below for this json query executes within a…
HAMZA Usman
  • 3
  • 5
0
votes
1 answer

Syntax Error: Lateral Join with Delete (PostgreSQL)

PostgreSQL 11.1 AFAIK, this is correct and should run. It Fails with syntax error on Delete. What am I missing? Thanks for any help. ERROR: syntax error at or near "DELETE" LINE 41: DELETE FROM d WITH _in (tservice, patient_recid,…
Alan Wayne
  • 5,122
  • 10
  • 52
  • 95
0
votes
0 answers

Function lateral flatten in snowflake

I need to un-parse JSON with lateral flatten. But I would like to have 2 values - one for club and one for free delivery, for club I am ok, but I can not get also free delivery This is code I have select LPAD(PURCHASE_ID,10, '0')as PURCHASE_ID,…
saska
  • 73
  • 1
  • 9
0
votes
1 answer

postgresql What does afew mean?

LINKs: https://www.2ndquadrant.com/wp-content/uploads/2019/05/sumtest.sql_.txt https://www.2ndquadrant.com/en/blog/join-lateral/ CREATE TABLE co_books ( dd numeric references books(dd), pname text references persons(pname), checked_out…
Mark
  • 19
  • 6
0
votes
1 answer

SQLAlchemy ORM Lateral Join using Subquery

I Want to convert an SQL query to SQLalcheny. Here is my query. **SELECT * FROM ( -- Get the first time each user viewed the homepage. SELECT pr.id, pr.name, pr.sku, ca.name as "Catergory Nmae", su.name as "Supplier Name", br.name as…
Justin aruja
  • 11
  • 2
0
votes
1 answer

Snowflake lateral flatten with changing keys in json

I have a table in Snowflake with similar structure as below, ---------------------------------------- Name | Number ---------------------------------------- Dim_1 | {'Table_1': 100} Dim_1 | {'Table_1': 101} Dim_2 | {'Table_2': 200, 'Table_3':…
Akshay Malik
  • 11
0
votes
2 answers

Alternative to JSON Flattening via Target Table in Snowflake

Per snowflake: https://docs.snowflake.net/manuals/user-guide/json-basics-tutorial-copy-into.html I created a target table (Testing_JSON), that is a single Variant column that contains an uploaded JSON file. My Question is How can I cut out creating…
0004
  • 1,156
  • 1
  • 14
  • 49
0
votes
1 answer

LATERAL VIEW and CASE Statement in KSQL

is there a LATERAL VIEW Statement in KSQL i cant finde anything in the docs. Or is there any alternative Comand to get the same result on column? When I use the Statement I get following error line 1:64: mismatched input 'view' expecting ';' Caused…
Vedad
  • 223
  • 4
  • 15
0
votes
1 answer

PostGresql: Copy data from a random row of another table

I have two tables, stuff and nonsense. create table stuff( id serial primary key, details varchar, data varchar, more varchar ); create table nonsense ( id serial primary key, data varchar, more varchar ); insert into…
Manngo
  • 14,066
  • 10
  • 88
  • 110
0
votes
0 answers

Select top-2 products in each of the top-5 groups as of revenue

I have a table called bills with the following columns: id, product – product id, product_group – product_group_id, Revenue – revenue in some currency I would like to get top-2 products from top-5 product groups as of their revenue. Any help is…
Andrey
  • 1
  • 2
1 2 3
4
5