Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

Whats wrong wit this SQL

CREATE TABLE IMCCRP.IMC_PARTINTSPECTEMPLATE_MQT AS ( SELECT pa.partinterchange_id, replace(replace(replace(pi.to_part_number, ' ' , '' ),'.', ''),'-','') AS unspacedpn,  MAX(CASE WHEN Identifier = 'field_1' THEN VALUE END) AS Sequence_Group, MAX(CASE WHEN Identifier = 'field_2' THEN VALUE END) AS Class_Code, MAX(CASE WHEN Identifier = 'field_3' THEN VALUE END) AS Dealer_List, MAX(CASE WHEN Identifier = 'field_4' THEN VALUE END) AS Dealer_Net FROM imccrp.imc_partinterchg_attr pa inner join imccrp.attr at on pa.attr_id=at.attr_id inner join imccrp.attrvaldesc av on pa.attrval_id=av.attrval_id inner join imccrp.imc_partinterchg pi on pa.partinterchange_id=pi.partinterchange_id where pi.interchange_type = 'MB' and av.language_id=-1 GROUP BY pa.partinterchange_id ) DATA INITIALLY DEFERRED REFRESH DEFERRED

Error:
 42803(-119)[IBM][CLI Driver][DB2/LINUXX8664] SQL0119N  An expression starting with "TO_PART_NUMBER" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.  SQLSTATE=42803
0
vvsrk76
Asked:
vvsrk76
1 Solution
 
momi_sabagCommented:
try

CREATE TABLE IMCCRP.IMC_PARTINTSPECTEMPLATE_MQT AS ( SELECT pa.partinterchange_id, replace(replace(replace(pi.to_part_number, ' ' , '' ),'.', ''),'-','') AS unspacedpn,  MAX(CASE WHEN Identifier = 'field_1' THEN VALUE END) AS Sequence_Group, MAX(CASE WHEN Identifier = 'field_2' THEN VALUE END) AS Class_Code, MAX(CASE WHEN Identifier = 'field_3' THEN VALUE END) AS Dealer_List, MAX(CASE WHEN Identifier = 'field_4' THEN VALUE END) AS Dealer_Net FROM imccrp.imc_partinterchg_attr pa inner join imccrp.attr at on pa.attr_id=at.attr_id inner join imccrp.attrvaldesc av on pa.attrval_id=av.attrval_id inner join imccrp.imc_partinterchg pi on pa.partinterchange_id=pi.partinterchange_id where pi.interchange_type = 'MB' and av.language_id=-1 GROUP BY pa.partinterchange_id ,replace(replace(replace(pi.to_part_number, ' ' , '' ),'.', ''),'-','')) DATA INITIALLY DEFERRED REFRESH DEFERRED
0
 
vvsrk76Author Commented:
Thank you So Much
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now