File handling vs SQL query?



IBM's flagship relational database management system

Re: File handling vs SQL query?

Postby steve-myers » Wed Nov 02, 2016 9:00 pm

Harhsaa_KK wrote:... Now to calculate the fee for each customer based on number of bulbs, ...

We have to remember the original purpose of electricity was to provide illumination after dark. In other words, to replace candles and other forms of illumination. So charging by the number of bulbs might have made sense, at least in 1890. I'm writing this about 11:15 in the morning on a bright sunny day. Now how many "bulbs" do a desktop computer, a modern LED based flat panel monitor, and the gadgets to talk to the internet represent?
steve-myers
Global moderator
 
Posts: 1850
Joined: Thu Jun 03, 2010 6:21 pm
Has thanked: 4 times
Been thanked: 188 times

Re: Re: File handling vs SQL query?

 

Re: File handling vs SQL query?

Postby xRDK » Thu Apr 06, 2017 8:16 pm

Not sure about all the COBOL stuff but if you wanted to do this in SQL (assuming the bulb cost is the same for all bulbs);

SELECT CUST_NUMBER, SUM(NUMBER_OF_BULBS) * COST_OF_BULB
FROM MYTABLE
GROUP BY CUST_NUMBER
ORDER BY CUST_NUMBER

Where COST_OF_BULB is what you wanna charge.

Let's say there's more than meets the eye here, and you're table has;

CUST_NUMBER, BULB_TYPE, NUMBER_OF_BULBS

And you have a bulb table with costs;

BULB_TYPE, COST_OF_MY_BULBZ

Then;

SELECT CUST_NUMBER, BULB_TYPE,
SUM(NUMBER_OF_BULBS) * (SELECT COST_OF_MY_BULBZ
FROM BULBS Z
WHERE Z.BULB_TYPE = A.BULB_TYPE)

FROM YOURTABLE A
GROUP BY CUST_NUMBER, BULB_TYPE
ORDER BY CUST_NUMBER, BULB_TYPE
xRDK
 
Posts: 3
Joined: Wed Apr 05, 2017 10:04 pm
Has thanked: 0 time
Been thanked: 0 time

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post