Building SQL

[yathish.venkatesh]

Isomorphic,

As we know in any data source, we have

SELECT <selectClause>
FROM <tableClause>
WHERE <whereClause>
GROUP BY <groupClause>
ORDER BY <orderClause>

These are straight forward for building queries like the below

SELECT FirstName, LastName, Age, Salary
FROM Employee
ORDER BY Salary

If i have a query with one or more sub query, how can that can be achieved?

For Example,

SELECT Name, age, Salary FROM (
            SELECT FirstName as Name, age, Salary
            FROM Employee
            WHERE Department='Computer Science'
UNION ALL
            SELECT FirstName as Name, age, Salary
            FROM Employee
            WHERE Department='ELECTRONICS'
)
ORDER BY age

I know here we can do department in ('a','b') But in my business case, we need to have sub queries with unions. How can we achieve this.

Can we also use velocity macros to generalise. If so, where to put these macros?

In the above query, except the where clause, everything would be same, so we can write a macro with one parameter and call that.

Let me know your thoughts on this. It would be very helpful in our process.

Thanks,
Yathish

[Isomorphic]

You can put sub selects into the tableClause.

For Velocity reference, see the Apache.org site.

[yathish.venkatesh]

Correct me if i am wrong. Fir the above query the nodes looks like below is it?
<selectClause>
<tableClause>
<selectClause>
<tableClause>
<whereClause>
<groupClause>
UNION
<selectClause>
<tableClause>
<whereClause>
<groupClause>
<whereClause>
<groupClause>
<orderClause>

Also how can i use macros in .ds.xml file. I know how to write macros and use it. where can i place(in which section of ds.xml) macros so that i can use that across all my operations?

[Isomorphic]

No, <clause> tags are never nested or repeated. Put the entire subselect into the tableClause. If your query can't be broken into a single set of clauses you must use <customSQL> (note it's drawbacks, such as lack of paging).