Re: help with report

Chad ha escrito: > I'm having trouble on a 4GL report getting a bill-to and ship-to > customer address retrieved from the database. I need help with what > the select statement should look like and how to read more than one > cursor into the report. > > Here is the table that contains the customer addresses. The same table > has the ship-to and bill-to addresses. > > ar_altname > an_customer integer Ship-to Alternate Customer number > an_cust integer Bill-to Customer Master Number > **Ties to > ar_customer.cust_customer** > an_name_addr char(90) Customer name & address, packed form > > > If ar_altname.an_customer = ar_altname.an_cust then an_name_addr is a > bill-to > If ar_altname.an_customer <> ar_altname.an_cust then an_name_addr is a > ship-to > > I have one cursor already selecting records for the report and it > selects from table ar_altname based on the ship-to, > ar_altname.an_customer. So what I need to do is if the ship-to and > bill-to columns don't equal then take the bill-to number that is > returned and do another select statement where that bill-to is equal to > the ship-to. Do I need another cursor for this select statement? How > do I pass the value of the customer number from one select to the other > and how do I read two cursors into the same report? I think you should use 2 prepared statements 1 that gets the bill-to and the other for the ship-to, when you make a prepared statement you can use the ? simbol to specifi that there, is a variable and when you execute the statement you can put what you want there with the comand 'using' variable.

berso wrote: > Chad ha escrito: > > > I'm having trouble on a 4GL report getting a bill-to and ship-to > > customer address retrieved from the database. I need help with what > > the select statement should look like and how to read more than one > > cursor into the report. > > > > Here is the table that contains the customer addresses. The same table > > has the ship-to and bill-to addresses. > > > > ar_altname > > an_customer integer Ship-to Alternate Customer number > > an_cust integer Bill-to Customer Master Number > > **Ties to > > ar_customer.cust_customer** > > an_name_addr char(90) Customer name & address, packed form > > > > > > If ar_altname.an_customer = ar_altname.an_cust then an_name_addr is a > > bill-to > > If ar_altname.an_customer <> ar_altname.an_cust then an_name_addr is a > > ship-to > > > > I have one cursor already selecting records for the report and it > > selects from table ar_altname based on the ship-to, > > ar_altname.an_customer. So what I need to do is if the ship-to and > > bill-to columns don't equal then take the bill-to number that is > > returned and do another select statement where that bill-to is equal to > > the ship-to. Do I need another cursor for this select statement? How > > do I pass the value of the customer number from one select to the other > > and how do I read two cursors into the same report? > > I think you should use 2 prepared statements 1 that gets the bill-to > and the other for the > ship-to, when you make a prepared statement you can use the ? simbol to > specifi that there, is a variable and when you execute the statement > you can put what you want there with the comand 'using' variable. I didn't see what version you are using 4GLso you may need to read your documentation for details , however you would use the ? symbol something like this # prepare statement to select cust no let scratch = "select cust_no,sum(amount) from inscashr where cert_no = ? group by cust_no" prepare get_c from scratch declare get_cash cursor for get_c let scratch = "select date_enter from inscashr where agency_code =? and cert_no = ?" prepare get_cde from scratch declare get_cdenter cursor for get_cde # create temp table for summary totals if do_sumry is NUll then let scratch = "create temp table sumary_tots(", "slsrep char(4), ", "prov char(2), ", "agency_no smallint, ", "sum_amnt decimal(12) )" prepare create_sumary from scratch execute create_sumary let scratch = "insert into sumary_tots values(?,?,?,?)" prepare ins_prep_sum from scratch