SQLExpress - Xb2.NET     ot4xb  
xfree.public
SQL
Thread Starter: Osvaldo Ramirez Started: 6/7/2008 3:41 PM UTC
Replies: 5
6/7/2008 3:41 PM UTC
SQL
Reply Quote

Osvaldo Ramirez

Buenos, dias amigos


Pienso que se puede hacer mas facil que recorrer cada registro y actualizar valores en otra tabla.

Bueno, hay va mi pregunta.

Tabla de articulos, tiene unas columnas de exitencias
Tabla de Compras tiene los datos de la compra
Tabla del detalle de las compras donde estan todos los articulos que se compraron.

Quiero actualizar cada articulo aumentado las existencias con la informacion que se capturo en el detalle de las compras.

Yo pense que en recorrer cada producto del detalle, y e ir a articulos y actualizarlo, pero pienso que es mas facil.

Alguna sugerencia?

-------------------------------------------------------------

Well guys

I need to know how to update a table ...

for exaple :

I have a table called articul, where are I have stock columns.
I have a master purchase order table.
I have a detail purchase order table

So how to change my inventory stock with the details table, with out going with each record.


------------------------------

Saludos
Osvaldo Ramirez
6/7/2008 7:29 PM UTC
Re: SQL
Reply Quote

Angel Pais

Jose did the same question 5 days ago.
Just change Items with Accounts and Quantity with Amount

=)

---------------------------------------------------------

Es la misma pregunta que hizo Jose hace 5 dias.
Solo cambia Cuentas por Articulo e Importes por Cantidades

Un balance es un balance no importa si estamos contando dinero o bolitas de vidrio.

=)
6/8/2008 4:51 PM UTC
Re: SQL
Reply Quote

Osvaldo Ramirez

Gracias Angel Pais

Pero lo que quiero es actualizar y no hacer un select:

Este es mi ejemplo

oStmt := oConnection:NewStatement()

// Actualizar la compra
oStmt:SQLString := "UPDATE movs SET mov_status = 'ACTUALIZADA' where mov_id = "+str(nMov)

oStmt:Execute()

 // Actualizar las existencias en la tabla de articul por cada producto en el detalle de sub_mov

oCursorLocal := SQLDataSet():new( "SELECT art_id,sm_cant,sm_costo,sm_ealm01,sm_ealm02 FROM sub_mov WHERE mov_id = "+alltrim(str(nMov))+ ";")

do while ! oCursorLocal:eof()
  if (nEAlm := oCursorLocal:fieldget("sm_ealm01")) > 0
    oStmt:SQLString := "UPDATE articul SET art_ealm01 = "+str(nEAlm)+"
     where art_id = '"+oCursorLocal:fieldget("art_id")+"'"
    oStmt:Execute()
  endif
  oCursorLocal:skip(1)
enddo
ocursorLocal:destroy()


Pero creo que no es lo correcto

-------------

Dear Angel,

This is my code


I kown that is not the right way,

I want to update the table movs and the table articul with each record contain in sub_mov table.

Thanks a lot
Osvaldo Ramirez






 wrote:
Jose did the same question 5 days ago.
Just change Items with Accounts and Quantity with Amount
=)
---------------------------------------------------------
Es la misma pregunta que hizo Jose hace 5 dias.
Solo cambia Cuentas por Articulo e Importes por Cantidades
Un balance es un balance no importa si estamos contando dinero o bolitas de vidrio.
=)
6/8/2008 10:21 PM UTC
Re: SQL
Reply Quote

Pablo Botella

Hi Osvaldo,

The ideal situation is that your program will not need to do nothing to update the article stock when you grab a new purchase or change the status of the purchase line ( UPDATE movs SET mov_status = 'ACTUALIZADA' )

To acomplish this task PostgreSQL have 2 mechanisms, RULES and TRIGGERS

The optimum mechanism will be RULE system that:

1) Increase the stock items when new purchase items are inserted (or marked as 'ACTUALIZADA') 2) Decrease the stock items when a new sale is generated
3) Prevent the direct modification of stock items

This will require a hard investiment on effort at the database design time, but will reduce the complexity of your application and also will increase the integrity of your data.

Your can use your "dbf like" aproach as a workarround in the meantime you read and learn about the PostgreSQL RULE system, and later when you have properly defined and tested, replace the dbf-like aproach with the server-side-rule mechanism.  

Regards,
Pablo Botella
6/10/2008 10:10 PM UTC
Re: SQL
Reply Quote

Jose Valle

Hello

RULES and TRIGGERS

With the new version of xbase can we write RULES in xbase++

I have no idea about what are RULES,Is the same as calculated columns?
perhaps I can say something like

art->stock = sum(ent->quantity,ent->cod_art=art->cod_art ) - sum(sal->quantity,sal->cod_art=art->cod_art)

well I supose that will be an sql expresion, bat still don´t know the syntax.
6/10/2008 11:20 PM UTC
Re: SQL
Reply Quote

Osvaldo Ramirez

Dear Jose Valle

Rules = Validation at the field level
Triggers = "Se dispara un evento al momento de cierta condicion"

Saludos
Osvaldo Ramirez

wrote:
Hello
RULES and TRIGGERS
With the new version of xbase can we write RULES in xbase++
I have no idea about what are RULES,Is the same as calculated columns?
perhaps I can say something like
art->stock = sum(ent->quantity,ent->cod_art=art->cod_art ) - sum(sal->quantity,sal->cod_art=art->cod_art)
well I supose that will be an sql expresion, bat still don´t know the syntax.