Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

select * and joins

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.

1 Solution

Accepted Solutions
MVP
MVP

select * and joins

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 Replies
nagaiank
Valued Contributor III

Re: select * and joins

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
;

MVP
MVP

select * and joins

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.

Not applicable

select * and joins

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.

Not applicable

select * and joins

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. 

Community Browser