0

I have a query like this in Postgres:

select sum("ATD_AMOUNT") AS CREDIT_SUM, 0 AS DEBIT_SUM,"ATD_ACCOUNT_MST_ID","AAD_OPEN_AMOUNT","AAM_ACCOUNT_NAME", "AAM_ACCOUNT_CODE" ,"AAD_YEAR_ID" 
from db_accounts."ACC_TRANSACTION_DET" 
left outer join db_accounts."ACC_TRANSACTION_MST" ON "ATD_TRANSACTION_MST_ID"=    "ATM_TRANSACTION_MST_ID"
left outer join db_accounts."ACC_ACCOUNT_MST" ON "ATD_ACCOUNT_MST_ID"="AAM_ACCOUNT_MST_ID" 
left outer join db_accounts."ACC_ACCOUNT_DET" on "AAM_ACCOUNT_MST_ID"  = "AAD_ACCOUNT_MST_ID" and "AAD_YEAR_ID"=(select "AAY_YEAR_ID" from db_accounts."ACC_ACCOUNT_YEAR" where "AAY_IS_CURRENT_YEAR"=true)
where "ATM_TRANSACTION_DATE"<= $1 and "ATM_TRANSACTION_DATE">=(select "AAY_START_DATE" from db_accounts."ACC_ACCOUNT_YEAR" where "AAY_IS_CURRENT_YEAR"=true)
and "ATM_ON_REVERSE_PARENT_TRANSACTION_ID" is null and "ATM_IS_CANCELLED"=false
    and  "AAM_DEL_FLAG" =false
AND "ATD_CREDIT_DEBIT_TRANSACTION" = 'CREDIT'
GROUP BY "ATD_ACCOUNT_MST_ID","AAD_OPEN_AMOUNT","AAM_ACCOUNT_NAME", "AAM_ACCOUNT_CODE","AAD_YEAR_ID

Here the db_accounts."ACC_TRANSACTION_DET" contains some 50 lakh records also db_accounts."ACC_TRANSACTION_MST" has 60000 records

Now my problem is when i run this query with same database in my system i get a result but when i try in other system it does not work?

Also what i have noticed is if any one of the below conditions are removed it displays a result

"ATM_TRANSACTION_DATE"<= $1 //field in db_accounts."ACC_TRANSACTION_MST" table

"ATM_TRANSACTION_DATE">=(select "AAY_START_DATE" from db_accounts."ACC_ACCOUNT_YEAR" where "AAY_IS_CURRENT_YEAR"=true) //field in db_accounts."ACC_TRANSACTION_MST" table

"ATD_CREDIT_DEBIT_TRANSACTION" = 'CREDIT' //field in db_accounts."ACC_TRANSACTION_DET" table
Improve this question
4
  • when you say "same database" do you mean both computers query the same database server, or did you copy over the database contents to a PostgreSQL instance running on your computer? Commented May 25, 2011 at 7:08
  • No its two different servers i took a back up from the other system and restored it in mine.It works fine in my system.Could this be because there is lot of data in the tables? Commented May 25, 2011 at 7:22
  • 1
    what does explain analyze have to say about the query? what indexes are available? are you sure you need the left joins, and that inner joins couldn't be used instead? Commented May 25, 2011 at 7:57
  • There are no indexes on the any tables.Only primary keys for each of them and a foreign reference to the DET table Commented May 25, 2011 at 10:07

1 Answer 1

Reset to default
1

I have reformatted your query with www.prettysql.net:

SELECT
 sum("ATD_AMOUNT") AS CREDIT_SUM,
 0 AS DEBIT_SUM,
 "ATD_ACCOUNT_MST_ID",
 "AAD_OPEN_AMOUNT",
 "AAM_ACCOUNT_NAME",
 "AAM_ACCOUNT_CODE",
 "AAD_YEAR_ID"
FROM
 db_accounts."ACC_TRANSACTION_DET" LEFT OUTER JOIN db_accounts."ACC_TRANSACTION_MST"
 ON
   "ATD_TRANSACTION_MST_ID"=
   "ATM_TRANSACTION_MST_ID" LEFT OUTER JOIN db_accounts."ACC_ACCOUNT_MST" ON "ATD_ACCOUNT_MST_ID" = "AAM_ACCOUNT_MST_ID" LEFT OUTER JOIN db_accounts."ACC_ACCOUNT_DET" ON
    "AAM_ACCOUNT_MST_ID" = "AAD_ACCOUNT_MST_ID"
  AND
   "AAD_YEAR_ID"=
   (
    SELECT "AAY_YEAR_ID"
    FROM db_accounts."ACC_ACCOUNT_YEAR"
    WHERE "AAY_IS_CURRENT_YEAR"=true
   )
WHERE
  "ATM_TRANSACTION_DATE"<=$1
 AND
  "ATM_TRANSACTION_DATE">=
  (
   SELECT "AAY_START_DATE"
   FROM db_accounts."ACC_ACCOUNT_YEAR"
   WHERE "AAY_IS_CURRENT_YEAR"=true
  )
 AND
  "ATM_ON_REVERSE_PARENT_TRANSACTION_ID" is null
 AND
  "ATM_IS_CANCELLED"=false
 AND
  "AAM_DEL_FLAG"=false
 AND
  "ATD_CREDIT_DEBIT_TRANSACTION"='CREDIT'
GROUP BY "ATD_ACCOUNT_MST_ID","AAD_OPEN_AMOUNT","AAM_ACCOUNT_NAME","AAM_ACCOUNT_CODE","AAD_YEAR_ID

It looks like you have a where clause in your query which contains $1, the pattern used in Postgresql for parameter passing.

Try replacing it with something like '2011-05-25'::date, and see if it helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the reformatting..... Yes this part of code is taken from a function where parameters are passed.I found out that the problem occured in this part.Instead of $1 i passed '2011-05-01 00:00:00' which is the timestamp format in the table
If you replace $1 with a specific date, do you get the same results on both database servers?
As i have explained earlier this query runs fine in my system and gives the expected result but just keeps on executing in the other system and doesn't give a result (Same data in both system's and '2011-05-01 00:00:00' given instead of $1)
OK, then you should check out the DateStyle parameter configured in your Postgresql engines: postgresql.org/docs/8.2/static/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.