Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
;
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.
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.
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.