4GL query by example problem
Hello netters and 4GL experts!!
I have a serious problem with creating a query by example that includes
more than one table, and which not all columns from the tables are
included. I have submitted the function, form specification, and the
table definitions below. The problem is described with capitalized letters
inside the function.
All variables are defined. If not within the function, I have defined the
globally elsewhere.
Maybe I should mention that I am relatively new to 4GL (and programming).
Please, take a few minutes to study the problem. If you cannot give me
a complete answer, maybe you can lead me in the right direction.
Any and all information are appreciated.
Thank you!
Sincerely,
SoM
---------------------------------------------------------
From: Knut E. Vonheim Sign.: SoM
Comp: A/S OBI
Addr.: Fred Olsensgt. 1, 0102 Oslo tlf: 22 34 18 06
Norway
e-mail knutv@boers.no Beeper: 096 99357
---------------------------------------------------------
FUNCTION change_comp_data()
define comp_select, comp_query char(500),
long_string char(1000)
let addr_cnt=1
let cont_cnt=1
{
THE FORM IS DISPLAYED FROM ANOTHER FUNCTION
}
clear form
message "Use the first row of the address and contact arrays for query"
construct comp_query on cname, address, po_no, post_office, addr_txt, attn,
comment, phone, fax
from cname, addr[1].address, addr[1].po_no, addr[1].post_office,
addr[1].addr_txt, contact[1].attn, contact[1].comment,
contact[1].phone, contact[1].fax
let comp_select="select cname, address, po_no, post_office, addr_txt, attn, ",
"comment, phone, fax from company, address, addr_id, contact_person",
" where ", comp_query clipped, " and addr_id.addr_type_id=address.addr_type_id",
" and company.cname=cname"
prepare query from comp_select
declare q_curs cursor for query
foreach q_curs into comp_name, comp_addr[counter].*, comp_contact[counter].*
{
THIS IS WHERE MY PROBLEM IS. ONE COMPANY MAY HAVE MORE THAN ONE ADDRESS AND MORE THAN ONE CONTACT PERSON.
ALSO, THE NUMBER OF CONTACTS AND THE NUMBER OF ADDRESSES MAY BE DIFFERENT.
I WOULD LIKE TO DISPLAY ALL ADDRESSES AND ALL CONTACTS FOR EACH COMPANY AS THE USER GOES THROUGH THE
SELECTED ROWS. THE PROBLEM IS, TO MY KNOWLEDGE, THAT I OPERATE ON MORE THAN ONE ROW IN THE ADDRESS AND
CONTACT_PERSON TABLES WHILE USING ONE ROW FROM THE COMPANY TABLE.
MY ATTEMPTS HAVE HAD VARIOUS RESULTS. I HAVE ONLY TESTED MY SOLUTIONS WITH ONE COMPANY WITH TWO
CONTACTS AND TWO ADDRESSES. THE RESULT, OF MY MOST SUCCESSFUL ATTEMPT, WAS THAT EACH ADDRESS AND
EACH CONTACT PERSON WAS DISPLAYED TWICE (READ: APPEARED TWICE IN EACH PROGRAM ARRAY).
I HAVE DELIBERATELY DELETED MY APPROACH IN ORDER TO (HOPEFULLY) GET NEW IDEAS FROM YOU
EXPERTS OUT THERE!! NEITHER DID I WANT TO LEAD YOU INTO MY WAY OF THINKING (WHICH HAVEN'T
SOLVED THE PROBLEM).
}
end foreach
free q_curs
display comp_name to cname
call set_count(addr_cnt-1)
display array comp_addr to addr.*
call set_count(cont_cnt-1)
display array comp_contact to contact.*
-- insert and update the information
END FUNCTION
This is the specification of the form used for the query by examle:
DATABASE fond
SCREEN
{
--------------------------------------------------------------------------------
Selskap: [c ]
Adresse Postnr. Poststed Type
[a ] [n ] [s ] [y ]
[a ] [n ] [s ] [y ]
[a ] [n ] [s ] [y ]
Kontakt Kommentar Telefon Fax
[p ] [k ] [t ] [f ]
[p ] [k ] [t ] [f ]
[p ] [k ] [t ] [f ]
[p ] [k ] [t ] [f ]
[p ] [k ] [t ] [f ]
--------------------------------------------------------------------------------
}
TABLES company, address, addr_id, contact_person
ATTRIBUTES
c=company.cname;
a=address.address;
n=address.po_no, include=(0,1,2,3,4,5,6,7,8,9);
s=address.post_office ;
y=addr_id.addr_txt;
p=contact_person.attn;
k=contact_person.comment;
t=contact_person.phone;
f=contact_person.fax;
INSTRUCTIONS
screen record addr[3] (address.address, address.po_no,
address.post_office, addr_id.addr_txt);
screen record contact[5] (contact_person.attn, contact_person.comment,
contact_person.phone, contact_person.fax);
These are the tables I want to do a query on:
{ This table contains ids for different types of addresses
(i.eg. mailing address, street address, invoice, etc.).}
CREATE TABLE addr_id
(addr_type_id serial(1) NOT NULL,
addr_txt CHAR(10)
);
CREATE UNIQUE CLUSTER INDEX XPKaddr_id
ON addr_id
(
addr_type_id ASC
);
{ Address contains each address for a specific company }
CREATE TABLE address
(addr_type_id integer NOT NULL,
cid smallint NOT NULL,
address CHAR(30),
po_no CHAR(4),
post_office character(20)
);
CREATE UNIQUE CLUSTER INDEX XPKaddress
ON address
(
addr_type_id ASC,
cid ASC
);
{ Table contains company id and company name }
CREATE TABLE company
(cid smallint NOT NULL,
cname CHAR(30) NOT NULL UNIQUE
);
CREATE UNIQUE CLUSTER INDEX XPKCompany
ON company
(
cid ASC
);
{ contacts within a company }
CREATE TABLE contact_person
(attn CHAR(20) NOT NULL,
cid smallint NOT NULL,
comment CHAR(15),
phone CHAR(15),
fax CHAR(15)
);
CREATE UNIQUE CLUSTER INDEX XPKperson
ON contact_person
(
attn ASC,
cid ASC
);
ALTER TABLE addr_id
ADD CONSTRAINT PRIMARY KEY (addr_type_id);
ALTER TABLE address
ADD CONSTRAINT PRIMARY KEY (addr_type_id, cid);
ALTER TABLE company
ADD CONSTRAINT PRIMARY KEY (cid);
ALTER TABLE contact_person
ADD CONSTRAINT PRIMARY KEY (attn, cid)
ALTER TABLE address
ADD CONSTRAINT FOREIGN KEY (addr_type_id)
REFERENCES addr_id
CONSTRAINT of_add_type;
ALTER TABLE address
ADD CONSTRAINT FOREIGN KEY (cid)
REFERENCES company
CONSTRAINT has_address;
ALTER TABLE contact_person
ADD CONSTRAINT FOREIGN KEY (cid)
REFE