List:General Discussion« Previous MessageNext Message »
From:joe j Date:April 5 2012 9:32am
Subject:Re: create multiple tables in a single query
View as plain text  
Thanks, Johan.

I run the script in Unix indeed-don't know why I said Dos:)

The problem is caused (at least that is the sense I get, so far) when
I use some conditional statements inside "case when" as:

(case when (country='${x}' AND var2<>"") then 1 else 0 end)

The problem lies with the "<>" part, but there is no issue if 'var2 IS
NOT NULL'  is used, for example.

I have one or two variables for which I have used this condition. If
the script is run in mysql (that is for a single table) it works, so
this seems a bit strange. Probably I could convert empty cells to NULL
and then use 'IS NOT NULL' condition as an alternative (this works
now).

Yes, I've now removed back ticks now, but it seems I need to use
single quotes for '${x}'-without the single quotes I get error
messages.

On Thu, Apr 5, 2012 at 10:50 AM, Johan De Meersman <vegivamp@stripped> wrote:
> ----- Original Message -----
>> From: "joe j" <joe.stata@stripped>
>>
>>
>> /*STEP 2. from the dos prompt?*/
>>
>> for x in UK ZA IN CN;
>> do mysql  -ujoe -p -e "INSERT INTO  `table_new`
>> SELECT var1, var2`, (case when (country='${x}') then 1 else 0 end) AS
>> citizen
>> ,'${x}' AS ctry
>> FROM `'${x}'_table`
>> LEFT JOIN
>> WORLD
>> USING(YEAR)";
>>
>> For some reasons step2 is not working.
>
> Could you, perphaps, specify the reasons? Errormessages et al :-)
>
>> I assume step 2 must be run from DOS prompt (my mysql runs on a
>> server, and I am using PuTTY).  Any thoughts?
>
> DOS? Ick. Heathen.
>
> Anyway, that loop won't work on a DOS prompt - it's Unix stuff. I suppose you can
> achieve similar things with Powershell, but that's going to be distinctly not *my* problem
> :-)
>
> If your server is running on a Unix, you'll of course need to run it there. The
> backticks will be an issue inside double quotes, though, and I don't see any need for
> quoting. There's something weird about your query, though...
>
> INSERT INTO  `table_new`
>  SELECT var1, var2`, (case when (country='${x}') then 1 else 0 end) AS citizen
> ,'${x}' AS ctry
>    FROM `'${x}'_table`
>      LEFT JOIN WORLD USING(YEAR)
>
> There seem to be an extraneous backtick right after var2? Also, no need to quote the
> ${x} bit - the shell will substitute that before MySQL sees it. Definitely remove all the
> backticks from the expression - those mean "execute this here".
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
Thread
create multiple tables in a single queryjoe j3 Apr
  • Re: create multiple tables in a single queryhsv4 Apr
    • Re: create multiple tables in a single queryRick James4 Apr
      • Re: create multiple tables in a single queryJohan De Meersman5 Apr
  • Re: create multiple tables in a single queryJohan De Meersman4 Apr
    • Re: create multiple tables in a single queryjoe j4 Apr
      • Re: create multiple tables in a single queryjoe j4 Apr
        • Re: create multiple tables in a single queryJohan De Meersman5 Apr
          • Re: create multiple tables in a single queryjoe j5 Apr
            • Re: create multiple tables in a single queryjoe j5 Apr
              • Re: create multiple tables in a single queryJohan De Meersman6 Apr
        • Re: create more tables in a single queryhsv5 Apr
Re: create multiple tables in a single queryJan Steinman5 Apr