4 Replies Latest reply: May 16, 2013 7:16 AM by Gysbert Wassenaar RSS

    MS Access Import data issue

    Sukhwant Matharoo

      hi all,

       

      i am importting 2 tables from same MS access and doing left join and using iF statement , but giving error. see below syntax:

       

      ODBC CONNECT To [MS Access Database; DBQ=path];

       

      raw:

      select

      category,

      [country of issue],

      [Coll Type],

      [buss code],

      if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region

      from table1;

       

      left join

       

      select

      category,

      value

      from table2;

       

       

      Gives error on:

      -If statement syntax

      -left join as ODBC Failed

       

      please advise

       

      thanks

        • Re: MS Access Import data issue
          m w

          The IF is QlikView syntax and can't be used inside a SQL Select statement.

           

          Do a preceding load and use the IF there.

            • Re: MS Access Import data issue
              Sukhwant Matharoo

              as i mentioned the import is from MS Acess, thus Load will not work. i have to use Select

               

              please advise

                • Re: MS Access Import data issue
                  m w

                  Something like this:

                   

                  raw:

                  load

                       *,

                  if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region;

                   

                  select

                  category,

                  [country of issue],

                  [Coll Type],

                  [buss code],

                  from table1;

                  • Re: MS Access Import data issue
                    Gysbert Wassenaar

                    MS-Access has the iif construct. Maybe this works:

                        

                         iif([buss code]='ufu', 'Europe',iif([buss code]='afu','Asia','')) as Region.

                     

                    It's possible you need to use double quotes instead of single quotes.

                    Alternatively you can use a preceding load:

                     

                    Table1

                    Load

                         category,[country of issue],[Coll Type],[buss code],

                         if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region;

                    Select category,[country of issue],[Coll Type],[buss code]

                    from table1;

                     

                    Why the left join fails is impossible to tell without more information. Do you also get an error if you load table two as a table like this:

                     

                    Table2:

                    select category, value from table2;