simple 4gl programme 
Author Message
 simple 4gl programme

pple i am having problem s or may be i am tied because i have been working
the whole night

problem

i have two tables and what i want is to take a unique value(column) from
table 2 and put it in
 table 1

example

table one
name  regno course
simba  34   ff
arts   35   tt
iwe    23   dd

table two
name regno course
simba 34   aa
arts  35   mm
iwe   23   dd

so i want a script that will run for three thousand records  and update
course
to a value in table2 eg course in table one to aa  ie basically copying
column course in table two to table one

Quote:
-----Original Message-----

Sent: Wednesday, November 29, 2000 12:28 AM

Subject: Re: TIMESTAMP data type?



> >Well, why not create your own TIMESTAMP datatype, since you're using
9.20?

> >Then you won't have to CHANGE YOUR CODE! :-)

> But, you can't create a distinct type with a default clause, can you?

Don't need a default clause if the type is designed to insert a time stamp
automatically.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 simple 4gl programme

Your request is a little confused (tired?).

Assuming you want to change table_1.name ...

UPDATE table_1
        SET table_1.name = ( SELECT table_2.name
                                        FROM table_2
                                        WHERE table_2.course =
table_1.course );

DO NOT DO THIS ON LIVE DATA WITHOUT TESTING!!

Quote:
-----Original Message-----

Sent: 29 November 2000 08:00

Subject: simple 4gl programme

pple i am having problem s or may be i am tied because i have been working
the whole night

problem

i have two tables and what i want is to take a unique value(column) from
table 2 and put it in
 table 1

example

table one
name  regno course
simba  34   ff
arts   35   tt
iwe    23   dd

table two
name regno course
simba 34   aa
arts  35   mm
iwe   23   dd

so i want a script that will run for three thousand records  and update
course
to a value in table2 eg course in table one to aa  ie basically copying
column course in table two to table one

[deleted]



Wed, 18 Jun 1902 08:00:00 GMT
 simple 4gl programme
update table1 set
    course = (select table2.course
                    from table2
                    where table1.regno    = table2.regno)
where exists (
        select 1 from table2
        where table1.regno    = table2.regno);

Note the following :
1.  Assumes that a unique constraint exists on table2.regno. If, instead, a
unique key exists on name, use name in place of regno. If no unique key
exists on table2, the select must be modifed to return exactly one value
using an aggregate like min(table2.course) or max, etc.
2.  The "where exists" clause ensures that table1 rows, that do not have
table2 matches, do not get set to null.

Rudy

Quote:

> problem

> i have two tables and what i want is to take a unique value(column) from
> table 2 and put it in
>  table 1

> table one
> name  regno course
> simba  34   ff
> arts   35   tt
> iwe    23   dd

> table two
> name regno course
> simba 34   aa
> arts  35   mm
> iwe   23   dd

> so i want a script that will run for three thousand records  and update
> course
> to a value in table2 eg course in table one to aa  ie basically copying
> column course in table two to table one



Wed, 18 Jun 1902 08:00:00 GMT
 simple 4gl programme

 I have done the following to copy from table bkpayment the value in its
column bkpm_linkref to
a table bkrec in the column bkr_linkref but I am getting an error " 391:
Cannot insert a null into column (bkrec.bkr_linkref). ". I then looked if
there are any null values in the source table (bkpaymnt) and found that
there were no rows found , Please help urgently.
The SQL statement I used is as follows

Update bkrec                                                
set bkr_linkref = ( select bkpaymnt.bkpm_linkref from bkpaymn
where bkr_batchref = bkpm_linkref                            
and   bkr_currvalue = bkpm_currvalue * -1                    
and bkr_proof = bkpm_proof                                  
and bkpm_linkref is not NULL                                
)                                                            
where bkr_status = "U"                                      
and bkr_linkref = " "    

nB) Please note that the source table has more columns that the destination
table  

Quote:
-----Original Message-----

Sent: Wednesday, November 29, 2000 3:02 PM

Subject: Re: simple 4gl programme

UPDATE table_1
SET course = (
     select t2.course
     from table_2 t2
     where t2.name = table_1.name and t2.regno = table_1.regno
);
Include the UNIQUE keyword or an aggregate (ie MAX, MIN, etc.) if there may
be more than one matching row in table_2.

Art S. Kagel

----- Original Message -----

At: 11/29  2:56

> pple i am having problem s or may be i am tied because i have been working
> the whole night

> problem

