List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:February 13 2012 5:11pm
Subject:Re: weird difference in workbench and CLI query
View as plain text  
> When I run this query in workbench:
> select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date',
> s.ExamDate 'SJH Exam Date' from chestcon_log c
> left join exams e on CONCAT("000",c.acc_number) = e.LastWordAccNum
> left join sjhreports_ s on c.acc_number = s.AccessionNumber
> WHERE
> c.timestamp_exam <= CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL
> '-1' DAY) AS DATETIME) AND c.timestamp_exam >=
> CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS
> DATETIME)
> 
> I get this:
> 7330565	NULL	 2012-02-01
> 6604419	 2011-01-25	NULL
> 
> but when I run the same query in a shell script, on a Linux box or OS X, I
> get this: 7330565	NULL	2012-02-01
> 6604419	NULL	NULL

I see mixes of single ' and double "... And this is the line that fails, the 
only occurance of ":
    left join exams e on CONCAT("000",c.acc_number) = e.LastWordAccNum

Which my crystal ball tells me is probably a sign of improper escapes in shell 
scripts. What happens if you replace that line with:

    left join exams e on CONCAT('000',c.acc_number) = e.LastWordAccNum
-- 
Rik Wasmus
Thread
weird difference in workbench and CLI queryJames W. McNeely13 Feb
  • Re: weird difference in workbench and CLI queryPeter Brawley13 Feb
  • Re: weird difference in workbench and CLI queryRik Wasmus13 Feb
    • Re: weird difference in workbench and CLI queryJim McNeely13 Feb