Query not populated

[piopio1]

Hello,

I have a problem in running a query. The following is the code:

a:='Select Array_To_String(Array(( Select Query1.customer_code From (Select tbl_invoices.customer_code, Sum(tbl_invoices.balance_amount * tbl_currencies.exchange_rate) As AROverdue From tbl_invoices Inner Join tbl_currencies On tbl_invoices.currency = tbl_currencies.id Where tbl_invoices.update_date = Current_Date And tbl_invoices.due_date <= Current_Date Group By tbl_invoices.customer_code)Query1 Left Join tbl_customer_master_data On tbl_customer_master_data.customer_code = Query1.customer_code , (Select 100000 as fixedvalue)Query2 Where Query1.AROverdue>Query2.fixedvalue And (tbl_customer_master_data.collection_category = 787 And tbl_customer_master_data.collection_step = 1) Group By Query1.customer_code , tbl_customer_master_data.collection_category,tbl_customer_master_data.collection_step )),''~'') AS customerids'
dummy2:=TPgQuery.Create(nil);
dummy2.Connection:=DM.PgConnection1;
dummy2.Close;
dummy2.SQL.Clear;
dummy2.SQL.Text:=a;
dummy2.Open;
b:=dummy2.FieldByName('customerids').AsString;

after the "Open" instruction the query shows dummy2.recordcount=1 but there is no data in the field "customerids" nor in the variable "b".
If I run the same query in PGAdmin III I have the query correctly populated with the relevant data.

Am I doing anything incorrectly ?


Many thanks
Pio Pio

[DemetrionQ]

Hello.

We couldn't determine the problem when using SQL queries like

Code: Select all

Select Array_To_String(Array[ ... ], '~');

Please provide the following information:
  - the server version;
  - the exact version of PgDAC, you can learn it from the About sheet of TPgConnection Editor;
  - the script for creating the tables.
  - the script for filling in the tables with the test data.

[piopio1]

Hello.

We couldn't determine the problem when using SQL queries like

Code: Select all

Select Array_To_String(Array[ ... ], '~');

Please provide the following information:
  - the server version;
  - the exact version of PgDAC, you can learn it from the About sheet of TPgConnection Editor;
  - the script for creating the tables.
  - the script for filling in the tables with the test data.

Hello DemetrionQ,
I have just sent an email containing all the information you have requested.

Many thanks
Pio Pio

[DemetrionQ]

Hello.

I have received your letter and performed a test. I couldn't reproduce the issue.
I used the following code:

Code: Select all

var
  a: string;
  dummy2: TPgQuery;
begin
  a :=  'Select Array_To_String(Array(( Select Query1.customer_code From (Select tbl_invoices.customer_code, Sum(tbl_invoices.balance_amount * tbl_currencies.exchange_rate) As AROverdue From tbl_invoices Inner Join tbl_currencies '
        +
        'On tbl_invoices.currency = tbl_currencies.id Where tbl_invoices.update_date = Current_Date And tbl_invoices.due_date <= Current_Date Group By tbl_invoices.customer_code)Query1 Left Join tbl_customer_master_data '
        +
        'On tbl_customer_master_data.customer_code = Query1.customer_code , (Select 100000 as fixedvalue)Query2 Where Query1.AROverdue>Query2.fixedvalue And (tbl_customer_master_data.collection_category = 787 And '
        +
        'tbl_customer_master_data.collection_step = 1) Group By Query1.customer_code , tbl_customer_master_data.collection_category,tbl_customer_master_data.collection_step )),''~'') AS customerids';

  dummy2 := TPgQuery.Create(nil);
  try
    dummy2.Connection := DM.PgConnection1;
    dummy2.SQL.Text := a;
    dummy2.Open;
    ShowMessage(dummy2.FieldByName('customerids').AsString);
  finally
    dummy2.Free;
  end;
end;

and got the following as an execution result:

Code: Select all

694~1184~189~902~525

Please check the code. If it shows an empty string, make sure that PgAdmin and your application connect to the same database.

[piopio1]

I found out the issue: right before running the query I ran another update query but not committed the transaction.

Thank you for your help, your test help me to find the solution
Pio Pio

[DemetrionQ]

Glad to see that you solved the issue. If any other questions come up, please contact us.