Column type for case ... end value

Transact-SQL
1

Hello Everyone

Using "CREATE TABLE" to create a temporary table,
what column type would be suitable to receive the result from the following:

(case when int_val > 0 then RTrim(SUBSTRING(<varchar(1088)>, , )) else '' end)
?

I keep getting: "Column name or number of supplied values does not match table definition."
for varchar(1088).

Or is there a collation issue?

Thanks
Etienne

2

You have a mismatch on the number of values return from your query and the number of columns in the destination table. Show your complete query

1 Like
3

Thank you @khtan
The code worked when I specified dbtemp. I am now assuming it was a collation issue.

IF OBJECT_ID('tempdb.dbo.#agena', 'U') IS NOT NULL
BEGIN

  • drop table tempdb.dbo.#agena;*
    END;
    CREATE TABLE tempdb.dbo.#agena ( [agenaComment] varchar(12) );
    INSERT INTO tempdb.dbo.#agena

  • select (case when varz.hiphen_pos>0 then /rtrim/(SUBSTRING( po.PO_COMMENT, varz.hiphen_pos - (5-varz1.start_pos), 11 )) else '' end) as [agenaComment]*

  • FROM tempdb.dbo.#master_agena ta*

  • join [AgenaDetails] adtl on adtl.dId=ta.dId*

  • cross apply ( select charIndex('inv',ta.PO_COMMENT) as start_pos ) varz1*

  • cross apply ( select (case when varz1.start_pos>0 then charIndex('-', substring( ta.PO_COMMENT, varz1.start_pos,25) ) else 0 end) as hiphen_pos ) varz*