Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Shell Programming and Scripting
1

Hello All,

I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns.
So my requirement is to generate a output query which will have 20 columns all the times irrespective of number of input columns.

Sample input file

SELECT
A AS COL1,
COALESCE(B ,'123') AS COL2,
CAST(C AS TIMESTAMP(0)) AS COL3
FROM TABLE
WHERE X = Y AND Z > (SELECT A FROM TABLE2);

Expected output

SELECT
A AS COL1,
COALESCE(B ,'123') AS COL2,
CAST(C AS TIMESTAMP(0)) AS COL3
,NULL AS COL4
,NULL AS COL5
..
..
,NULL AS COL20
FROM TABLE
WHERE X = Y AND Z > (SELECT A FROM TABLE2);

Can someone one please help me achieve this in UNIX KSH/BASH?

Thanks in Advance

2

In shell or some flavor of procedural SQL?

3

Thanks for your reply.

Shell is the preferred. Because we are planning to use this script to work across multiple databases. But please provide PL SQL code also if you have any.

4

Load an array of 20 entries, "NULL Default_01" through "NULL Default_20". The read your file and replace the first entries in the array with column names from the file. Now, generate a query string from the array, and run it. What does input look like?

5

Identifying columns individually in the query to load into array is the problem I see here. Because I need to pick some delimiter for identifying columns. I can not choose ',' or 'AS' as they are used by other sql functions like coalesce and cast.
Also there is no guarantee that the columns will be one per line. They may include multiple columns in the same line.

Input file can be like this

SELECT
A AS COL1,COALESCE(B ,'123') AS COL2,CAST(C AS TIMESTAMP(0)) AS COL3
FROM TABLE
WHERE X = Y AND Z > (SELECT A FROM TABLE2);

or

SELECT
A AS COL1,
COALESCE(B ,'123') AS COL2,
CAST(C AS TIMESTAMP(0)) AS COL3
FROM TABLE
WHERE X = Y AND Z > (SELECT A FROM TABLE2);
6

So, just do a:

ct=0
for col in `cat file`
do
 cols[$ct]=$col
 (( ct++ ))
done
7

If i use your code each row will go into one array element. But how can i know how many columns are there in the sql query actually?

For example if we consider below row, how can i select col1, col2 and col3 in to 3 array elements instead of 1?

A AS COL1,COALESCE(B ,'123') AS COL2,CAST(C AS TIMESTAMP(0)) AS COL3[

Thanks

8

You need a sql knowlegable parser to normalize the target list. It is more than just comma separated, as commas can occur in other contexts, like function calls and quoted strings. Can you do something to make the users more disciplined in their input, like an entry tool?