Sometimes simple things can be of a great help. I used to always find it quite cumbersome to wrap my SQL code into NSQL when making portlets. That's why I decided one day to try to make this go faster by creating a very simple Excel tool to make my life easier, the NSQL Maker.
The Excel sheet contains 3 tabs:
- Queries: to create NSQL code for queries
- Lookups: to create NSQL code for lookups
- Get attributes: space to paste your attributes for easier copy/paste into the previous tabs
So how does it work?
1. Once you have done your SQL query, run the query in your SQL developer, or if not possible into a Clarity Lookup
2. Copy the Headers in your clipboard (SQL Developer), or from the "Browse Window" in your lookup
3. Paste the results / column names into the "Get attributes" tab. Remove unnecessary data, columns. Make sure the column names display vertically (use the copy / paste transpose function if needed).
4. Copy / paste the columns into the first column (A) of the Queries worksheet
5. Put the dimension name in cell B1
6. Put the SQLWRAPPER name (name you want to use for the query that will hold your SQL code) in cell B2
7. Column C contains the fact if the attribute is a dimension or not. Leave blank if not.
8. Column D contains the dimension name, leave blank if it should be the same as the main dimension name.
9. Column E contains the type of data (e.g. boolean). Leave blank to use IMPLIED (standard).
10. Copy the results (in this example from B4 to B35) and paste it in a NOTEPAD++
11. Add the following after the Pasted Data
FROM( <paste your SQL here>
) SQLWRAPWHERE @FILTER@
12. You're done. Copy/Paste this in your Clarity query.
You can do the same for lookups using the Lookups worksheet.
This tool is very simple, but lets you easily wrap SQL code into NSQL without the need to hassle with the syntax. It will not hold any logic in the NSQL but will wrap it around SQL code.
Hope that can help anyone as good as it helped me
Thank you for sharing this with the community Anthony!
NSQL Maker tool
the GEL Scripting site ( set up by chris.wuenstel ) also has some nice NSQL "helpers" ; GEL Scripting