Hi Guys,
Need your help in getting list of unused custom field. I have run the following SQL query but giving an error. Kindly suggest :-
Error :- The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Query Souce :- https://confluence.atlassian.com/enterprise/managing-custom-fields-in-jira-effectively-945523781.html
select count(*), customfield.id, customfield.cfname, customfield.descriptionfrom customfield left join customfieldvalue on customfield.id = customfieldvalue.customfieldwhere customfieldvalue.stringvalue is null and customfieldvalue.numbervalue is null and customfieldvalue.textvalue is nulland customfieldvalue.datevalue is nullgroup by customfield.id, customfield.cfname, customfield.description;
Hi,
It seems that you're using a MSQL database so i think this threah might help you
https://stackoverflow.com/questions/14979413/the-text-ntext-and-image-data-types-cannot-be-compared-or-sorted-except-whe
In PGSQL the SQL is working fine.
Hi @Mohamed Benziane I have tried to add as NVARCHAR but getting error :- Incorrect syntax near the keyword 'as'.
select count(*), customfield.id, customfield.cfname, customfield.descriptionfrom customfield left join customfieldvalue on customfield.id = customfieldvalue.customfieldwhere customfieldvalue.stringvalue is null and customfieldvalue.numbervalue is null and customfieldvalue.textvalue is nulland customfieldvalue.datevalue is nullgroup by (customfield.id AS NVARCHAR(MAX)), (customfield.cfname AS NAVRCHAR(MAX)), (customfield.description AS NAVRCHAR(MAX));
Ah, the joys of running SQL against a Jira database. It's a horrible thing to do.
I'm afraid your SQL won't work. You're never going to get any results from it, even if it is correct.
You can't look for empty custom fields this way, because the data is not there, and you can't search for something that isn't there.
Customfieldvalue does not hold nulls for empty custom fields, it doesn't have a row at all.
Imagine I've got an issue with these fields set:
Then my customfieldvalue table will be something like
Here is the working query for MySQL Database , which give the list of custom field which are EMPTY :-
select count(*), customfield.id, customfield.cfname, cast([customfield].[description] as varchar(100)) as description fromcustomfield left join customfieldvalue oncustomfield.id = customfieldvalue.customfieldwhere customfieldvalue.stringvalue is nulland customfieldvalue.numbervalue is null and customfieldvalue.textvalue is null and customfieldvalue.datevalue is null group by customfield.id, customfield.cfname, cast(customfield.[description] as varchar(100));
It looks like you're new here. Sign in or register to get started.
