Re: create multiple tables in a single query

[joe j]

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 <vegiv...@tuxera.be> wrote:
> ----- Original Message -----
>> From: "joe j" <joe.st...@gmail.com>
>>
>>
>> /*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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql