LEN function in SQL

[#1]

Hi Guys,

Can anyone think of a reason why the Len function doesn't work in SQL I'm using LEN(fieldname) just keeps telling me the function does not exist 😕

[happydays1967]

Max() and MaxString() are QV script functions. I am not quite clear on what you mean with 'a lot of linking statements'... This (or it's equivalent in SQL) is what I always use to get values like that one one line...

Sometimes you just have to split the beast up in chunks.... Load data, then do the next processing step on that data and so on. Hope that helps...

HP

[#3]

Its odd its telling me I cannot just group by service, id and I must group by all the other parameters, Am very stuck here !!!

[happydays1967]

group by is always over all non-aggregated fields.... can you send script so I can have a look?

HP

[#5]

thanks

SQL SELECT
 
  tmp_oap.orderatomid as service_id,
 
 
  if(tmp_ap.flagged_parameter=1,orderatomparam_displayval(tmp_oap.orderatomparamid),'') as parameter_flag1
 
//  if(tmp_ap.flagged_parameter =1, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag1,
//  if(tmp_ap.flagged_parameter =2, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag2,
//  if(tmp_ap.flagged_parameter =3, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag3,
//  if(tmp_ap.flagged_parameter =4, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag4,
// 
//  if(tmp_ap.flagged_parameter =5, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag5,
// 
//  if(tmp_ap.flagged_parameter =6, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag6,
//        if(tmp_ap.flagged_parameter =7, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag7
//       

        FROM
            orderatomparams tmp_oap
            INNER JOIN atomparams tmp_ap  ON (tmp_ap.atomparamid = tmp_oap.atomparamid)
            INNER JOIN orderatoms oa ON (tmp_oap.orderatomid = oa.orderatomid)         
        WHERE
            tmp_ap.flagged_parameter>='1'
          AND  tmp_oap.orderatomid = oa.orderatomid
          AND oa.daterecognised > '01/03/2015'
          AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL

AND 1=1
          
GROUP by tmp_oap.orderatomid, tmp_ap.flagged_parameter,tmp_oap.orderatomparamid
;

[happydays1967 · Accepted solution]

SQL SELECT

  tmp_oap.orderatomid as service_id,

  max(if(tmp_ap.flagged_parameter =1, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag1,

  max(if(tmp_ap.flagged_parameter =2, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag2,

  max(if(tmp_ap.flagged_parameter =3, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag3,

  max(if(tmp_ap.flagged_parameter =4, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag4,

  max(if(tmp_ap.flagged_parameter=1,orderatomparam_displayval(tmp_oap.orderatomparamid),'')) as parameter_flag1

  max(if(tmp_ap.flagged_parameter =5, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag5,

  max(if(tmp_ap.flagged_parameter =6, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag6,

  max(if(tmp_ap.flagged_parameter =7, orderatomparam_displayval(tmp_oap.orderatomparamid),'-')) as  parameter_flag7

FROM

  orderatomparams tmp_oap INNER JOIN atomparams tmp_ap ON (tmp_ap.atomparamid = tmp_oap.atomparamid)

  INNER JOIN orderatoms oa ON (tmp_oap.orderatomid = oa.orderatomid)         

WHERE

  --tmp_ap.flagged_parameter >= '1' AND -- then you will not get flag 3-7!!!!

--  tmp_oap.orderatomid = oa.orderatomid AND --> redundant clause, as this is already your join clause

  oa.daterecognised > '01/03/2015' AND

  orderatomparam_displayval(tmp_oap.orderatomparamid) IS NOT NULL

AND 1=1

GROUP BY

  tmp_oap.orderatomid

;

Try that...

HP

[#7]

Thanks for all your help,

its not liking the max for some reason, I also tried Maximum no luck.. ARGH !

[happydays1967]

What database is it you are querying?  (oracle, sql server, mysql?) AND is orderatomparam_displayval() a function? Because that is what it looks like in the statement.... That might be your issue....

What are you trying to do there?

HP

[#9]

Tried again and seems to work fine now . THANKYOU SO MUCH !

[happydays1967]

You're welcome Glad I could help.

HP