https://netsuiteprofessionals.com logo
Join Slack
Powered by
I am running a query and I've come across 4 fields...
# suiteql
j
I am running a query and I've come across 4 fields that give me an error when i add them to the group by? Ive removed them all to get the query to work. Any suggestions as to why? Permissions?
j
what type of fields are they?
c
@Joseph Lee Can you share your query?
j
The fields are hyperlink fields.
Copy code
SELECT 
   item.itemid AS "Item", 
  item.upccode AS "Each Barcode", 
  item.description AS Description, 
  MAX(item.custitem_length) AS "Each Length", 
  ROUND(MAX(item.custitem_length * 2.54), 2) AS "Each Length CM", 
  MAX(item.custitem_width) AS "Each Width", 
  MAX(item.custitem_width * 2.54) AS "Each Width CM", 
  MAX(item.custitem_height) AS "Each Height", 
  MAX(item.custitem_height * 2.54) AS "Each Height CM", 
  MAX(item.custitem_weight) AS "Each Weight", 
  ROUND(MAX(item.custitem_weight * 453.59237),2) AS "Each Weight Grams", 
  item.custitem_top30rank AS Top30Rank, 
  item.custitem_mm_specifcation AS Specification, 
  item.custitem_mm_un3091 AS UN3091, 
  item.custitem_mm_un3481 AS UN3481, 
  item.custitem_mm_un1170 AS UN1170, 
  item.custitem_mm_prop_65 AS Prop65,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_country_of_origin AS CountryOfOrigin,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_formal_name AS FormalName,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_batteries_included AS BatteriesIncluded,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_life AS BatteryLife,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_type_used AS BatteryTypeUsed,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_extra_batteries_included AS ExtraBatteriesIncluded, 
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_1 AS BulletPoint1, 
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_2 AS BulletPoint2,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_3 AS BulletPoint3,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_4 AS BulletPoint4,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_5 AS BulletPoint5,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_spec_colors_available AS ColorsAvailable, 
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_feeling_sensation AS FeelingOrSensation,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_functions AS Functions,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_latex_pht_free AS Latexphthalatefree,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_material AS Material,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_one_size_fits_most AS OneSizeFitsMost,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_desc_short AS ProductDescriptionShort,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_long AS ProductDescriptionLong,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_type AS ProductType,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_retail_pack_type AS RetailUnitPackType,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_reusable AS Reusable,  
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_texture AS Texture, 
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_1 AS VideoLink1,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_2 AS VideoLink2,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_3 AS VideoLink3,
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS "inner barcode",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN BUILTIN.DF( CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit ) END ) AS "BX Alias Unit",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc END ) AS "BX Alias Description",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) AS "BX Inner Quantity",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length END) AS "BX Alias Length",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length * 2.54 END) AS "BX Alias Length CM",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width END) AS "BX_Alias_Width",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width * 2.54 END) AS "BX Alias Width CM",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height END) AS "BX_Alias_Height",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height * 2.54 END) AS "BX Alias Height CM",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight END) AS "BX Alias Weight",
ROUND(MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight * 453.59237 END), 2) AS "BX Alias Weight Grams", 
  MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS "master barcode",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN BUILTIN.DF( CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit ) END ) AS "MC Alias Unit",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc END ) AS "MC Alias Description",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) AS "Master Quantity",
(MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) / MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END)) AS "Inners Per MC",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length END) AS "MC Alias Length",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length * 2.54 END) AS "MC Alias Length CM",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width END) AS "MC Alias Width",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width * 2.54 END) AS "MC Alias Width CM",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height END) AS "MC Alias Height",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height * 2.54 END) AS "MC Alias Height CM",
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight END) AS "MC Alias Weight",
ROUND(MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight * 453.59237 END), 2) AS "MC Alias Weight Grams",
FROM 
  item 
  INNER JOIN CUSTOMRECORD_MM_PRODUCT_SPEC ON item.custitem_mm_specifcation = CUSTOMRECORD_MM_PRODUCT_SPEC.id 
  INNER JOIN CUSTOMRECORD_WMSSE_SKU_ALIAS ON item.id = CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_item 
WHERE 
  item.custitem_mm_saleable_item = 'T' 
  AND item.isinactive = 'F' 
  AND item.itemid NOT LIKE 'W%' 
GROUP BY
	item.itemid, item.upccode, item.description,  item.custitem_top30rank, item.custitem_mm_specifcation, item.custitem_mm_un3091, item.custitem_mm_un3481, item.custitem_mm_un1170, item.custitem_mm_prop_65, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_country_of_origin, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_formal_name, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_batteries_included, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_life, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_type_used, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_extra_batteries_included, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_1, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_2, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_3, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_4, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_5, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_spec_colors_available, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_feeling_sensation, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_functions, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_latex_pht_free, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_material, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_one_size_fits_most, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_desc_short, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_long, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_type, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_retail_pack_type, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_reusable, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_texture, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_1, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_2, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_3
ORDER BY 
  item.itemid
Its just a query to grab all the product data
j
Can you please create a stripped down version, maybe just a couple of grouped fields and a couple of MAX ones? This is a LOT to try to read/parse.
j
yes i will strip it to a few lines from each table
Copy code
SELECT 
   item.itemid AS "Item", 
  MAX(item.custitem_length) AS "Each Length", 
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_country_of_origin AS CountryOfOrigin,
MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS "inner barcode",
  MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS "master barcode",
FROM 
  item 
  INNER JOIN CUSTOMRECORD_MM_PRODUCT_SPEC ON item.custitem_mm_specifcation = CUSTOMRECORD_MM_PRODUCT_SPEC.id 
  INNER JOIN CUSTOMRECORD_WMSSE_SKU_ALIAS ON item.id = CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_item 
WHERE 
  item.custitem_mm_saleable_item = 'T' 
  AND item.isinactive = 'F' 
  AND item.itemid NOT LIKE 'W%' 
GROUP BY
	item.itemid, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_country_of_origin
ORDER BY 
  item.itemid
this query works but if i add • CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_medium • CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_1 • CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_2 • CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_3 it then gives me error invalid or unsupported
c
@Joseph Lee and those 4 fields you reference are in the main SELECT group without being included in an analytic or aggregate function?
j
yes
I just tried to run MAX on the fields and still nothing.
j
what happens if you do an ungrouped search including those?
are they stored values?
j
Copy code
SELECT 
item.itemid AS "Item",
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_medium AS Medium,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_1 AS Link1,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_2 AS Link2,
CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_3 AS Link3,
FROM 
  item 
  INNER JOIN CUSTOMRECORD_MM_PRODUCT_SPEC ON item.custitem_mm_specifcation = CUSTOMRECORD_MM_PRODUCT_SPEC.id 
  INNER JOIN CUSTOMRECORD_WMSSE_SKU_ALIAS ON item.id = CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_item 
WHERE 
  item.custitem_mm_saleable_item = 'T' 
  AND item.isinactive = 'F' 
  AND item.itemid NOT LIKE 'W%'
They def have stored values that i can access this way.
c
@Joseph Lee Have you tried adding them one a time to narrow down the specific culprit?
j
c
What ended up resolving the issue? @Joseph Lee
10 Views
Open in Slack
PreviousNext
Powered by