[Solved] Help with query

Discuss the database features
Post Reply
joaofmateus
Posts: 33
Joined: Fri Mar 20, 2009 12:15 pm
Location: Lisbon - Portugal

[Solved] Help with query

Post 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
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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Help with query

Post 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:
  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
Posts: 33
Joined: Fri Mar 20, 2009 12:15 pm
Location: Lisbon - Portugal

Re: Help with query

Post 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.
OOo 4.1.5 on MS Windows 10
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Help with query

Post 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
joaofmateus
Posts: 33
Joined: Fri Mar 20, 2009 12:15 pm
Location: Lisbon - Portugal

Re: Help with query

Post 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 ) )
OOo 4.1.5 on MS Windows 10
Post Reply