Pivot object not being recognized by snowflake

Question

I have written a SQL code in snowflake to create a pivot table. I am using the object of the pivot table in another query but its not being recognized.

create or replace table "SCRATCH"."ASHISHKUMAR".pivoted2 as
SELECT * FROM
(SELECT
  sample_number,customer_sample_number, grower, farm, field, test_parameter,result, SYM, project_id
FROM
    "SCRATCH"."ASHISHKUMAR"."PIVOTED" test
)
PIVOT(
   avg (result)
   FOR test_parameter IN ('Falling Number',
'Damaged Kernels Total',
'Alpine Screen O/100',
'Width (W)',
'Area (135 min.)',
'Absorption',
'Peak',
'Tolerance (Stability)',
'Bread Score Total (100)',
'Weight',
'Ratio No. (Max) (135 min.)',
'Area (90 min.)',
'Ratio No. (R/E) (90 min.)',
'Resistance (90 min.)',
'Shrunken & Broken',
'Wheat of Other Classes',
'Alpine Screen O/45',
'Bake Absorption',
'Crumb Color (10)',
'Digital Image Analysis',
'Max. Resistance (135 min.)',
'0/9',
'Weight (Average)',
'Water',
'Dough Temp.',
'Volume 1',
'Top Grain Score',
'Extensibility (45 min.)',
'Resistance (45 min.)',
'Hardness (Std. Deviation)',
'Moisture (Std. Deviation)',
'Dry Gluten',
'T/9',
'Diameter (Std. Deviation)',
'Weight (Std. Deviation)',
'Contrasting Classes',
'Heat Damage',
'Alpine Screen O/140',
'Bake @ 425°',
'Mix 1 (straight dough)',
'Taste (10)',
'Uniformity (10)',
'Spread Factor (W/T)',
'Ratio No. (R/E) (45 min.)',
'Proof Time',
'Extensibility (90 min.)',
'Protein',
'Test Weight (kg/hl)',
'Total Defects',
'Alpine Screen T/325',
'Ash',
'Crust (10)',
'Symmetry (10)',
'B*',
'Extensibility (135 min.)',
'1000 Kernel Weight',
'Aroma (10)',
'Volume 2',
'L*',
'Ratio No. (Max) (90 min.)',
'Ratio No. (R/E) (135 min.)',
'0/7',
'Max. Resistance (45 min.)',
'Ochratoxin A (Elisa)',
'Yield (Experimental Milling)',
'Dockage',
'Grade',
'Alpine Screen O/200',
'Thickness (T)',
'Area (45 min.)',
'Max. Resistance (90 min.)',
'Lactic Acid',
'Starch Damage',
'M.T.I.',
'Wet Gluten',
'Gluten Index',
'Vomitoxin (Elisa)',
'Foreign Material',
'Test Weight (lb/bu)',
'Moisture',
'Alpine Screen O/325',
'Bread Score:',
'Grain (20)',
'Specific Volume',
'Texture (20)',
'Resistance (135 min.)',
'Moisture (Average)',
'Sucrose',
'Amylograph',
'Fermentation Time',
'Ratio No. (Max) (45 min.)',
'Diameter (Average)',
'Loaf Height',
'Sodium Carbonate',
'Hardness (Average)',
'A*'
)
) AS GPALPIVOT order by project_id;
select * from "PARTHENON"."AGRONOMY"."INDIGO_LOT_ALL" lots left join GPALPIVOT
  on lots.id ilike GPALPIVOT.customer_sample_number
left JOIN PARTHENON.ORACLE.WHEAT_HARVEST_2019_INVENTORY inv
   on GPALPIVOT.customer_sample_number = inv.subinv_description||'_'||split_part(inv.locator,'.',1)||'-'||split_part(inv.locator,'.',2);

I am getting an error while I am trying to run this. PFB the screenshot of the error:

Link of the screenshot of the error

I have tried everything, I am clueless what to do now. Can someone please throw some light on this?

Thanks for the quick response! My SQL code is running smoothly till the pivot statement, after that its showing the error as it is not able to find the pivot object. What is wrong with it? — Ashish Kumar
I did the same. Now I am getting this error: SQL compilation error: Object 'PIVOTED2' does not exist or not authorized. — Ashish Kumar
You need to fully qualify that with the schema that you used when you created the table. You are clearly not in that schema when you run your second statement. — Mike Walton

Stuart Ozer Stuart Ozer · Accepted Answer · 2019-11-19T23:51:08

GPALPIVOT is not the name of the table you created -- it is just an alias, internal to the original SELECT statement, for the table expression you constructed inside your first query. The table name to reference in your follow-up query should be "SCRATCH"."ASHISHKUMAR".pivoted2

Pivot object not being recognized by snowflake

1 Answers