Like operator in Query Reports Revised

Frappe Framework Customization
1

I want to use like operator in Query Reports but I am receiving some errors.

select
it.item_group as “Item Group”,
max(cast(mid(it.item_code,5,6) as unsigned)) as “Reference”,
concat(mid(it.item_group,1,4),lpad(cast(max(cast(mid(it.item_code,5,6) as unsigned)) as char),6,‘0’)) as “Latest Item Code”
from tabItem it
group by it.item_group
where it.item_group not like ‘T%’

OR

select
it.item_group as “Item Group”,
max(cast(mid(it.item_code,5,6) as unsigned)) as “Reference”,
concat(mid(it.item_group,1,4),lpad(cast(max(cast(mid(it.item_code,5,6) as unsigned)) as char),6,‘0’)) as “Latest Item Code”
from tabItem it
group by it.item_group
where it.item_group not like (‘T%’)

OR

select
it.item_group as “Item Group”,
max(cast(mid(it.item_code,5,6) as unsigned)) as “Reference”,
concat(mid(it.item_group,1,4),lpad(cast(max(cast(mid(it.item_code,5,6) as unsigned)) as char),6,‘0’)) as “Latest Item Code”
from tabItem it
group by it.item_group
where it.item_group not like T%

OR

select
it.item_group as “Item Group”,
max(cast(mid(it.item_code,5,6) as unsigned)) as “Reference”,
concat(mid(it.item_group,1,4),lpad(cast(max(cast(mid(it.item_code,5,6) as unsigned)) as char),6,‘0’)) as “Latest Item Code”
from tabItem it
group by it.item_group
where it.item_group not like “T%”

Also, I have tried
it.item_group NOT REGEXP ‘^T’

but it didn’t work.

2

Use “%%” in like condition:

select 
    it.item_group as "Item Group",
    max(cast(mid(it.item_code,5,6) as unsigned)) as "Reference",
    concat(mid(it.item_group,1,4),lpad(cast(max(cast(mid(it.item_code,5,6) as unsigned)) as char),6,'0')) as "Latest Item Code"
from tabItem it
where it.item_group not like 'T%%'
group by it.item_group
3 Likes
3

Thanks Nabin, it worked for me.