Page 1 of 1
[Solved] Help with query
Posted: Fri Aug 25, 2017 1:53 pm
by joaofmateus
I have created the following query to select the movements, in a certain period, of a certain account (the selection is made through the field cod_conta (text [VARCHAR]).
SELECT "COD_CONTA" AS "Account", "DESC_CONTA" AS "DESC", "DESC_MOV", SUM ("DEBIT") AS "DEBIT", SUM ("CREDIT") AS "CREDIT" WHERE "DATA_MOV"> =: DATAINICIAL AND "DATA_MOV" <=: DATAFINAL GROUP BY "COD_CONTA", "DESC_CONTA", "DESC_MOV" HAVING (("COD_CONTA" =: INDIQUECONTA_A_CONSULTAR))
At first everything seemed fine, I now verify that there are certain accounts that do not appear in the selection (examples 61 and 69).
Where will I go wrong?
Thank you, from now, to whom I can help
Re: Help with query
Posted: Fri Aug 25, 2017 2:56 pm
by Sliderule
Please try the following Query
Code: Select all
SELECT
"COD_CONTA" AS "Account",
"DESC_CONTA" AS "DESC",
"DESC_MOV",
SUM( COALESCE("DEBIT",0) ) AS "DEBIT",
SUM( COALESCE("CREDIT",0) ) AS "CREDIT"
FROM "MY_TABLE_NAME" -- Change this to the correct table name
WHERE "DATA_MOV" BETWEEN :DATAINICIAL AND :DATAFINAL
AND "COD_CONTA" = :INDIQUECONTA_A_CONSULTAR
GROUP BY "COD_CONTA",
"DESC_CONTA",
"DESC_MOV"
Explanation: - Your Query, as written above, does not include the table name, so, please add it by changing "MY_TABLE_NAME" above to your defined table name.
- I changed the WHERE clause to use a BETWEEN
- I changed the HAVING part to include it in the WHERE clause
- Each time you use a colon : as a parameter prompt, it should NOT include a space immediately afterwards
- I changed both the "DEBIT" and "CREDIT" to use a COALESCE in the event any of the values might be NULL ( undefined )
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.
Re: Help with query
Posted: Fri Aug 25, 2017 4:01 pm
by joaofmateus
I tried, with the necessary changes, the example you gave me and the result was exactly the same.
SELECT
"COD_CONTA" AS "Account",
"DESC_CONTA" AS "DESC",
"DESC_MOV",
SUM( COALESCE("DEBITO",0) ) AS "DEBIT",
SUM( COALESCE("CREDITO",0) ) AS "CREDIT"
FROM "qMOVIMENTOS_CONTA" -- Change this to the correct table name
WHERE "DATA_MOV" BETWEEN :DATAINICIAL AND :DATAFINAL
AND "COD_CONTA" = :INDIQUECONTA_A_CONSULTAR
GROUP BY "COD_CONTA",
"DESC_CONTA",
"DESC_MOV"
With account 66 appear results, with the 69 comes always blank.
In the respective table there are records of both accounts.
Anyway thanks for trying to help.
I will not close the topic yet, as another suggestion may come up.
Re: Help with query
Posted: Fri Aug 25, 2017 4:24 pm
by Sliderule
Please tell me, ¿ Is "qMOVIMENTOS_CONTA" a Table, a View, OR, a Query ?
If it is a Query, can you please show us the Query ( the Select statement ) ?
Sliderule
Re: Help with query
Posted: Fri Aug 25, 2017 6:34 pm
by joaofmateus
"QMOVIMENTOS_CONTA" is a query on the table "tMOVIMENTOS_CONTA" that, after breaking a lot of stone, I discovered that it was damaged (database compaction or another problem). I had to rebuild everything (including the table) and now everything is working. Thanks again for your time sliderule
At the moment things went like this:
qMVIMENTOS_CONTA
SELECT "tMOVIMENTOS_CONTA"."COD_CONTA", "tMOVIMENTOS_CONTA"."DATA_MOV", "tMOVIMENTOS_CONTA"."DESC_MOV", "tMOVIMENTOS_CONTA"."DEBITO", "tMOVIMENTOS_CONTA"."CREDITO", "tCONTAS"."DESC_CONTA" FROM "tCONTAS" AS "tCONTAS", "tMOVIMENTOS_CONTA" AS "tMOVIMENTOS_CONTA" WHERE "tCONTAS"."COD_CONTA" = "tMOVIMENTOS_CONTA"."COD_CONTA" AND "tMOVIMENTOS_CONTA"."DATA_MOV" >= :DATAINICIAL AND "tMOVIMENTOS_CONTA"."DATA_MOV" <= :DATAFINAL
qMOVIMENTOS_CONTA_REPORT_RUBRICA (The final)
SELECT "COD_CONTA" AS "Conta", "DESC_CONTA" AS "DESC", "DESC_MOV", SUM( "DEBITO" ) AS "DEBITO", SUM( "CREDITO" ) AS "CREDITO" FROM "qMOVIMENTOS_CONTA" AS "qMOVIMENTOS_CONTA" GROUP BY "COD_CONTA", "DESC_CONTA", "DESC_MOV" HAVING ( ( "COD_CONTA" = :INDIQUECONTA_A_CONSULTAR ) )