recursive SQL statement please HELP!

scotttiamit · #1 (**permalink**) 10-29-03, 19:09

I am trying to write a pretty complex SQL select statement for a Dataflex database. I am trying to select the labour hours assigned to all stockcodes on all orders for a specific date.

bmtran - holds the bill of materials info (i.e qty of labour hous assigned to each stockcode)

oetran and oemaster - holds order info such as stockcode, date and qty_ord

here is an example of the kind of thing i have tried:

Select bmtran.qty from BMTRAN where bmtran.PARENT_PART IN
(Select oetran.STOCK_CODE from OETRAN where oetran.ORDER_NO IN
(SELECT oemaster.NUMBER FROM OEMASTER
WHERE (oemaster.DATE={d '2003-03-06'}))

I think the reason it does not work is because each subquery returns only one column, but multiple rows.

Any ideas would be much appreciated.
Thanks, Scott.

r937 · #2 (**permalink**) 10-29-03, 20:33

you didn't really say what "does not work" means

i've never even heard of dataflex, but how about rewriting what you have as a join?

Code:

select sum(bmtran.qty)
  from oemaster
inner
  join oetran   
    on oemaster.number = oetran.order_no
inner
  join bmtran
    on oetran.stock_code = bmtran.parent_part        
 where oemaster.date = {d '2003-03-06'}

assuming you wanted total hours, use SUM()

rudy
http://r937.com/

scotttiamit · #3 (**permalink**) 10-29-03, 20:52

Hi Rudy,

Sorry - does not work means either it locks up completely or sometimes returns an error about subquery returning multiple rows.

I have tried using those exact joins, but application always locks up. I have tried using MSQuery to test the SQL as I have the ODBC driver for dataflex.

I have also tried things like:
select sum(bmtran.qty)
from oemaster, bmtran, oetran
where oemaster.number = oetran.order_no
and oetran.stock_code = bmtran.parent_part
and oemaster.date = {d '2003-03-06'}

P.S Dataflex is just a type of database file format, we use CBA which is an accounting database (old and DOS based....yuck!).

Sorry, I am a bit rusty on the SQL side of things!

Thanks.

r937 · #4 (**permalink**) 10-29-03, 21:14

well, best of luck, i'm fresh out of ideas, except one last shot...

if you have microsoft sql server, couldn't you import the dataflex records using DTS and then run your queries there?

all accounting applications i've ever worked on, the users were quite satisfied to get reports based on data as of last night's download

gannet · #5 (**permalink**) 10-30-03, 09:06

I have seen Dataflex (many, many years ago) but not actually used it, so I sympathise.

Assuming that the qty in bmtran is the hours assigned; the parent_part in bmtran is a stock code and the number in oemaster is an order number the following should work.

Code:

SELECT sum(qty)
FROM
  bmtran b,
  (SELECT stock_code, order_no
  FROM oetran) t,
  (SELECT number
  FROM oemaster
  WHERE date = '2003-03-06') m
WHERE t.order_no = m.number
AND b.parent_part = t.stock_code;

scotttiamit · #6 (**permalink**) 10-30-03, 15:32

I ha e tried this which is the same sort of thing, but again I get errors saying that subquery returened multiple rows.

SELECT sum(qty)
FROM bmtran
where bmtran.parent_part in
(SELECT oetran.stock_code, oetran.order_no
FROM oetran) and oetran.order_number in
(SELECT oemaster.number
FROM oemaster
WHERE oemaster.date = '2003-03-06')
and oetran.order_no = oemaster.number
AND bmtran.parent_part = oetran.stock_code

Thanks anyway.

P.S I have since found that data flex does not support inner joins.