Announcement

Collapse
No announcement yet.

subselect based on another subselect

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • subselect based on another subselect

    I'm trying to create a report. It has multiple subselects in it based on one another. I get an error with the where clause in the query for the second sub select.
    Here is how i'm selecting the field:
    AccountNo = {group.AccountNo}

    with group being the first subselect

    It says Unknown column 'Array' in 'where clause' on runtime.


    Any help would be much appreciated. Thanks in advance.

  • #2
    Originally posted by gamer99 View Post
    I'm trying to create a report. It has multiple subselects in it based on one another. I get an error with the where clause in the query for the second sub select.
    Here is how i'm selecting the field:
    AccountNo = {group.AccountNo}

    with group being the first subselect

    It says Unknown column 'Array' in 'where clause' on runtime.


    Any help would be much appreciated. Thanks in advance.
    I'm interested in the {group.AccountNo} part. As fields between {} are treated as formfield variables, I think that {group.AccountNo} is not a valid fieldname for a formfield. If SC is allowing a period in a fieldname then I advise to get rid of that since it will generate all kind of problems. I.e. by calling the field groupAccountNo.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Thanks for replying.

      Yeah, its not really a form field. It was created by the first subselect.

      Basically my problem is I dont know how to call/compare the first subselect field with the second subselect field.

      Some more details:

      Here is the query for my first subselect:
      SELECT
      GAccountNo,
      GAccountDescription,
      Debit,
      Credit
      FROM
      coagroup
      WHERE
      MAccountNo = {MAccountNo}

      Here is the query for the second subselect:
      SELECT
      CAccountNo,
      CAccountDescription,
      Debit,
      Credit
      FROM
      coacontrol
      WHERE
      GAccountNo = ?????

      The where clause is the problem. I dont know how to call the GAccountNo from the first subselect. The field list is showing it as: group.GAccountNo
      Last edited by gamer99; 11-20-2013, 09:50 AM.

      Comment


      • #4
        It's not clear for me what you exactly want to achieve. But you are trying to create some kind of join between two tables?
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          Lets see. I've got 4 tables: Master, Group, Control and Sub. I've created the PDF report with the Master table and have joined the Group table to it via a subselect. Now I need to create a second subselect to join the data from the Control table to the Group table.

          In the field list the the fields from the group table have been added like: group.GAccountNo, group.AccountDescription and so on. So I dont know how to link group.GAccountNo to GAccountNo in the WHERE clause of the Control subselect query (the Control table has a field called GAccountNo).

          Hope this clarifies my situation.

          Thanks for replying.

          Comment


          • #6
            Originally posted by gamer99 View Post
            Lets see. I've got 4 tables: Master, Group, Control and Sub. I've created the PDF report with the Master table and have joined the Group table to it via a subselect. Now I need to create a second subselect to join the data from the Control table to the Group table.

            In the field list the the fields from the group table have been added like: group.GAccountNo, group.AccountDescription and so on. So I dont know how to link group.GAccountNo to GAccountNo in the WHERE clause of the Control subselect query (the Control table has a field called GAccountNo).

            Hope this clarifies my situation.

            Thanks for replying.
            Ah yes, I understand. I have to admit that I don't have that much experience with the pdf stuff to answer this question in a relyable way. Will try to find some time to look in to that, but I hope someone else can answer this question meanwhile.
            Albert Drent
            aducom software netherlands
            scriptcase partner, reseller, support and (turn-key) development
            www.scriptcase.eu / www.scriptcase.nl

            Comment


            • #7
              No worries. Thank you for your time and effort.

              Comment


              • #8
                Hola seria algo asi, al menos en Sql :

                Here is the query for the second subselect:
                SELECT
                CAccountNo,
                CAccountDescription,
                Debit,
                Credit
                FROM
                coacontrol
                WHERE
                GAccountNo in (
                SELECT
                GAccountNo
                WHERE
                MAccountNo = {MAccountNo}
                )

                Comment


                • #9
                  Thanks for the reply. I'll try it out.

                  Comment

                  Working...
                  X