1

I have a questionnaire form which stores the values of the form and could be made available (if not completed) for completion at a later time. I want a mechanism which tracks the percentage of the form's completion. I thought about calculating the percentage of incomplete fields compared to the total number of fields of the table. I want something like this:

SELECT * FROM tbapplication t WHERE t.* IS NOT NULL

The above SQL does not work. My table has more than 100 fields so I believe including all the fields and checking if IS NULL for each one is not the right way. Any ideas?

alwaysVBNET
  • 3,150
  • 8
  • 32
  • 65
  • when testing a column against a value (including the value null) you must list the column name in the query. This will make your query (with 100 column names) excessively huge. Instead, consider doing the null column count vs not-null collumn count in your non-db code. – DwB Feb 06 '14 at 16:46
  • 2
    "Including all the fields and checking if IS NULL" *is* the right way to go. You can use the `INFORMATION_SCHEMA.COLUMNS` table to get a list of all columns in the table, to help you formulate your query. – Gordon Linoff Feb 06 '14 at 16:46
  • Related http://stackoverflow.com/q/9079037/73226 – Martin Smith Feb 06 '14 at 16:49

2 Answers2

0

The IS NULL check cannot be done against every column in the table in the way you are trying.

The first question should maybe be should my table have 100 columns ?

I'm presuming you have a large number of answers for your survey and each one has a column of it's own ?

You need to redesign your tables and normalise the structure of your tables.

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • 1
    Not my downvote. Possibly someone disagreeing with your assessment that the table isn't normalised though. Which normalisation rule do you feel it violates? – Martin Smith Feb 06 '14 at 16:52
  • 1
    I didn't downvote you either, but your first sentence it's kind of ambiguous: "The IS NULL check cannot be done against every column in the table". It can be done if you explicitely name each column (ie: `col1 IS NOT NULL AND col2 IS NOT NULL...`), what you can't do is `table.* IS NOT NULL` – Lamak Feb 06 '14 at 16:54
  • I'm not saying it violates any to be honest. A form that feeds a table that has 100 columns and the op wants to track progress by checking if each column is null was the part that made me think there might be a column per answer ? I DV myself but I just thought this was a bit harsh. – SteveB Feb 06 '14 at 16:56
  • @Lamak - yep. Agree. What I meant was it can't be done against all columns in the form `where t.* IS NULL`. Edited. – SteveB Feb 06 '14 at 16:57
0

Unfortunately there is no way I could think of to ask SQL how many columns in specific row are currently NULL. Currently three solutions come to my mind, that could make you achieve what you want in more "general" way. You could stick to one of them:

  1. Every time a row is stored: calculate numbers of missing fields and store this information in dedicated table column.

  2. Generate your query automatically on the basis of information stored in syscolumns.

  3. (Possibly?) redesign your database, so that information is stored in related rows rather than in columns.

Kuba Wyrostek
  • 6,163
  • 1
  • 22
  • 40