July 5, 2010 at 11:52 am
Hello,
I am brand new to SQL Server, so the question is very basic. Why did I get the error shown in the screen shot?
Thanks a lot in advance,
Gabor
July 5, 2010 at 12:13 pm
Gabor there are two kinds of functions...scalar functions and table valued functions.
if you call a table-value-function as if it were a scalar, you'd get the error you received.
a TVF is treated as if it were a table joined against a source: the syntax looks like my example below....
does this return any results?
select *
from dbo.ts_exp_def_detail
CROSS APPLY dbo.bmo_col_list(1036) myAlias
order by col_order
Lowell
July 5, 2010 at 12:34 pm
Hello Lowell,
Thanks for the help.
does this return any results?
select *
from dbo.ts_exp_def_detail
CROSS APPLY dbo.bmo_col_list(1036) myAlias
order by col_order
No, it does not. It returns
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.bmo_col_list'.
I think the function is a scalar-valued function because it returns a varchar. It simply attempts to create a comma separated list of column values fetched by a query (sorry, indentation doesn't seem to be kept by pasting):
CREATE FUNCTION dbo.BMO_col_list ( @an_export_id decimal(10,0) ) RETURNS varchar
WITH EXECUTE AS CALLER AS
BEGIN
DECLARE
@ls_colvarchar(4000),
@ls_col_listvarchar(4000)
declare
c1 cursor for
select
case
when
table_name is null
then
column_name
else
table_name + '.' + convert ( varchar, column_name )
end
from
db0.ts_exp_def_detail
where
export_id = @an_export_id
order by
col_order
open c1
fetch next from c1 into @ls_col
while @@fetch_status = 0
begin
select @ls_col_list = @ls_col_list + ', ' + @ls_col
end
close c1
deallocate c1
RETURN ( @ls_col_list )
END
Any help would be greatly appreciated!
Gabor
July 5, 2010 at 12:39 pm
i think your function is not compiling because it is refering to a misspelled schema:
db0.ts_exp_def_detail
instead of
dbo.ts_exp_def_detail
..extra pair of eyes....forest thru the trees and all that stuff
Lowell
July 5, 2010 at 12:52 pm
As a side note: It seems like all you want to do is get a comma separated list columns ofr a table specified by @an_export_id.
If so, there are concepts available that will perform much better than a c.u.r.s.o.r inside a scalar function.
If you'd like to see an alternative way please post the dbo.ts_exp_def_detail definition together with table def involved and some sample data.
If you need some guidance on how to post such information either follow the steps given in Lowells signature or follow the first link in my signature.
Edit: typos corrected
July 5, 2010 at 1:05 pm
Hello,
Thanks for the extra set of eyes! Good catch!
However:
1. It did compile despite that typo (which is strange...)
2. When I corrected it, it did not compile (a CONVERT was required).
3. Now that it is compiled correctly, I am still getting the same error.
4. Is there any easier way to replace a function then refreshing the function list and then deleting the function before creating it again?
I will post the create table and insert statements in a few minutes.
Thanks again!
Gabor
July 5, 2010 at 1:14 pm
Regarding question #4: Did you try to right click on the function in question and select "Modify"? 😉
July 5, 2010 at 1:15 pm
Hello Lutz,
Thanks, yes, I would definitely like to learn about more efficient ways of achieving what you assumed correctly I want to do. So below is the requested info. BTW, I would still like to understand, just to satisfy my curiosity, why I am getting this error.
Thanks!
Gabor
--------------------------------------------------------------
Script to create the table:
USE [DEVCR]
GO
/****** Object: Table [dbo].[TS_EXP_DEF_DETAIL] Script Date: 07/05/2010 11:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TS_EXP_DEF_DETAIL](
[EXPORT_ID] [decimal](10, 0) NOT NULL,
[TABLE_NAME] [varchar](250) NULL,
[COLUMN_NAME] [text] NOT NULL,
[ALIAS] [varchar](64) NULL,
[DATA_TYPE] [varchar](15) NULL,
[PAD_SIDE] [varchar](5) NULL,
[OFFSET] [decimal](10, 0) NULL,
[LENGTH] [decimal](10, 0) NULL,
[FORMAT] [varchar](64) NULL,
[COL_ORDER] [decimal](10, 0) NOT NULL,
[DBTYPE] [varchar](10) NOT NULL,
[CRD_LOCK] [char](1) NOT NULL DEFAULT ('N'),
[REQUIRED] [decimal](1, 0) NULL,
[ORDER_BY] [decimal](10, 0) NULL,
[ORDER_TYPE] [varchar](4) NULL,
CONSTRAINT [TS_EXP_DEF_DETAIL_PK] PRIMARY KEY CLUSTERED
(
[EXPORT_ID] ASC,
[COL_ORDER] ASC,
[DBTYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------------------------------------------
Script to populate the table:
delete from ts_exp_def_detail where export_id = 1036;
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','BLOCKID','','',null,null,'',1,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','PARENTID','','',null,null,'',2,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'ts_order_alloc.acct_cd + isnull(cast(ts_order_alloc.special_inst as varchar(3)),''??'')','ACCT','','',null,null,'',3,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'floor(ts_order_alloc.exec_qty)','QUANTITY','NUMERIC','',null,null,'#0.00000',4,'SYB','N',1,3,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when ts_order.trans_type = ''BUYL'' then ''B'' when ts_order.trans_type = ''BUYL'' then ''B'' when ts_order.trans_type = ''SELLL'' then ''S'' end','BUY_SELL','','',null,null,'',5,'SYB','N',1,2,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when csm_security.sec_typ_cd = ''OCALL'' or csm_security.sec_typ_cd = ''OPUT'' then (select ext_sec_id from csm_security where sec_id = ts_order.sec_id) end','ADPCODE','','',null,null,'',6,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','CUSIP','','',null,null,'',7,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when (select max(cntry_cd) from bmonb_blotter_osp where blotter_cd=ts_order.usr_class_cd_2) = ''CAN'' and csm_security.sec_typ_cd not in (''OPUT'', ''OCALL'') then ''.'' + csm_security.ticker when (select max(cntry_cd) from bmonb_blotter_osp where blotter_cd=ts_order.usr_class_cd_2) = ''USA'' and csm_security.sec_typ_cd not in (''OPUT'', ''OCALL'') then replace(csm_security.ticker, ''.'', '''') else case when csm_security.list_exch_cd = ''CDN'' and csm_security.sec_typ_cd not in (''OPUT'', ''OCALL'') then ''.'' + csm_security.ticker when csm_security.list_exch_cd <> ''CDN'' and csm_security.sec_typ_cd not in (''OPUT'', ''OCALL'') then replace(csm_security.ticker, ''.'', '''') end end','SYMBOL','','',null,null,'',8,'SYB','N',1,1,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'isnull(ts_order_alloc.exec_price,0.0000)','PRICE','NUMERIC','',null,null,'#0.0000',9,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when ts_order.prin_local_crrncy = ''USD'' then ''U$'' when ts_order.prin_local_crrncy = ''CAD'' then ''C$'' end','TRDCURRENCY','','',null,null,'',10,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'ts_order.USR_CLASS_CD_2','BLOTTER','','',null,null,'',11,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'(select max(bmonb_blotter_osp.osp) from bmonb_blotter_osp where bmonb_blotter_osp.blotter_cd = ts_order.usr_class_cd_2)','OSP','','',null,null,'',12,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','SPECIAL_COMMISSION','','',null,null,'',13,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','TRADE_DATE','','',null,null,'',14,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','SETTLEMENT_DATE','','',null,null,'',15,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when ts_order.usr_class_cd_2 = ''1N'' and cs_fund.usr_class_cd_3=''DAP'' then ''DX'' else case when exists (select 0 from fix_incoming where ts_order.order_id=fix_incoming.tm_orig_id and fix_incoming.user_defined like ''%6776="Y"%'') then case when (select count(0) from fix_incoming where fix_incoming.fix_ord_status in (''1'',''2'') and fix_incoming.tm_orig_id=ts_order.order_id) > 1 then ''2A'' else ''WP'' end else case when (select count(0) from fix_incoming where fix_incoming.fix_ord_status in (''1'',''2'') and fix_incoming.tm_orig_id=ts_order.order_id) > 1 then ''AT'' else '''' end end end','TRAILER_CODE','','',null,null,'',16,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,'CS_FUND','MANAGER','RR','','',null,null,'',17,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''''','PREFIG_COMMISSION','','',null,null,'',18,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when ts_order_alloc.tax_lot_udf_char3 = ''CAD'' then ''C$'' when ts_order_alloc.tax_lot_udf_char3 = ''USD'' then ''U$'' else ''??'' end','CLIENT_SIDE_CURRENCY','','',null,null,'',19,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'case when isnumeric(ts_order_alloc.usr_class_cd_6) = 1 then ts_order_alloc.usr_class_cd_6 else '''' end','CLIENT_SIDE_CONVERSION','','',null,null,'',20,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'(select min(fix_clordid) from ts_order_placement where ts_order_placement.order_id = ts_order.order_id)','TRAILER1','','',null,null,'',21,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''PCDCRIMS''','ORDERMANAGER','','',null,null,'',22,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,null,'''EQ''','SECTYPE','','',null,null,'',23,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,'TS_ORDER','ORDER_ID','ORDER_ID','numeric','',null,null,'#0',24,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,'TS_ORDER_ALLOC','TRADE_ID','TRADE_ID','numeric','',null,null,'#0',25,'SYB','N',1,null,null)
insert into ts_exp_def_detail (export_id,table_name,column_name,alias,data_type,pad_side,offset,length,format,col_order,dbtype,crd_lock,required,order_by,order_type)
values (1036,'TS_ORDER_ALLOC','ACCT_CD','FUNDID',null,null,null,null,null,26,'SYB','N',1,null,null)
July 5, 2010 at 1:18 pm
lmu92 (7/5/2010)
Regarding question #4: Did you try to right click on the function in question and select "Modify"? 😉
Hm. No. But it does sound logical...:blush:
Thanks.
July 5, 2010 at 1:48 pm
well...this does what you wanted, but look at the results based on your sample data....it's appending a bunch of CASE WHEN statements and stuff....is that what you wanted?
this statemnts uses FOR XML to concat your values together...which can be converted to a function.... but look at the results:
1036'','',ts_order_alloc.acct_cd + isnul,floor(ts_order_alloc.exec_qty),case when ts_order.trans_type ,case when csm_security.sec_typ,'',case when (select max(cntry_cd,isnull(ts_order_alloc.exec_pri,case when ts_order.prin_local_,ts_order.USR_CLASS_CD_2,(select max(bmonb_blotter_osp.,'','','',case when ts_order.usr_class_c,CS_FUND.MANAGER,'',case when ts_order_alloc.tax_l,case when isnumeric(ts_order_a,(select min(fix_clordid) from ,'PCDCRIMS','EQ',TS_ORDER.ORDER_ID,TS_ORDER_ALLOC.TRADE_ID,TS_ORDER_ALLOC.ACCT_CD
SELECT export_id,stuff(( SELECT ','
+ case
when table_name is null
then convert ( varchar, column_name )
else table_name + '.' + convert ( varchar, column_name )
end
FROM ts_exp_def_detail s2
WHERE s2.export_id= s1.export_id --- must match GROUP BY below
ORDER BY export_id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM ts_exp_def_detail s1
GROUP BY s1.export_id --- without GROUP BY multiple rows are returned
ORDER BY s1.export_id
Lowell
July 5, 2010 at 2:06 pm
First of all: Thank your for providing ready to use sample data!! 🙂
Made it really easy to focus on the subject!
Here are a few items I run across while transforming your function:
1) your current function:
a) The function will return an error because your source table TS_EXP_DEF_DETAIL has the column COLUMN_NAME defined as TEXT. Please note that this data type is marked deprecated since SQL 2005. You should use VARCHAR(MAX) instead. However, in your scenario it doesn't make sense to use a VARCHAR(MAX) column and concatenate it to a target size of VARCHAR(4000). The TEXT data type will cause your function to error with
Msg 402, Level 16, State 1, Line xx
The data types varchar and text are incompatible in the add operator.
Therefore, you'd either need to change the data type of your column (recommended solution) or cast it to a VARCHAR(n) data type within your function.
I've chosen the latter approach for the modified function. But you should spot the section to be modified right away...
b) Once the above error is fixed, the function will run forever, since there is nothing that will make the c.u.r.s.o.r. go to the next column. You'd need to change the l.o.o.p. to
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ls_col_list = @ls_col_list + ', ' + @ls_col
FETCH NEXT FROM c1 INTO @ls_colEND
2) and now the alternative:
There is a common routine to concatenate strings: it's a combination of FOR XML PATH to get the values per row concatenated together with STUFF() to remove the leading separator.
CREATE FUNCTION [dbo].[itvf_BMO_col_list] ( @an_export_id decimal(10,0) )
RETURNS TABLE
AS
RETURN
(
SELECT STUFF((SELECT ', '
+ CASE
WHEN table_name IS NULL
THEN cast(column_name as varchar(4000))
ELSE table_name + '.' + cast(column_name as varchar(4000))
END
FROM dbo.ts_exp_def_detail t2
WHERE t2.EXPORT_ID = t1.EXPORT_ID
FOR XML PATH(''), TYPE
).value('./text()[1]', 'NVARCHAR(4000)'),1,2,'') AS concatenated
FROM dbo.ts_exp_def_detail t1
WHERE EXPORT_ID = @an_export_id
GROUP BY EXPORT_ID
)
And here's an example how to use it:
DECLARE @tbl TABLE
(
EXPORT_ID INT
)
INSERT INTO @tbl
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 1036
SELECT t.EXPORT_ID,itvf_BMO_col_list.concatenated
FROM @tbl t
CROSS APPLY
dbo.[itvf_BMO_col_list] (t.EXPORT_ID)
I'm not sure what the main process does and what the source for the value of the parameter @an_export_id really is. But you might end up changing this process (maybe a c.u.r.s.o.r. again ?) to get the results all at once and use it for further processing.
July 5, 2010 at 2:07 pm
well...this does what you wanted
Unfortunately no.
First, because it returns the error message which this whole thread is about.
Second, because, as you pointed out, this is a work in progress and probably will have to be worked on still in order to produce the required results (not only the GROUP BY clause is missing).
Thanks again!
Gabor
July 5, 2010 at 2:12 pm
Lowell (7/5/2010)
well...this does what you wanted, but look at the results based on your sample data....it's appending a bunch of CASE WHEN statements and stuff....is that what you wanted?this statemnts uses FOR XML to concat your values together...which can be converted to a function.... but look at the results:
...
SELECT export_id,stuff(( SELECT ','
+ case
when table_name is null
then convert ( varchar, column_name )
else table_name + '.' + convert ( varchar, column_name )
end
FROM ts_exp_def_detail s2
WHERE s2.export_id= s1.export_id --- must match GROUP BY below
ORDER BY export_id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM ts_exp_def_detail s1
GROUP BY s1.export_id --- without GROUP BY multiple rows are returned
ORDER BY s1.export_id
Hey Lowell,
seems you beat me to it! :crying:
But at least Gabor now has two almost identical versions (based on the same concept anyway)...
July 5, 2010 at 2:21 pm
Hello Guys,
Thanks a lot for the explanations and ideas. This is a lot for me to digest, so please bare with me for a day (or two). I am brand new to all this, remember?... 🙂
Anyways, thanks again!
Gabor
July 5, 2010 at 3:13 pm
grevesz (7/5/2010)
Hello Guys,Thanks a lot for the explanations and ideas. This is a lot for me to digest, so please bare with me for a day (or two). I am brand new to all this, remember?... 🙂
Anyways, thanks again!
Gabor
Glad we could help 😀
Post back if you have any questions/concerns.
Meanwhile, here are two links that might help you to understand how the FOR XML stuff works and why I used a slightly different method than Lowell did:
Side note: you might need to read a few post within those threads,
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply