4 Replies Latest reply: Jun 23, 2011 4:46 AM by david.morris RSS

    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.

        • Re: select * and joins
          Nagaian Krishnamoorthy

          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
              John Witherspoon

              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

                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.