topic select * and joins in QlikView

select * and joins

Wed, 22 Jun 2011 15:43:22 GMT

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.

Re: select * and joins

nagaiank — Wed, 22 Jun 2011 16:30:15 GMT

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
;

select * and joins

johnw — Wed, 22 Jun 2011 23:58:42 GMT

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.

select * and joins

Thu, 23 Jun 2011 08:41:48 GMT

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.

select * and joins

Thu, 23 Jun 2011 08:46:00 GMT

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.