You must provide a sku_id with tblAllergenInfo as ( select distinct #url.sku_id# linkId, * from ( select LevelofContainment_#_col# = ( select ai.levelOfContainment from tbl_PRD_AllergenInfo ai with (nolock) where ai.ownerType = and ai.ownerid = and ai.linkid = and ai.linkType = 'sku' and ai.allergenId = ) , ) as x ) select s.sku, s.skuid, s.skuDesc, s.skuType, s.schoolProduct, s.ItemNameLong, CAST(master_unit_qty as varchar) + ' ' + master_unit_desc as pack_size, s.brandId as brand_id, s.prodId as category_id, p.product as category_code, shortDesc as category_name, longDesc as category_description, s.ItemNameLong, c.categoryid as level3_id, c.catnum as level3_code, c.category as level3_name, c.description as level3_description, pl.ProdLine_ID as level4_id, pl.productcode as level4_code, pl.productline as level4_name, pak.shelf_life, pak.upc, pak.pallet_high, pak.pallet_tie, pak.scc14 as gtin, pak.MASTER_UNIT_QTY as unit_quantity, pak.MASTER_UNIT_SIZE + ' ' + pak.MASTER_UNIT_SIZE_MEASURE as unit_size, pak.MASTER_NET_WEIGHT + ' ' + pak.MASTER_NET_WEIGHT_UNIT as case_net_weight, pak.MASTER_GROSS_WEIGHT + ' ' + pak.MASTER_GROSS_WEIGHT_UNIT as case_gross_weight, pak.MASTER_LENGTH + ' ' + pak.MASTER_LENGTH_UNIT as case_length, pak.MASTER_WIDTH + ' ' + pak.MASTER_WIDTH_UNIT as case_width, pak.MASTER_HEIGHT + ' ' + pak.MASTER_HEIGHT_UNIT as case_height, pak.MASTER_CUBE + ' ' + pak.MASTER_CUBE_UNIT as case_cube, pak.CAT_NUM, pak.CLASS_NUM, pak.SCC14, pak.MASTER_UNIT_QTY, pak.MASTER_UNIT_SIZE, pak.MASTER_UNIT_DESC, pak.MASTER_UNIT_SIZE_DESC, pak.MASTER_UNIT_SIZE_MEASURE, pak.MASTER_PACK, pak.MASTER_GROSS_WEIGHT, pak.MASTER_GROSS_WEIGHT_UNIT, pak.MASTER_NET_WEIGHT, pak.MASTER_NET_WEIGHT_UNIT, pak.MASTER_NET_WEIGHT_TYPE, pak.MASTER_HEIGHT, pak.MASTER_HEIGHT_UNIT, pak.MASTER_WIDTH, pak.MASTER_WIDTH_UNIT, pak.MASTER_LENGTH, pak.MASTER_LENGTH_UNIT, pak.MASTER_CUBE, pak.MASTER_CUBE_UNIT, pak.MASTER_TARE, pak.MASTER_TARE_UNIT, pak.KOSHER, pak.KOSHER_COMMENTS, pak.CATCH_WEIGHT, pak.STORAGE_TEMP, pak.STORAGE_TEMP_UNIT, pak.STORAGE_TEMP_MIN, pak.STORAGE_TEMP_UNIT_MIN, pak.PRODUCT_STABILITY, pak.SHELF_LIFE_AMBIENT, pak.SHELF_LIFE_REFRIG, pak.SHELF_LIFE_FROZEN, pak.GROWING_METHOD_CODE, pak.SERVINGS_PER_CASE, pak.SERVING_SIZE, pak.SUB_MASTER_UNIT_QTY, pak.SUB_MASTER_UNIT_DESC, pak.SUB_MASTER_UNIT_SIZE, pak.SUB_MASTER_UNIT_SIZE_MEASURE, pak.SUB_MASTER_UNIT_SIZE_DESC, pak.PACKAGE_UNIT_QTY, pak.PACKAGE_UNIT_DESC, pak.PACKAGE_UNIT_SIZE, pak.PACKAGE_UNIT_SIZE_MEASURE, pak.PACKAGE_UNIT_SIZE_MEASURE_TYPE, pak.PACKAGE_UNIT_SIZE_DESC, pak.ITEM_UNIT_QTY, pak.ITEM_UNIT_DESC, pak.ITEM_UNIT_SIZE, pak.ITEM_UNIT_SIZE_MEASURE, pak.ITEM_UNIT_SIZE_DESC, pak.PALLET_EXTRA, pak.TOTAL_PALLET, pak.PALLET_UPC, pak.PALLET_CUBE, pak.PALLET_CUBE_UNIT, pak.PALLET_WEIGHT, pak.PALLET_WEIGHT_UNIT, pak.PALLET_NOTES, pak.ucc, pak.uccType, pak.MASTER_DRAINED_WEIGHT, pak.MASTER_DRAINED_WEIGHT_UNIT, n.comments as servings_size_text, n.WeightSrvGrams + ' ' + n.UnitMeasure as serving_size, n.UnitPerSrv as ServingDesc, n.UnitMeasure as ServingType, n.WeightSrvGrams as ServingSizeWeight, n.UnitDescription as ServingSizeWeightType, n.SrvPerContainer as ServingsPerContainer, n.SrvPerContainerText as ServingsPerContainerText, n.fat_cal as calories_from_fat, n.fat as total_fat, n.saturatedfat as saturated_fat, n.saturatedfat_pdv as saturated_fat_pdv, n.transfat as trans_fat, '' trans_fat_pdv, n.CARBS as total_carbs, n.CARBS_PDV as total_carb_pdv, n.FIBER as dietary_fiber, n.FIBER_PDV as dietary_fiber_pdv, n.sugar as sugars, n.addedSugar as added_sugars, n.addedSugar_PDV as added_sugars_pdv, n.vitamina as vitamin_a, n.vitaminc as vitamin_c, n.comments as nutrition_comments, n.UnitMeasure, n.UnitDescription, n.UnitPerSrv, n.WeightSrvGrams, n.SrvPerContainerText, n.SrvPerContainer, n.Equv_Grams, n.UnitPerSrv_Equv, n.UnitMeasure_Equv, n.Alcohol, n.Ash, n.Ash_Equv, n.Arginine, n.Arginine_equv, n.Biotin, n.Biotin_Equv, n.Caffeine, n.Calcium, n.Calcium_Type, n.Calcium_Equv, n.Calcium_PDV, n.Calcium_Type_Equv, n.Calories, n.Calories_Equv, n.Calories_PDV, n.Carbs, n.Carbs_Equv, n.Carbs_PDV, n.Carbs_other, n.Carbs_other_equv, n.Cholesterol, n.Cholesterol_Equv, n.Cholesterol_PDV, n.Choline, n.Choline_Type, n.Choline_Equv, n.Choline_Type_Equv, n.Copper, n.Copper_Equv, n.Copper_PDV, n.Chromium, n.Chromium_Equv, n.Energy, n.Energy_Equv, n.Fat, n.Fat_Equv, n.Fat_PDV, n.Fat_Cal, n.Fat_Cal_Equv, n.Fat_Cal_PDV, n.Fiber, n.Fiber_Equv, n.Fiber_PDV, n.Fiber_Soluble, n.Fiber_Soluble_Equv, n.Fiber_Soluble_PDV, n.Fiber_Insoluble, n.Fiber_Insoluble_Equv, n.Fiber_Insoluble_PDV, n.Folate, n.Folate_Equv, n.Folate_PDV, n.Iodine, n.Iodine_Equv, n.Iron, n.Iron_Type, n.Iron_Equv, n.Iron_Type_Equv, n.Iron_PDV, n.Magnesium, n.Magnesium_Equv, n.Magnesium_PDV, n.Manganese, n.Manganese_Equv, n.Moisture, n.Moisture_Equv, n.Molybdenum, n.Molybdenum_Equv, n.MonounsatFat, n.MonounsatFat_Equv, n.MonounsatFat_PDV, n.Niacin, n.Niacin_Equv, n.Niacin_PDV, n.PantoAcid, n.PantoAcid_Equv, n.PantoAcid_PDV, n.Phosphorous, n.Phosphorous_Equv, n.Phosphorous_PDV, n.Potassium, n.Potassium_Equv, n.Potassium_PDV, n.PolyunsatFat, n.PolyunsatFat_Equv, n.PolyunsatFat_PDV, n.Protein, n.Protein_Equv, n.Protein_PDV, n.Riboflavin, n.Riboflavin_Type, n.Riboflavin_Equv, n.Riboflavin_Type_Equv, n.Riboflavin_PDV, n.SaturatedFat, n.SaturatedFat_Equv, n.SaturatedFat_PDV, n.Selenium, n.Selenium_Equv, n.Sodium, n.Sodium_Equv, n.Sodium_PDV, n.Sugar, n.Sugar_Equv, n.Sugar_PDV, n.SugarAlcohol, n.Thiamin, n.Thiamin_Equv, n.Thiamin_PDV, n.Transfat, n.Transfat_Equv, n.Transfat_PDV, n.VitaminA, n.VitaminA_Type, n.VitaminA_Equv, n.VitaminA_PDV, n.VitaminA_Type_Equv, n.VitaminB6, n.VitaminB6_Equv, n.VitaminB6_PDV, n.VitaminB12, n.VitaminB12_Equv, n.VitaminB12_PDV, n.VitaminC, n.VitaminC_Equv, n.VitaminC_PDV, n.VitaminC_Type, n.VitaminC_Type_Equv, n.VitaminD, n.VitaminD_Equv, n.VitaminD_PDV, n.VitaminE, n.VitaminE_Type, n.VitaminE_Equv, n.VitaminE_Type_Equv, n.VitaminE_PDV, n.VitaminK, n.VitaminK_Equv, n.VitaminK_PDV, n.Zinc, n.Zinc_Equv, n.Zinc_PDV, #_subFolderUrl# + '/' + c_#_colAlias#.mainFolder + '/' + c_#_colAlias#.subFolder + '/' + c_#_colAlias#.fileName as #_imageAlias#, s.CustomFld_1 as Recipe_Image_URL, coalesce(s.foodservice_rewards,'N') as foodservice_rewards, allergens.LevelofContainment_carrots, allergens.LevelofContainment_celery, allergens.LevelofContainment_glutenCereals, allergens.LevelofContainment_cocoa, allergens.LevelofContainment_coriander, allergens.LevelofContainment_corn, allergens.LevelofContainment_eggs, allergens.LevelofContainment_fish, allergens.LevelofContainment_lupine, allergens.LevelofContainment_milk, allergens.LevelofContainment_molluscs, allergens.LevelofContainment_mustard, allergens.LevelofContainment_peanuts, allergens.LevelofContainment_podFruits, allergens.LevelofContainment_rye, allergens.LevelofContainment_sesameSeeds, allergens.LevelofContainment_shellfish, ''as shellfish_type, allergens.LevelofContainment_soybeans, allergens.LevelofContainment_sulphites, allergens.LevelofContainment_treenuts, ''as treenuts_type, allergens.LevelofContainment_wheat, allergens.LevelofContainment_crustaceans, allergens.LevelofContainment_bioEngineered, allergens.LevelofContainment_whole_grain, allergens.LevelofContainment_gluten, allergens.LevelofContainment_gmo, allergens.LevelofContainment_poppy, s.customFld_1, s.customFld_2, s.customFld_3, s.customFld_4, s.customFld_5, s.customFld_6 , s.customFld_5 as 'Label_Reform' , s.customFld_1 as 'CN_Total_Weight', s.customFld_2 as 'CN_Credit_Amt', s.customFld_3 as 'CN_Certifier_Title', s.customFld_4 as 'CN_Certified_Date', s.customFld_5 as 'CN_Certified_Phone', 'http://doclibrary.com/MFR405/IMG/cn-specsheet-signature.png' as cn_specsheet_signature ---> from tbl_PRD_Products p with (nolock) inner join tbl_PRD_Skus s with (nolock) on p.prodId = s.prodId left outer join tbl_PRD_Categories c with (nolock) on c.categoryid = p.categoryid and c.fsl_TableCode = p.fsl_tableCode and c.ownerid = p.ownerid left outer join tbl_MFR_ProductLines pl with (nolock) on pl.mfr_id = c.ownerid and c.fsl_TableCode = 'MFR' and c.prodlineid = pl.prodline_id left outer join tbl_PRD_Packaging pak with (nolock) on s.skuId = pak.linkId and pak.linkType = 'SKU' left outer join tblAllergenInfo allergens with (nolock) on allergens.linkId = s.skuid left outer join tbl_PRD_Images img_#i# with (nolock) on img_#i#.ownerId = and img_#i#.ownerType = and img_#i#.LinkType = 'SKU' and img_#i#.linkId = s.skuId and img_#i#.imageCode = '#i#' left outer join tbl_FSC_Content c_#i# with (nolock) on c_#i#.contentId = img_#i#.ContentId left outer join tbl_FSC_SubFolders f_#i# with (nolock) on f_#i#.subFolderCode = c_#i#.subFolder left outer join tbl_PRD_Nutrition n on n.OwnerId = and n.fsl_tablecode = and n.NutType = 'SKU' and n.linkId = s.skuid where p.fsl_tablecode = and p.ownerId = and s.skuid = and s.unpublishDate >= #CreateODBCDate(Now())# and #CreateODBCDate(Now())# >= s.publishdate
Sku not found.
 QueryAddColumn(qmd_skuDetail, "factPanelImageURL", "varchar", arrayNew(1)); QuerySetCell(qmd_skuDetail, "factPanelImageURL", "#_imgPath##variables.imgName##variables.imgExt#",1); select m.memoData, dataName = case when m.memoType = 'D001' then 'long_description' when m.memoType = 'D002' then 'cooking_instructions' when m.memoType = 'D003' then 'serving_suggestion' when m.memoType = 'D004' then 'product_benefits' when m.memoType = 'D005' then 'ingredients' when m.memoType = 'D006' then 'other_information' when m.memoType = 'D020' then 'packaging_storage' when m.memoType = 'D033' then 'product_formulation_statement' when m.memoType = 'FSE1' then 'additional_information' else REPLACE(mt.Description, ' ', '') end from tbl_PRD_Memo m with (nolock) inner join tbl_PRD_MemoTypes mt with (nolock) on m.memoType = mt.code where m.fsl_TableCode = and m.ownerId = and m.linkId = and m.linkType = 'SKU' order by dataName queryAddColumn( qmd_skuDetail, "#dataName#", "varchar", ArrayNew(1) ); querySetCell(qmd_skuDetail,"#dataName#","#qmd_getMemoData.memoData#",1); queryAddColumn( qmd_skuDetail, "#_c#", "varchar", ArrayNew(1) ); querySetCell(qmd_skuDetail,"#_c#","#qmd_getMemoData.memoData#",1); select name from tbl_PRD_Fields with (nolock) where ownerType = and ownerId = order by sortrank select #_fieldNames# from tbl_PRD_OtherAttributes with (nolock) where ownerType = and ownerId = and linkId = and linkType = 'SKU' queryAddColumn( qmd_skuDetail, col, "varchar", ArrayNew(1) ); querySetCell(qmd_skuDetail,col,"#qmd_getFlexData["#col#"][1]#",1); queryAddColumn( qmd_skuDetail, "cn_specsheet_signature", "varchar", ArrayNew(1) ); if (request.https eq "on"){ querySetCell(qmd_skuDetail,"cn_specsheet_signature","https://www.appslibrary.com/assets/MFR405/IMG/cn-specsheet-signature.png",1); }else{ querySetCell(qmd_skuDetail,"cn_specsheet_signature","http://doclibrary.com/MFR405/IMG/cn-specsheet-signature.png",1); } SELECT recipeId FROM tbl_REC_Items WHERE CustomFld_1 like AND MFR_ID = QueryAddColumn(qmd_skuDetail, "RELATED_RECIPES", RelatedRecipes); QueryAddColumn(qmd_skuDetail, "SpecSheet", PDFList); SELECT child_attrId as attrId from tbl_PRD_AttributeLinks WHERE child_attrId IN ( select a.attrId as mstr_attrId from tbl_PRD_Attributes a inner join tbl_PRD_AttributeTypes at ON a.attrTypeId = at.attrTypeId where fsl_tablecode = and ownerId = ) and mstr_attrId = and child_attrTypeId = 4 and mstr_attrTypeId = 7 SELECT d.DocTitle, d.DocDescription, dl.DocPath FROM tbl_DOC_Documents d with (nolock) INNER JOIN tbl_ORG_AttributeLinks oal with (nolock) ON oal.mstr_attrID = d.DocID INNER JOIN qrySecureDocFullPathLookup dl with (nolock) ON d.ContentID = dl.ContentID WHERE d.OwnerID = AND d.FSL_TableCode = AND oal.mstr_attrTypeID = AND oal.child_attrTypeID = AND oal.child_attrID = ORDER BY d.sortrank, d.UploadDate QuerySetCell(qmd_skuDetail, "cn_label_title_#qry_attachedDocs.currentRow#", "#qry_attachedDocs.DocTitle#",1); QuerySetCell(qmd_skuDetail, "cn_label_url_#qry_attachedDocs.currentRow#", "#qry_attachedDocs.DocPath#",1); select categoryid from tbl_prd_repositorycategories with (nolock) where containertype = 'SKU' and (ownerid = and ownertype = or ownerid = 0 and ownertype in (,'*')) and categoryDescription = QueryAddColumn(qmd_skuDetail, "#_colomnName#_title","varchar", arrayNew(1))> QueryAddColumn(qmd_skuDetail, "#_colomnName#_url","varchar", arrayNew(1))> QuerySetCell(qmd_skuDetail, "#_colomnName#_title", "#qmd_repositoryContents.DocTitle#",1); QuerySetCell(qmd_skuDetail, "#_colomnName#_url", "#_docPath#",1); select v.videoId, v.videoTitle, v.videoDesc, v.videoShareLink from tbl_PRD_Videos v with (nolock) where v.ownerID = and v.ownerType = and v.linkID = and v.linkType = 'SKU' order by v.videoID asc