CODEWITHSUNDEEP
mysqlsqlhana
Analytics1988
Analytics1988

Reputation: 13

Dynamic fields in Select Statement in SQL

There are 2 tables to be compared.

Table 1 is the check table which contains all the fields:

TAB1
KEY F1  F2  F3  F4
1A  70  61  58  91
1B  61  97  73  53
1C  96  16  32  92

Table 2 is a subset of table 1 and is loaded from a different source. Ideally, the corresponding data in both tables should match but practically, there will be differences. For example, below, the value in column F3 for Key 1C is different..everything else is the same.

TAB2
KEY F1  F3
1A  70  58
1C  96  10

End output I need is a comparison

KEY F1_CHK  F1  F1_DELTA    F3_CHK  F3  F3_DELTA
1A  70      70    0           58    58    0
1C  96      96    0           32    10    22

so the select I am looking at is like

select TAB1.F1, TAB2.F1 , (TAB1.F1-TAB2.F1) , TAB1.F3, TAB2.F3 , (TAB1.F3-TAB2.F3)
FROM TAB1 INNER JOIN
     TAB2
     ON KEY = KEY

Now, the problem is that I need this solution to be dynamic. I mean the list of fields in the select I marked in bold above should be based on the fields of the subset table (TAB2)

Step 1 should be to get the list of fields in TAB1 Step 2 should be the select were you pick up those fields from TAB1 and TAB2 (Corresponding fields names will always be the same) and also calculate their difference. So basically the expected select statement should look like this. select dynamic list of fields based on TAB2 field list FROM TAB1 INNER JOIN TAB2 ON KEY = KEY

For the list of fields in TAB1, I can go to a metadata table and fetch it.. Step 2 confuses me.. Any help would be great..

Thanks!

Upvotes: 0

Views: 1612

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562811

The SQL language requires that the columns and tables you reference are set at prepare-time. There's no way around that.

So you need to write code that creates the SQL query in a string, and then run that query.

Here's how I would do it:

  1. Run an SQL query like SHOW COLUMNS FROM TAB2 to get the list of fields in that table.

  2. Start an array in your code that will become the select-list of columns for your query.

  3. For each field in the SHOW COLUMNS result, add an element to the select-list array.

  4. Form a new SQL query using the array as your query select-list.

Here's some quick (untested) Python code to do something like what I mean:

db = MySQLdb.connect(...)

cur = db.cursor(MySQLdb.cursors.DictCursor)
cur.execute("""SHOW COLUMNS FROM TAB2""")
select_list = []
for col in cur.fetchall():
    select_list.append('TAB1.{field}'.format(field=col['Field']))
    select_list.append('TAB2.{field}'.format(field=col['Field']))
    select_list.append('TAB1.{field}-TAB2.{field}'.format(field=col['Field']))

sql = """
    SELECT {select_list} 
    FROM TAB1 INNER JOIN TAB2 ON KEY = KEY
    """.format(select_list=','.join(select_list))

cur.execute(sql)
... read results ...

I know nothing about writing code for HANA, but the principle is the same. You need to write code to build up the SQL query, then execute it.

Upvotes: 1

Related Questions