This group of records should go into every IN clause of the above query.
Can it be possible to do using SORT?
I am planning to do as below.
1.count the IN clauses in the input query. The number of IN clauses can vary, they are not 3 always as shown above.
2.Repeat the group of records in second dataset based on the count from step 1. Have each group of records tagged with a number and alternate the number for every next group, such as 2,4,6,8 etc...
3.For the query put a tag like <A> from start of the query and the next will be after the IN clause. Essentially group the records will have the same sequence number before IN and after IN clause and alternate way as in 1,3,5,7 etc...
4.Now, copy the query and the repeated group of records and sort by the number attached. This should rearrange the records and prepare as below.
Code:
SELECT * FROM A WHERE A1 IN (
'1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)
UNION
SELECT * FROM B WHERE B1 IN (
'1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)
UNION
SELECT * FROM C WHERE C1 IN(
'1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)
WITH UR;
Please suggest me if there is any better way to do this. Thank you very much for the help.
The number of queries are variable and the IN clauses within each query are variable. Means the example query I posted has 3 IN clauses. In the next query I might have only one IN clause. so, I will need to add the records only once.