Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

Why openerp report query result vary from sql query result? [Closed]

By
priyankahdp
on 10/2/13, 9:29 AM 3,670 views

The Question has been closed

by
priyankahdp
on 10/03/2013 02:11:47

Here is my code in python file

class sign_in_out_model_class(osv.osv):
    _name = "sign.in.out"
    _description = "Sign In / Out Report" 
    _auto = False
    _columns = {
        'name': fields.char('Emp No', size=128, readonly=True,select=True),
        'reldate':fields.char('Date', readonly=True,select=True),
        'sign_in': fields.char('Sign In', readonly=True,select=True),
        'sign_out': fields.char('Sign Out', readonly=True,select=True),
    }
    _order = 'sign_in asc'

    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'sign_in_out')
        cr.execute("""CREATE OR REPLACE VIEW sign_in_out AS (
                        select max(dup_id) as id,name,
to_char(min(date),'YY/MM/DD') as reldate,
to_char(min(date),'HH12:MIam') as sign_in,
to_char(max(date),'HH12:MIam') as sign_out 
from text_based_attendance 
group by date(date),name)""")

sign_in_out_model_class()

its gives below output

result in openerp

but when i run that query in pgAdmin its gives below result

pgAdmin result

need to know what is the reason for this difference.? any issue with my coding.?

1

Grover Menacho

--Grover Menacho--
2523
| 5 7 8
La Paz, Bolivia
--Grover Menacho--

Systems Engineer + MBA

Grover Menacho
On 10/2/13, 1:23 PM

This is a common error. As you can see on your pgAdmin you have loads of ids repeated. OpenERP works basically with ids to perform operations so if you have repeated ids it's going to filter them and display only a row of each id. You have to change and display a UNIQUE id.

1

priyankahdp

--priyankahdp--
1089
| 5 5 6
Colombo, Sri Lanka
--priyankahdp--
Java Developer ORACLE Python J2EE Spring Hibernate Struts & Jquery............. :-)
priyankahdp
On 10/3/13, 1:52 AM

thanks dear Grover, I update my query as this. now its ok. wats ur feedback about my solution.? :-)

cr.execute("""CREATE OR REPLACE VIEW sign_in_out AS (
                        select row_number() OVER () as id,name,
to_char(min(date),'YY/MM/DD') as reldate,
to_char(min(date),'HH12:MIam') as sign_in,
to_char(max(date),'HH12:MIam') as sign_out 
from text_based_attendance 
group by date(date),name)""")

Perfect, that's the best way to do it.

GH Mediacloud, Grover Menacho
on 10/3/13, 2:54 AM

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

0 follower(s)

Stats

Asked: 10/2/13, 9:29 AM
Seen: 3670 times
Last updated: 3/16/15, 8:10 AM