0

Hello I have problem with this query

INSERT INTO radacct 
    (acctsessionid,acctuniqueid,username, 
    realm,nasipaddress,nasportid, nasporttype,acctstarttime,
    acctupdatetime, acctstoptime,acctsessiontime, acctauthentic,
     connectinfo_start,connectinfo_stop, acctinputoctets, 
     acctoutputoctets,calledstationid, callingstationid, 
     acctterminatecause,servicetype,framedprotocol, 
     framedipaddress,dup,t_session, nas_name,groupname,
     percent_q,down_uq,up_uq,all_tu,up_dq,dwon_dq,time_dq,t_update)
     VALUES ('80a000cc', '678593b487e8a42f68685f896b9d6d5d',
     '1', '', '10.40.47.1', 'WAN6', 'Wireless-802.11', 
     DATE_SUB('2019-05-18 03:06:18' , INTERVAL 10934 SECOND), 
     CURRENT_TIMESTAMP(), NULL, 10934, '', '', '',
     '0' << 32 | '29802288', '0' << 32 | '739955022', 
     'hotspot1', '3C:D9:2B:50:B2:BC', '', '', '', '10.40.47.44',
     '80a000cc', unix_timestamp(), 'Router 21',
     COALESCE((SELECT groupname FROM `radusergroup` WHERE username = '1' ORDER BY `id` DESC LIMIT 1),'0'),
     COALESCE((SELECT userinfo.percent_now,SUM(r1.acctoutputoctets * (r1.percent_q/100)),SUM(r1.acctinputoctets * (r1.percent_q/100)),SUM(`r1`.`acctsessiontime`) FROM radacct as `r1` INNER JOIN userinfo ON (userinfo.username=r1.username) WHERE r1.username = '1' AND UNIX_TIMESTAMP(STR_TO_DATE(r1.acctstarttime, '%Y-%m-%d %H:%i:%s')) >= userinfo.updatedate),(SELECT 100,SUM(r2.acctoutputoctets),SUM(r2.acctinputoctets),SUM(`r9`.`acctsessiontime`) FROM radacct as `r2` INNER JOIN radcheck ON (radcheck.username=r2.username AND radcheck.is_card ='1' AND `radcheck`.`attribute`='Cleartext-Password' ) WHERE r2.username = '1')), 
     COALESCE((SELECT SUM(`r5`.`acctinputoctets` * (r5.percent_q/100)),SUM(`r6`.`acctoutputoctets` * (r6.percent_q/100)),SUM(`r6`.`acctsessiontime`)  FROM `radacct` as `r5` WHERE  DATE_FORMAT(STR_TO_DATE(`r5`.`acctstarttime`,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') AND r5.username = '1'),(SELECT 0,0,0)), '')
1
  • 1
    The error message that you are getting is missing! You should count then number of fields after 'radacct(', and before the ')', and that count should match the number of fields after 'VALUES(', en before the final ')'.
    – Luuk
    May 18, 2019 at 7:55

1 Answer 1

1

The error is due to the last two COASLESCE() function in your query:

For an example, you are expecting 3 values from the below COALESCE() function, but it doesn't

COALESCE((
        SELECT SUM(`r5`.`acctinputoctets` * (r5.percent_q / 100)),
               SUM(`r6`.`acctoutputoctets` * (r6.percent_q / 100)),
               SUM(`r6`.`acctsessiontime`)
        FROM `radacct` AS `r5`
        WHERE DATE_FORMAT(STR_TO_DATE(`r5`.`acctstarttime`, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
            AND r5.username = '1'
        ), (
        SELECT 0,
            0,
            0
        )),

You need to change it to individual select value, then it will work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.