2

I have the following stored procedure code:

    SET @sql=NULL;
    SET SESSION group_concat_max_len = 1000000;

    Select group_concat( DISTINCT if(monthname(date_vis) is null, 
    CONCAT('max(if (monthname(date_vis) is null, state, 0)) as ''NULL'' '),
    CONCAT('max(if (monthname(date_vis)=''', monthname(date_vis), ''', week_total, "N/A")) 
as ''', monthname(date_vis), ''' ')) )
    into @sql from mon join (SELECT @sql:='')a WHERE agent_id = a1 ORDER BY MONTH(date_vis);
    set @sql = concat('SELECT CONCAT(doc.firstname," ",doc.lastname), ' , @sql , ' , 
    SUM(CASE When a.visit_status=1 Then 1 Else 0 End ) as total_p, 
    SUM(CASE When a.visit_status=0 OR a.visit_status="N/A" Then 1  Else 0 End ) as total_a 
    FROM  (visit a  INNER JOIN agent age on (a.agent_id = age.agent_id )
    JOIN doctor doc on(a.doc_id=doc.doc_id))  WHERE  a.agent_id = ', a1, ' 
    AND visit_status !=""
    GROUP BY a.doc_id 
    ORDER BY month(a.date_vis)');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

It will return an error saying Unknown column 'week_total' . And here is the view table called mon containing the said column (where week_total is only in mon).

enter image description here

Query for the view:

  CREATE VIEW `mon`  AS  select month(`visit`.`date_vis`) AS `w`,
sum(`visit`.`visit_status`) AS `week_total`,`visit`.`visit_status` AS `visit_status`,
`visit`.`date_vis` AS `date_vis`,
`visit`.`agent_id` AS `agent_id` 
from `visit` group by `visit`.`doc_id`,
month(`visit`.`date_vis`) ;

Please help...

kurwaaa
  • 41
  • 7
  • What are the names of the columns returned when you query 'SELECT * FROM mon limit 1'? – Matt Jameson Oct 04 '18 at 08:54
  • @Matt It returns the ff. : w, week_total, visit_status, date_vis, agent_id – kurwaaa Oct 05 '18 at 01:58
  • does this work? SELECT week_total FROM mon – Matt Jameson Oct 05 '18 at 07:18
  • @Matt yes it works. – kurwaaa Oct 05 '18 at 07:52
  • @Matt I think it has something to do with this problem, and now I can't seem to get around it. https://stackoverflow.com/questions/15989529/unknown-column-in-field-list-but-column-does-exist – kurwaaa Oct 05 '18 at 07:53
  • 1
    When your're concatenating you seem to have 3 single quote marks in some place ('''), could this be an issue? id have thought that would throw an error – Matt Jameson Oct 05 '18 at 13:29
  • @Matt that's what I thought too, but I tried using other column and it executes fine.. – kurwaaa Oct 06 '18 at 01:06
  • I've never come across syntax like monthname(date_vis)=''' and it work. I've just ran your code on my local db and it throws an error about syntax – Matt Jameson Oct 08 '18 at 07:54
  • @Matt Thank you so much for your time , it actually runs, and I found a way to make it work but it wasn't outputting the right results. I just recently found another workaround. Again, much appreciation to you. – kurwaaa Oct 08 '18 at 09:27

0 Answers0