SELECT blues 1

[torturedmind]

hi all. been a while since the last time i visited this awesome forum.

anyways, i've got a scenario here...

[tt]table1
======
lot_code ref_lot1 ref_lot2 ref_lot3
AAAAAAAA 11111111
BBBBBBBB 22222222 33333333 44444444
CCCCCCCC 55555555 66666666
DDDDDDDD 77777777 88888888
EEEEEEEE 99999999 00000000

table2
======
ref_lot qty_issued issued_date
11111111 854 06/01/2010
22222222 361 06/01/2010
33333333 1925 06/05/2010
44444444 682 06/07/2010
55555555 736 06/07/2010
66666666 557 06/08/2010
77777777 1188 06/09/2010
88888888 991 06/10/2010
99999999 715 06/10/2010
00000000 981 06/11/2010
[/tt]

i need the output to be like this...
[tt]
table3
======
lot_code ref_lot1 issqty1 issd1 ref_lot2 issqty2 issd2 ref_lot3 issqty3 issd3
--------------------------------------------------------------------------------------------------------------
AAAAAAAA 11111111 854 06/01/2010
BBBBBBBB 22222222 361 06/01/2010 33333333 1925 06/05/2010 44444444 682
CCCCCCCC 55555555 736 06/07/2010 66666666 557 06/08/2010
DDDDDDDD 77777777 1188 06/09/2010 88888888 991 06/10/2010
EEEEEEEE 99999999 715 06/10/2010 00000000 981 06/11/2010
[/tt]

my question is, can this be done using select statement only? if this can be, then how? i tried...

select table1.lot_code, ;
    table2.ref_lot as ref_lot1, ;
    table2.qty_issued as issqty1, ;
    table2.issued_date as issd1, ;
    table1.ref_lot2, ;
    table1.ref_lot3 ;
    from table1 ;
    left outer join table2 ;
    on table1.ref_lot1 = table2.ref_lot ;
    into cursor table3a

select table3a.lot_code, ;
    table3a.ref_lot as ref_lot1, ;
    table3a.issqty1, ;
    table3a.issd1, ;
    table3a.ref_lot2, ;
    table2.qty_issued as issqty2, ;
    table2.issued_date as issd2, ;
    table3a.ref_lot3 ;
    from table3a ;
    left outer join table2 ;
    on table3a.ref_lot2 = table2.ref_lot ;
    into cursor table3b

select table3b.lot_code, ;
    table3b.ref_lot as ref_lot1, ;
    table3b.issqty1, ;
    table3b.issd1, ;
    table3b.ref_lot2, ;
    table3b.issqty2, ;
    table3b.issd2, ;
    table3b.ref_lot3 ;
    table2.qty_issued as issqty3, ;
    table2.issued_date as issd3, ;
    from table3b ;
    left outer join table2 ;
    on table3b.ref_lot3 = table2.ref_lot ;
    into cursor table3_final

problem is, those records with blank ref_lots in table1 extracts record from table2 even if their ref_lots were not equal or even empty. care to give me a hand here, anyone?

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[cbasoz]

SELECT lot_code, ;
	ref_lot1, t21.qty_issued as issqty1, t21.issued_date as issd1, ;
	ref_lot2, t22.qty_issued as issqty2, t22.issued_date as issd2, ;
	ref_lot3, t23.qty_issued as issqty3, t23.issued_date as issd3 ;
from table1 t1 ;
left join table2 t21 on t1.ref_lot1 = t21.ref_lot ;
left join table2 t22 on t1.ref_lot2 = t22.ref_lot ;
left join table2 t23 on t1.ref_lot3 = t23.ref_lot ;
into cursor table3
SET NULLDISPLAY TO ''
BROWSE

PS: I honestly wouldn't suggest such tables designs. But since that is what you want here it is.

Cetin Basoz
MS Foxpro MVP, MCP

 

[torturedmind]

@cetin
thanks so much for the suggestion.

PS: I honestly wouldn't suggest such tables designs. But since that is what you want here it is.

yup i know but this is what i got when i arrived at this company. anyways, am gonna try this and see what happens. thanks again.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[torturedmind]

sorry for the late update. i tried the suggestion above and sad to say, it took almost forever to query a couple of hundreds of thousands of records. so i just made my own workaround.

note: tables are contained in a temporary database container.

SELECT table1
INDEX ON table1.lot_code TO (SYS(2023) + "\tbl_lot_code")

SELECT table2
SCAN
	SELECT table1

	FOR i = 1 TO 3
		fld_ref_lot = "ref_lot" + TRANSFORM(i)
		ref_lot_to_seek = table2.&fld_ref_lot

		IF NOT EMPTY(ref_lot_to_seek)
			SEEK ref_lot_to_seek

			IF FOUND()
				fld_ref_lot = "ref_lot" + TRANSFORM(i) + "_rcv_qty"

				SELECT table2
				REPLACE table2.&fld_ref_lot WITH table1.qty_rcv
				SELECT table1
			ENDIF
		ENDIF
	ENDFOR

	SELECT table2
ENDSCAN

well, it may not be the most elegant solution but it works. thanks for the suggestion cetin.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Kilroy,

A couple of small points:

1. Is Table1 a permanent table, or do you create it on the fly? If permanent, you'll save a bit of time by making the index permanent as well.

In other words, instead of doing this each time you run the code:

INDEX ON table1.lot_code TO (SYS(2023) + "\tbl_lot_code")

Do this, once only:

INDEX ON table1.lot_code TAG Lot_Code

Then, when you run the code, do this:

SET ORDER TO Lot_Code

That will save the time it takes to create the index each time.

2. Going further, why do you need the index on Lot_Code? Your code doesn't appear to be using it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[torturedmind]

hi mike. thanks for giving time to reply.

1. Is Table1 a permanent table, or do you create it on the fly? If permanent, you'll save a bit of time by making the index permanent as well.

it's being created on the fly.

2. Going further, why do you need the index on Lot_Code? Your code doesn't appear to be using it.

SELECT table1
INDEX ON table1.lot_code TO (SYS(2023) + "\tbl_lot_code")

SELECT table2
SCAN
    SELECT table1

    FOR i = 1 TO 3
        fld_ref_lot = "ref_lot" + TRANSFORM(i)
        ref_lot_to_seek = table2.&fld_ref_lot

        IF NOT EMPTY(ref_lot_to_seek)
            [b][red]SEEK ref_lot_to_seek[/red]    [green]&& i use it for seeking[/green][/b]

            IF FOUND()
                fld_ref_lot = "ref_lot" + TRANSFORM(i) + "_rcv_qty"

                SELECT table2
                REPLACE table2.&fld_ref_lot WITH table1.qty_rcv
                SELECT table1
            ENDIF
        ENDIF
    ENDFOR

    SELECT table2
ENDSCAN

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Kilroy,

Oh, I see. I hadn't realised that the SEEK on Ref-Lot-To_Seek was using the Lot_Code index. But now you mention it, it's obvious.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

Provided you have indexes it would take a fraction of time. If you are creating that on the fly, you should think about why you are creating it like that in the first place and you may also do that during creation of table1. Probably there is an easier way from original source.
PS: You could create indexes on the fly too.

Cetin Basoz
MS Foxpro MVP, MCP

 

[torturedmind]

Thanks Cetin and Mike (who, btw, is always here to help since i first joined this awesome forum) for taking the time to reply.

Well, i myself do not like the over-all design of the whole work-in-process (aka WIP) system (we call it that way). To start with, tho the tables have fields to accommodate the user's entry, these fields do not really describe (or characterize) the data they carry. An example would be the one i asked help for. Here is our scenario:

We manufacture custom micro-electronics which, as you know, needs to have different micro-components to assemble to finally produce the finish products. One of the components, the wafer, is divided into several slices and each slice produces several hundreds finished product. The user encodes the "mother" lot number (we call this lot_code) of the wafer which, when divided, will produce reference lot numbers per slice - one slice, one reference lot number (or ref_lot). The "lot_code" and the "ref_lot" are both 11-character long data but only the first 7 characters are identical. "ref_lot"s carry unique alphanumerics in the last 4 characters.

During material issuance, ref_lots are encoded by the user, unfortunately, in to the fields that are not really descriptive of their purpose - the "date_code" field. Depending on the capacity of the production line, a production batch can be composed of 1, 2, or 3 slices and their slice id's (the ref_lot) are encoded in the "date_code" field but only the last 4 characters! The user just separate them using slash "/" if the batch is composed of more than 1 slices. "Why on earth the date_code field?!", you might ask. Tho this is just MY speculation, i think because they were instructed by the system designer himself. So what i did was to "assemble" the actual ref_lot first so i can have reference to their lot_code ("mother" lot). Thus, this post.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[cbasoz]

I see. You have a nice and hard work I even can't do a rough soldering.


Cetin Basoz
MS Foxpro MVP, MCP