3 Replies Latest reply: May 20, 2011 12:24 PM by Javier Piqueras RSS

    How to use Query Result From One Query in Another?

      Suppose I have a Query Say "Select Ticket_ID, Status from Table A". It returns Ticket_ID values as 1, 2, 3

       

      Now I want to use the values in Another query without writing a sub query

       

      Query Should be Select ticket_ID, Sum(Value) from Table B Where Ticket_ID in (1, 2, 3) i.e.values from Query 1.

       

      And Not Select Ticket_ID, sum(Value) from Table A Where Ticket_ID in (Select Ticket_ID from table A)

       

      Because the actual Query I have Is quite big and I dont want to use that as a subquery.

       

      I cannot used join here since it with give me 1 -> n values which will give incorrect results.

       

      Please suggest

       

      Regards

      Suresh

       

        • How to use Query Result From One Query in Another?
          Chris Cammers

          I would suggest storing the Ticket_ID's from your first query in a variable, you would use the Concat() function to create a comma separated list and then pass the variable in your sql using syntax something like this...

           

          Tickets:

          Load

          Concat(Chr(39) & Ticket_ID & Chr(39),', ') as Tickets;

          Select Ticket_ID from...;

           

          Let YourVar = Peek('Tickets',0,'Tickets');

           

           

          Select * from Blah where Ticket_ID In ($(YourVar));

          • Re: How to use Query Result From One Query in Another?

            Try this:

             

            Test:

            Load

            Ticket_ID,

            Status

            from TableA.qvd (qvd);

             

            concatenate

             

            Load

            ticket_ID as Ticket_ID,        // I assume that ticket_ID is another field in table B

            Value                                 //which has same values as Ticket_ID from table A

            from TableB.qvd (qvd);

             

            Test1:

            Load

            Ticket_ID,

            Value

            resident Test where Ticket_ID=1 or 

             

            Ticket_ID=2 or Ticket_ID=3;

             

            drop table Test;

             

            Test2:

            Load

            Ticket_ID,

            Sum(Value) as Total_Value

            resident Test1 group by  Ticket_ID;

             

            drop table Test1;

            • Re: How to use Query Result From One Query in Another?

              Try something like this:

               

              TB_A:

              SELECT
                   TICKET_ID,
                   STATUS
              FROM TABLE_A;
              INNER JOIN

              SELECT
                   TICKET_ID,
                   SUM(VALUE) AS TICKET_VALUE
              FROM TABLE_B
              GROUP BY TICKET_ID;