> i have two tables and what i want is to take a unique value(column) from
> table 2 and put it in
>  table 1

> example

> table one
> name  regno course
> simba  34   ff
> arts   35   tt
> iwe    23   dd

> table two
> name regno course
> simba 34   aa
> arts  35   mm
> iwe   23   dd

> so i want a script that will run for three thousand records  and update
> course
> to a value in table2 eg course in table one to aa  ie basically copying
> column course in table two to table one

> -----Original Message-----

> Sent: Wednesday, November 29, 2000 12:28 AM

> Subject: Re: TIMESTAMP data type?



> > >Well, why not create your own TIMESTAMP datatype, since you're using
> 9.20?

> > >Then you won't have to CHANGE YOUR CODE! :-)

> > But, you can't create a distinct type with a default clause, can you?

> Don't need a default clause if the type is designed to insert a time stamp
> automatically.

> Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 simple 4gl programme

Alright - it's bigger than a script, it needs a small program.

Effectively you're setting bkrec.bkr_linkref == bkref.bkr_batchref, in
special circumstances.

Is this what you really want?

You get NULLs because bkrec contains records that do not map to bkpaymnt
(with your query constraints).  In other words, bkrec contains more records
than bkpayment.

It's simplest to do this in a program (i4gl, c, or perl with DBI)

FUNCTION some_function()

DEFINE  pbkrec  RECORD LIKE bkrec.*,
                pbkpayment      RECORD
                        bkpm_linkref    LIKE bkpayment.bkpm_linkref
                                END RECORD,
                text            CHAR( 512 )

LET text = "SELECT * FROM bkrec"

PREPARE pc_bkrec FROM text
DECLARE qc_bkrec CURSOR FOR pc_bkrec FOR UPDATE

LET text = "SELECT bkpayment.bkpm_linkref FROM bkpayment ",
                        "WHERE bkpayment.some_field = ? ",
                        ...

PREPARE pc_bkpayment FROM scratch
DECLARE qc_bkpayment CURSOR FOR pc_bkpayment

FOREACH qc_bkrec INTO pbkrec.*
        LET pbkrec.bkr_currvalue = pbkrec.bkr_currvalue - 1
        OPEN qc_bkpayment using ....
        FETCH qc_bkpayment INTO pbkpayment.*
        IF sqlca.sqlcode = 0
        AND sqlca.sqlerrd[ 3 ] THEN

                UPDATE bkrec USING pbkpayment.bkpm_linkref
                        WHERE CURRENT OF qc_bkrec
        END IF
        CLOSE qc_bkpayment
END FOREACH

END FUNCTION

Quote:
-----Original Message-----

Sent: 30 November 2000 13:55


Subject: simple 4gl programme

I have done the following to copy from table bkpayment the value in its
column bkpm_linkref to
a table bkrec in the column bkr_linkref but I am getting an error " 391:
Cannot insert a null into column (bkrec.bkr_linkref). ". I then looked if
there are any null values in the source table (bkpaymnt) and found that
there were no rows found , Please help urgently.
The SQL statement I used is as follows

Update bkrec                                                
set bkr_linkref = ( select bkpaymnt.bkpm_linkref from bkpaymn
where bkr_batchref = bkpm_linkref                            
and   bkr_currvalue = bkpm_currvalue * -1                    
and bkr_proof = bkpm_proof                                  
and bkpm_linkref is not NULL                                
)                                                            
where bkr_status = "U"                                      
and bkr_linkref = " "    

nB) Please note that the source table has more columns that the destination
table  

-----Original Message-----

Sent: Wednesday, November 29, 2000 3:02 PM

Subject: Re: simple 4gl programme

UPDATE table_1
SET course = (
     select t2.course
     from table_2 t2
     where t2.name = table_1.name and t2.regno = table_1.regno
);
Include the UNIQUE keyword or an aggregate (ie MAX, MIN, etc.) if there may
be more than one matching row in table_2.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. RESEND WITH MORE INFO: simple 4gl programme

2. writing a simple programme

3. SIMPLE 4GL PROGRAM

4. A simple 4gl program question

5. Simple(?) 4gl question

6. simple but long 4gl question

7. 4GL *** UPSHIFT Function for 4GL available *** 4GL

8. Simple 4GL query

9. SQL7, SIMPLE SIMPLE SIMPLE question

10. A question about doing a programme with db-library

11. execute other programme from SQL Server


 
Powered by phpBB® Forum Software