10.513: Advanced SDK for Integration and Single Sign-on

Description 

The error, "cannot CREATE VIEW with parameters," is returned when running a report with the following conditions: 

• Report is run against a Redshift database
• Parameterized query is enabled
• The VLDB setting, "Intermediate Table Type," is set to Temporary view

Why is this happening? 

If you check the report SQL, you can see the following. 

create view TMPIO9703MD000 ("QUARTER_ID", "CATEGORY_ID", "WJXBFS1") as 
select	"a12"."QUARTER_ID"  "QUARTER_ID",
	"a14"."CATEGORY_ID"  "CATEGORY_ID",
	sum("a11"."TOT_COST")  "WJXBFS1"
from	"item_mnth_sls"	"a11"
	join	"lu_month"	"a12"
	  on 	("a11"."MONTH_ID" = "a12"."MONTH_ID")
	join	"lu_item"	"a13"
	  on 	("a11"."ITEM_ID" = "a13"."ITEM_ID")
	join	"lu_subcateg"	"a14"
	  on 	("a13"."SUBCAT_ID" = "a14"."SUBCAT_ID")
where	"a14"."SUBCAT_DESC" in (?, ?, ?)
group by	"a12"."QUARTER_ID",
	"a14"."CATEGORY_ID" 
with parameters:
	TV's
	Sports & Health
	Soul / R&B

A CREATE VIEW statement is included with "?" placeholders, while in Redshift, parameters are not allowed in a CREATE VIEW statement. 

Solution 

You should change the VLDB setting of "Intermediate Table Type," from Temporary view to another value. 
Derived table is recommended, which is the default value. 
After changing the value to Derived table, the SQL looks like: 
 

select	"pa11"."QUARTER_ID"  "QUARTER_ID",
	"a14"."QUARTER_DESC"  "QUARTER_DESC",
	"pa11"."CATEGORY_ID"  "CATEGORY_ID",
	"a13"."CATEGORY_DESC"  "CATEGORY_DESC",
	"pa11"."WJXBFS1"  "WJXBFS1",
	"pa12"."WJXBFS1"  "WJXBFS2"
from	(select	"a12"."QUARTER_ID"  "QUARTER_ID",
		"a14"."CATEGORY_ID"  "CATEGORY_ID",
		sum("a11"."TOT_COST")  "WJXBFS1"
	from	"item_mnth_sls"	"a11"
		join	"lu_month"	"a12"
		  on 	("a11"."MONTH_ID" = "a12"."MONTH_ID")
		join	"lu_item"	"a13"
		  on 	("a11"."ITEM_ID" = "a13"."ITEM_ID")
		join	"lu_subcateg"	"a14"
		  on 	("a13"."SUBCAT_ID" = "a14"."SUBCAT_ID")
	where	"a14"."SUBCAT_DESC" in (?, ?, ?)
	group by	"a12"."QUARTER_ID",
		"a14"."CATEGORY_ID"
	)	"pa11"
	join	(select	"a11"."QUARTER_ID"  "QUARTER_ID",
		"a11"."CATEGORY_ID"  "CATEGORY_ID",
		"a11"."TOT_COST"  "WJXBFS1"
	from	"qtr_category_sls"	"a11"
	where	(("a11"."CATEGORY_ID")
	 in	(select	"s21"."CATEGORY_ID"
		from	"lu_subcateg"	"s21"
		where	"s21"."SUBCAT_DESC" in (?, ?, ?)
		group by	"s21"."CATEGORY_ID"))
	)	"pa12"
	  on 	("pa11"."CATEGORY_ID" = "pa12"."CATEGORY_ID" and 
	"pa11"."QUARTER_ID" = "pa12"."QUARTER_ID")
	join	"lu_category"	"a13"
	  on 	("pa11"."CATEGORY_ID" = "a13"."CATEGORY_ID")
	join	"lu_quarter"	"a14"
	  on 	("pa11"."QUARTER_ID" = "a14"."QUARTER_ID")
where	(("pa11"."CATEGORY_ID")
 in	(select	"s21"."CATEGORY_ID"
	from	"lu_subcateg"	"s21"
	where	"s21"."SUBCAT_DESC" in (?, ?, ?)
	group by	"s21"."CATEGORY_ID"))
with parameters:
	TV's
	Sports & Health
	Soul / R&B
	TV's
	Sports & Health
	Soul / R&B
	TV's
	Sports & Health
	Soul / R&B

The SQL is changed, however, no CREATE VIEW statement is present, and the error is gone.