select * and joins

[#1]

Is one able to run SQL queries with joins and use the * shorthand to return all columns in the select?? I have a query that runs fine on the database itself but I get an error when loading into qlikview. The SQL is along the lines of:

select *,
  case  
  stuff here
  end financialYear

from table1
     left outer join table2 on
     (linkid = linkid)
where
  stuff here
;

Should that be doable?

Many thanks,

Dave.

[nagaiank]

Dis you try using SQL prefix to your SELECT statement? e.g.

SQL select *,
  case  
  stuff here
  end financialYear

from table1
     left outer join table2 on
     (linkid = linkid)
where
  stuff here
;

[johnw · Accepted solution]

It may depend on the SQL implementation, but I believe you can EITHER select * or select a list of fields and expressions, but not both.  You could just put the case logic on the QlikView side in the load instead using nested if() functions.  But honestly, I recommend against using select *.  If you're grabbing ALL fields, then someone can add a field to the database that, say, causes a join you didn't want to occur.  People adding fields to a database shouldn't have to worry if some QlikView application is going to bomb out as a result.  And you shouldn't have to worry about people adding fields to a database.  Those should be independent.  The way you achieve that independence is by explicitly listing the columns you need and only the columns you need.

[#4]

Thanks for the response. I gave the SQL prefix a go, but it did not solve the issue. I think the solution is as John mentions.

[#5]

Many thanks for the advice John. It seems you are correct about the use of *, and specific columns mixed. It loads fine with specific field names only.