This discussion is archived
3 Replies Latest reply: Feb 25, 2013 1:20 AM by HamidHelal RSS

Query from multiple data blocks

993095 Newbie
Currently Being Moderated
Hello professionals,

I having difficulty in finding solutions to query data from multiple data blocks. By the way I'm using Oracle Forms 10g

I have 4 data blocks and all items are database items

Below are the blocks,

1. student_main (columns : student_id, student_name, student_state)
2. subject_main (columns : subject_id, subject_desc, subject_student_id)
3. invoice_main (columns : invoice_no, invoice_amt, invoice_student_id)
4. receipt_main (columns : receipt_no, receipt_amt, receipt_invoice_ref)

The form will need to allow user to be able to query from any of the items/fields from the data blocks as listed above

For eg,
: if user query student_id then all related value items from table subject_main, invoice_main, receipt_main need to be displayed
: if user query invoice_no then all related value items from table student_main, subject_main, receipt_main need to be displayed


However the tricky part is that some student might have or might not have invoice / receipt data

I tried using relations but it didn't work both ways
I even create relations in every table (student_main to subject_main, subject_main to student_main, invoice_main to student_main, student_main to invoice_main and etc.) but it just make my running process hang

Need help on this matter

Thank you

Edited by: 990092 on Feb 25, 2013 12:15 AM
  • 1. Re: Query from multiple data blocks
    HamidHelal Guru
    Currently Being Moderated
    990092 wrote:
    Hello professionals,

    I having difficulty in finding solutions to query data from multiple data blocks. By the way I'm using Oracle Forms 10g

    I have 4 data blocks and all items are database items

    Below are the blocks,

    1. student_main (columns : student_id, student_name, student_state)
    2. subject_main (columns : subject_id, subject_desc, subject_student_id)
    3. invoice_main (columns : invoice_no, invoice_amt, invoice_student_id)
    4. receipt_main (columns : receipt_no, receipt_amt, receipt_invoice_ref)

    The form will need to allow user to be able to query from any of the items/fields from the data blocks as listed above

    For eg,
    : if user query student_id then all related value items from table subject_main, invoice_main, receipt_main need to be displayed
    : if user query invoice_no then all related value items from table student_main, subject_main, receipt_main need to be displayed


    However the tricky part is that some student might have or might not have invoice / receipt data

    I tried using relations but it didn't work both ways
    I even create relations in every table (student_main to subject_main, subject_main to student_main, invoice_main to student_main, student_main to invoice_main and etc.) but it just make my running process hang
    In general, relation will work. your relation creating was wrong, i think.
    do as
    1. student_main master to subject_main details
    2. student_main master to invoice_main details
    3. invoice_main master to receipt_main details

    Hope this helps

    Hamid
  • 2. Re: Query from multiple data blocks
    993095 Newbie
    Currently Being Moderated
    Thanks for replying

    I tried creating the relations as advised

    It did work where all the item displayed accordingly
    However that's only if I tried to query from student_main item (I query student id)

    When I test to query using 'subject_id' the value of data block subject_main only appear while the rest didn't
    Is as if the master block didn't even respond to detail block query
  • 3. Re: Query from multiple data blocks
    HamidHelal Guru
    Currently Being Moderated
    990092 wrote:
    Thanks for replying

    I tried creating the relations as advised

    It did work where all the item displayed accordingly
    However that's only if I tried to query from student_main item (I query student id)
    i said in general. and it works..
    When I test to query using 'subject_id' the value of data block subject_main only appear while the rest didn't
    Is as if the master block didn't even respond to detail block query
    in relation block, query on details block doesn't effect on master block.
    in your exceptional work, you need to write exceptional query to see the data.

    Hope this helps

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points