HOW TO JOIN TWO TABLE IN JBASE QUERY!

[jbase.newbie]

Hi all,
I've used T24 and Jbase. I've written a program in infobasic language,
I want to join 2 table (2 file) in order to get data and sort, etc.
However, I don't want to read each file and compare to the other in
normal way.
Can you show me how to solve it?
I also tried sql-jbase but I have a problem
My example query like:
"SQLSELECT a.ACCOUNT.NUMBER, a.CURRENCY, b.SECTOR FROM FBNK.CUSTOMER
b, FBNK.ACCOUNT a WHERE a.CUSTOMER = b.CUSTOMER.CODE"
Note: CUSTOMER and ACCOUNT are table in T24.
Plz show me the way
Thanks for your attention.

[dennis bartlett]

So you have written a program to do it? or are you wanting to skip writing the program?

The easiest way is to create I-TYPE DICT records in one file to point to the other file, then sort and select on either the local or foreign fields.

If you're in a program, you can still use the above method, by executing the "SELECT XXX BY YYY" statement via EB.READLIST and processing the result.

As the relationship between CUSTOMER and ACCOUNT is one-many, it makes sense to list the ACCOUNT file and refer back to CUSTOMER.

Our F.ACCOUNT file already has an I-TYPE in F.ACCOUNT called SECTOR, as follows

ED DICT F.ACCOUNT SECTOR
SECTOR
TOP
.P
TOP
001 I
002 CUSTOMER.NO; SUBR("ENQ.TRANS","CUSTOMER", @1, "SECTOR")
003
004 SECTOR
005 4R
006 S

so to return a list (report) of what you are seeking in your SQL below you just have to

LIST FBNK.ACCOUNT WITH CUSTOMER LIKE ZZZ123... SECTOR CURRENCY

The problem for you here is that this will only display this on a screen. There are a number of ways to arrive at outputted data in the form an SQL statement would return it...

The easiest I find is to "capture" the screen output to a "COMmand Output" (COMO) file, or simply write a program to do the reads you want to avoid. This COMO file, called &COMO&, is in effect a directory, accessible via MS Excel.

In order to do the extract via a COMO file, into what looks like a CSV file, it helps to create another I-TYPE called COMMA, in the dict of whatever file you want to extract from, which you define as

COMMA
TOP
.P
TOP
001 I
002 ","
003
004 ,
005 1R
006 S

and then, at the command prompt, redirect the output to a COMO file by typing

> COMO ON CSV.NAME.csv

then executing the list statement as follows

> LIST FBNK.ACCOUNT WITH CUSTOMER LIKE ZZZ123... COMMA SECTOR COMMA CURRENCY

To "end" the command output redirection (and arrive at a closed, finished, conpleted file) you enter

> COMO OFF

What will be in the &COMO& file is a file containing a CSV file showing ACCOUNT number in Column A, SECTOR in coloumn B, and CURRENCY in Column C.

I hope this helps somewhat...

dennis

2008/ 12/19 jbase.newbie <jbase....@gmail.com>

[jbase.newbie]

Thank for your reply.
However, if use I-TYPE DICT, I cannot take the value of that field in
my code (by I_F.CUSTOMER, I_F.ACCOUNT).
CUSTOMER and ACCOUNT is just an example.
Thanks by anyway.

On 22 Tháng Mười Hai, 17:34, "dennis bartlett" <dqbartl...@gmail.com>
wrote:

> 2008/ 12/19 jbase.newbie <jbase.new...@gmail.com>

>
>
>
>
>
> > Hi all,
> > I've used T24 and Jbase. I've written a program in infobasic language,
> > I want to join 2 table (2 file) in order to get data and sort, etc.
> > However, I don't want to read each file and compare to the other in
> > normal way.
> > Can you show me how to solve it?
> > I also tried sql-jbase but I have a problem
> > My example query like:
> > "SQLSELECT a.ACCOUNT.NUMBER, a.CURRENCY, b.SECTOR FROM FBNK.CUSTOMER
> > b, FBNK.ACCOUNT a WHERE a.CUSTOMER = b.CUSTOMER.CODE"
> > Note: CUSTOMER and ACCOUNT are table in T24.
> > Plz show me the way

> > Thanks for your attention.- Ẩn nội dung trích dẫn -
>
> - Hiển thị văn bản được để trong dấu trích dẫn -