Tip for using views

[Fabio Lenzarini]

When developing a complex application, perhaps sometimes you need complex queries that, i think, you can't handle with Jam.py.

is this correct?

For example, in my previous ERP written with Visual Fox Pro or now with Dynamics AX, I often have queries with many tables... join, order by..

SELECT Rmovco.num_reg, Rmovco.riga_mov, Rmovco.conto, Rmovco.dareavere,;

  Rmovco.c_caucont, Rmovco.importo, Rmovco.note, Rmovco.contro,;

  Tmovco.data_reg, Tmovco.data_doc, Tmovco.num_doc, Rmovco.doc_coll,;

  Pdc.patr_eco,;

  IIF(Rmovco.dareavere="D",importo,pnmaxvalue)-IIF(Rmovco.dareavere="A",importo,pnmaxvalue) AS differenza,;

  Tmovco.data_comp, Rmovco.checked;

 FROM FORCE ;

     data!rmovco ;

    INNER JOIN data!tmovco ;

   ON  Rmovco.num_reg = Tmovco.num_reg ;

    INNER JOIN data!pdc ;

   ON  Rmovco.conto = Pdc.conto ;

    LEFT OUTER JOIN data!a_view_select_caucont ;

   ON  Rmovco.c_caucont = A_view_select_caucont.c_caucont;

 WHERE (  Rmovco.conto BETWEEN ?r_conto_f AND ?r_conto_t;

   AND  Rmovco.contro BETWEEN ?r_contro_f AND ?r_contro_t );

   AND  (  Tmovco.data_reg BETWEEN ?r_data_reg_f AND ?r_data_reg_t;

   AND  (  A_view_select_caucont.stampa = ( .T. );

   AND  (  Tmovco.apechiu <> ( ?lsApeChiu );

   AND  Tmovco.data_comp BETWEEN ?r_data_comp_f AND ?r_data_comp_t ) ) );

 ORDER BY Rmovco.conto, Rmovco.dareavere DESC, Rmovco.num_reg,;

  Rmovco.riga_mov

Reproducing this with Open on individual Item definitely creates much more data traffic than a simple DB query.

So I'm doing some testing...

I have created a view on SQLite:

CREATE VIEW SEM_VIEW_SALES_ORDER_DELAY

AS 

SELECT

sem_sales_order.id,

sem_sales_order.deleted,

sem_sales_order.sales_id,

sem_sales_order.partner,

sem_sales_order.payment_type,

sem_sales_order_line.item_code,

sem_sales_order_line.item_description,

sem_invent_table.name

FROM

sem_sales_order

INNER JOIN sem_sales_order_line ON sem_sales_order_line.sales_id = sem_sales_order.id

INNER JOIN sem_invent_table ON sem_invent_table.id = sem_sales_order_line.item_code

WHERE sem_sales_order.deleted = 0 AND sem_sales_order_line.deleted = 0 AND sem_invent_table.deleted = 0

ORDER BY sem_sales_order.sales_id

Then I disconnected Jam.py from the DB and manually created an Item with all the fields

I reconnected the DB to Jam.py..

and all work fine.

Now

the problem is bringing the changes back from the development environment to the test or production environment.

jam.py doesn't handle views

when i import the project, a table is created.. not a view.

How can i get around this problem?

thanks for any suggestion

Fabio

[Fabio Lenzarini]

I was thinking of having jam.py "skip" handling objects with "_view_" in the name....

what do you think?

[Drazen D. Babic]

To be fair, views are long in due. It is interesting that u managed to connect to a view tho. So, well done!

This is a problem to recreate it, so you are out of options, I’m afraid, unless support for views is added to Jam.

Same with stored procedures (SP). However, this is the question of having Python as your primary SP resource or not.

Which is a valid question for the views as well (they are great till need to move them to some other DB provider).