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
[Solved] Help with query
-
- Posts: 33
- Joined: Fri Mar 20, 2009 12:15 pm
- Location: Lisbon - Portugal
[Solved] Help with query
Last edited by joaofmateus on Fri Aug 25, 2017 6:35 pm, edited 1 time in total.
OOo 4.1.5 on MS Windows 10
Re: Help with query
Please try the following Query
Explanation:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
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"
- 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 )
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 33
- Joined: Fri Mar 20, 2009 12:15 pm
- Location: Lisbon - Portugal
Re: Help with query
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.
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.
OOo 4.1.5 on MS Windows 10
Re: Help with query
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
If it is a Query, can you please show us the Query ( the Select statement ) ?
Sliderule
-
- Posts: 33
- Joined: Fri Mar 20, 2009 12:15 pm
- Location: Lisbon - Portugal
Re: Help with query
"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 ) )
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 ) )
OOo 4.1.5 on MS Windows 10