[Solved] Help with query

[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

[Sliderule]

Please try the following Query

Code: Select all   Expand viewCollapse view

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:

1. 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.
2. I changed the WHERE clause to use a BETWEEN
3. I changed the HAVING part to include it in the WHERE clause
4. Each time you use a colon : as a parameter prompt, it should NOT include a space immediately afterwards
5. 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.

[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 ATAINICIAL AND ATAFINAL
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.

[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

[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" >= ATAINICIAL AND "tMOVIMENTOS_CONTA"."DATA_MOV" <= ATAFINAL

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 ) )