Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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));
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;
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;