On 19 Oct 2012 13:33, Nisha Ramesh wrote:
The complete program logic is this
I have a file which has 6 different account numbers. For each of this
account number, I need to check in File1 which has account number,
security and Quantity. I need to sum up the qty field for a
combination of Accnum and security. Once I get the security, I need
to search in another file say File2 for SecRate corresponding to the
security which I got from File1. Apart from the Qty sum up, I have
done the program with normal file operation. But the Qty part seems
to be complex without SQL. But I haven't written any SQL so far. So
Please show me how to write query for this.
Thanks for all of your time
I need to write the output in a summary file.
If I understand correctly, this new description of the requirements,
the changed file names, and apparently a new or different column in the
summary file, then effectively something like the following (noting: I
have chosen column names for my convenience):
<code>
create table File0 (AcN CHAR)
; -- six account numbers for which summarizing is desirable
insert into File0 values
('A'),('B'),('C'),('D'),('E'),('F')
;
create table File1 (AcN CHAR, Sec CHAR, Qty DEC)
; -- quantities for each account\security combination
insert into File1 values
('A', 'x', 2), ('A', 'x', 3), ('A', 'x', 1)
, ('A', 'y', 2), ('A', 'y', 3)
, ('B', 'y', 5), ('B', 'y', 1)
, ('C', 'x', 2), ('C', 'x', 2)
, ... /* note: missing matching values may go here */
, ('Z', '.', 0)
;
create table File2 (Sec CHAR, Rte DEC)
; -- security "rate" correlation; one-to-one
insert into File2 values
('x', 11)
, ('y', 22)
, ... /* note: any missing matching values to go here */
, ('.', 0)
;
create table File3 as
( select S.AcN, S.Sec, S.Qty, C.Rte
from ( select A.AcN, B.Sec, sum(B.Qty) as Qty
from file0 A left outer join file1 B
on A.AcN = B.AcN
group by A.AcN, B.Sec
) S
left outer join file2 C
on S.Sec = C.Sec
/* inner join(s) if no missing AcN */
) with data
;
</code>
I suppose the "rate" field may be intended to evaluate with the
summarized quantity as an expression for the created summary data,
having only three fields as originally described... but where the last
column is the multiplication of "rate" by "quantity". Then perhaps
instead, the following:
<code>
create table File3 as
( select S.AcN, S.Sec, decimal(S.Qty * C.Rte, 10, 2) as calc
from ( select A.AcN, B.Sec, sum(B.Qty) as Qty
from file0 A left outer join file1 B
on A.AcN = B.AcN
group by A.AcN, B.Sec
) S
left outer join file2 C
on S.Sec = C.Sec
/* inner join(s) if no missing AcN */
) with data
;
</code>
Note: My examples use LEFT JOIN because the setup data I included is
missing matching data values in [at least] one of the files [if the
ellipses are not replaced with necessary matching values].
midrange.com
