Hello to all,

My task is to find earnings for each client according to 3 rules:

  1. Find a customer by custom_id , if custom_id not with a number, then you need to search for another type custom_id .

  2. Find a client by ae_custom_id but if it’s on the same line as custom_id then they have the same amount.

  3. Find client by re_custom_id but under re_custom_id should be only one client record in the database.

I am trying to fulfill the following query and I already have a problem and cannot understand why.

 select sum(case when exists (select * from test_table where custom_id is not null) exists (select * from test_table join ee_client_id on ee_client_id.custom_id = case when test_table.custom_id ~ '(^[0-9]+)$' then case when substr(test_table.custom_id,1,3)::integer = 901 then substr(test_table.custom_id,1,10)::bigint when substr(test_table.custom_id,1,3)::integer between 100 and 106 then substr(test_table.custom_id,1,3)::bigint end end) then total_sum else 0 end) from test_table
  • Here it is case when exists (select * from test_table where custom_id is not null) very strange. it will also work if there is at least one entry in the table with a non-empty custom_id. Not seen here cut by customers. Could you on some sqlfiddle.com to make a test label with the data and say what you want to get. In the current form to understand the meaning of the question is unrealistic. Just like how your request is related to it. what the values ​​901, 100-106 - they are not found in the text description of the question - Mike
  • I created a test table for excel csv file type. Link to file. cloud.mail.ru/public/KHxt/SdmSaSxdX I'm sorry that I could not create a table in sqlfiddle. - dron4ik pm
  • Great, like this regex101.com/r/zN1lH0/3 it can be loaded into a test table. I just need the normal structure of the table, because it is absolutely not clear how these id1, id2, id3 fields correspond to your query. need to make the query work on the table being created - Mike
  • And you from the ID in the request take from 1 to 3 characters. In the example of data in these positions, the letters are everywhere ... - Mike
  • I will try to explain myself in words, since I cannot create a test table ... :( I have a table. I need to find a customer for this money in this column. This client can be found in 4 ways. 1. Find custom_id it can be with numbers and maybe without, it can also be null. In my request, I check first if it is with numbers if yes then let it find this client in the database. After I found the client by custom_id he has his real passport_id Then I check if this passport_id is in the report for which I am searching .. - dron4ik

0