How to use Query Result From One Query in Another?

[#1]

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

[chriscammers]

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));

[#3]

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;

[#4]

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;