Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Auckland, New Zealand
    Posts
    3

    Unhappy Unanswered: recursive SQL statement please HELP!

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    Join Date
    Oct 2003
    Location
    Auckland, New Zealand
    Posts
    3
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    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;

  6. #6
    Join Date
    Oct 2003
    Location
    Auckland, New Zealand
    Posts
    3
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by scotttiamit
    P.S I have since found that data flex does not support inner joins.
    well, what good is it then?

    what about the idea of importing your data to sql server?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •