Google BigQuery Error: Resources exceeded during query execution

Question

I get a "resources exceeded" error when I run the following simple query in BigQuery, most of the answers to this error refer to the EACH clause , but I am not using any such clause in my query.

SELECT
  XXX_lnaourj_ID,
  XXX_DR_Tma_LC,
  XXX_CR_Tma_LC,
  XXX_Amount_LC,
  XXX_TLAffect_LC,
  XXX_Entry_Desc,
  XXX_DocType,
  XXX_TCode,
  XXX_Company,
  XXX_Posted_By,
  XXX_Parked_By,
  XXX_Accounting_Date,
  XXX_Creation_Date
FROM
  [acl_data.XXX_JE_All_Formatted]
WHERE
  XXX_lnaourj_ID IN (
  SELECT
    XXX_lnaourj_ID
  FROM
    [acl_data.XXX_JE_All_Formatted]
  WHERE
    XXX_Creation_Date - XXX_Accounting_Date > 30 )
ORDER BY
  XXX_lnaourj_ID ASC;

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

Remove ORDER BY and it should work

I think BigQuery engine translates your query using JOIN (and that's why you see it is referenced), so another option for you would be to rewrite your query using JOIN explicitly and try it with and/or without EACH clause

see https://stackoverflow.com/a/24191584/5221944 for more details from bigquery teammember

Edit

I haven't realized it is the same table! Why not to use something like below?

SELECT
  XXX_lnaourj_ID,
  XXX_DR_Tma_LC,
  XXX_CR_Tma_LC,
  XXX_Amount_LC,
  XXX_TLAffect_LC,
  XXX_Entry_Desc,
  XXX_DocType,
  XXX_TCode,
  XXX_Company,
  XXX_Posted_By,
  XXX_Parked_By,
  XXX_Accounting_Date,
  XXX_Creation_Date
FROM [acl_data.XXX_JE_All_Formatted]
WHERE XXX_Creation_Date - XXX_Accounting_Date > 30

edited May 23 '17 at 12:09

Community

1
1

answered Mar 23 '16 at 21:15

Mikhail Berlyant

165,386
8
154
230

But what if I want to sort it ? The link you gave me does not address the query at hand. Thanks. – user1965449 Mar 30 '16 at 16:02
extra link is just for some extra reading :o) - i actually gave you answer with few options - do they worked for you? – Mikhail Berlyant Mar 30 '16 at 16:11
I rewrote it using join on the same table but then I get the same error even if do not use the ORDER BY clause. – user1965449 Mar 30 '16 at 16:32
in this case - i recommend you to update your question with this info + re-written query - so you will have chance for better answer or/and i follow up on my answer – Mikhail Berlyant Mar 30 '16 at 16:46
Because the same XXX_lnaourj_ID could be present in both the cases i.e. where XXX_Creation_Date - XXX_Accounting_Date > 30 and XXX_Creation_Date - XXX_Accounting_Date < 30 , however if the difference is > 30 then I need all the rows for the XXX_lnaourj_ID . Hope I am clear. Thank you. – user1965449 Mar 31 '16 at 06:38
agree. that's exactly what is in my comment to your twin question – Mikhail Berlyant Mar 31 '16 at 07:03
SELECT JE_All1.xxx_Journal_ID, JE_All1.xxx_DR_Amt_LC, JE_All1.xxx_CR_Amt_LC, JE_All1.xxx_Amount_LC, JE_All1.xxx_PLImpact_LC, JE_All1.xxx_Entry_Desc, JE_All1.xxx_DocType, JE_All1.xxx_TCode, JE_All1.xxx_Company, JE_All1.xxx_Posted_By, JE_All1.xxx_Parked_By, JE_All1.xxx_Accounting_Date, JE_All1.xxx_Creation_Date FROM [acl_data.xxx_JE_All_Formatted] as JE_All1 JOIN [acl_data.xxx_JE_All_Formatted] as JE_All2 ON JE_All1.xxx_Journal_ID = JE_All2.xxx_Journal_ID WHERE JE_All2.xxx_Creation_Date - JE_All2.xxx_Accounting_Date > 30 order by JE_All1.xxx_Journal_ID ASC – user1965449 Mar 31 '16 at 19:08
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/107887/discussion-between-user1965449-and-mikhail-berlyant). – user1965449 Mar 31 '16 at 19:09

Google BigQuery Error: Resources exceeded during query execution

1 Answers1

Linked