July 6, 2010 at 10:42 am
Hello,
I have played quite a bit with this and found that this is not exactly working properly for some reason.
Please look at the 5th insert statement in my post yesterday at 3:15:35 PM. It inserts a long string (a CASE statement) into the third column called COLUMN_NAME. That string (the CASE statement) is not returned properly by the SELECT statement you provided. I have no clue why.
Any idea?
Thanks once again for your help!
P.S. I made a mistake when I opened this thread, because I am using SQL Server 2005, not 2008. Is there any way to move this thread to the correct location and what would that location be?
Thanks,
Gabor
July 6, 2010 at 11:06 am
I had wondered why the table had those values...it didn't make any sense to me before, either...loked ugly in the results.
the issue:
the value in the field is a string which happens to contain the word "CASE...blah blah"
the string value is not re-interpreted as a SQL statement.
Lowell
July 6, 2010 at 11:26 am
the string value is not re-interpreted as a SQL statement.
No, it isn't. The problem is that it isn't returned as a string correctly.
Regards,
Gabor
July 6, 2010 at 12:03 pm
grevesz (7/6/2010)
the string value is not re-interpreted as a SQL statement.
No, it isn't. The problem is that it isn't returned as a string correctly.
Regards,
Gabor
I don't quite understand.
What section or character isn't returned properly?
And who do you refer to as when stating "the select statement you provided"?
When running the function I posted I get the following result for the concatenated column. Please tell me the section you consider being not correct.
'', '', ts_order_alloc.acct_cd + isnull(cast(ts_order_alloc.special_inst as varchar(3)),'??'), floor(ts_order_alloc.exec_qty), 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, 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, '', 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, isnull(ts_order_alloc.exec_price,0.0000), case when ts_order.prin_local_crrncy = 'USD' then 'U$' when ts_order.prin_local_crrncy = 'CAD' then 'C$' end, ts_order.USR_CLASS_CD_2, (select max(bmonb_blotter_osp.osp) from bmonb_blotter_osp where bmonb_blotter_osp.blotter_cd = ts_order.usr_class_cd_2), '', '', '', 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, CS_FUND.MANAGER, '', 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, case when isnumeric(ts_order_alloc.usr_class_cd_6) = 1 then ts_order_alloc.usr_class_cd_6 else '' end, (select min(fix_clordid) from ts_order_placement where ts_order_placement.order_id = ts_order.order_id), 'PCDCRIMS', 'EQ', TS_ORDER.ORDER_ID, TS_ORDER_ALLOC.TRADE_ID, TS_ORDER_ALLOC.ACCT_CD
July 6, 2010 at 12:14 pm
Hello Lutz,
I was talking about the SELECT statement in this thread posted yesterday at 4:12:48 PM:
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
This statement, when I run it, returns case when ts_order.trans_type BUY_SELL, as the 5th element instead of the expected 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',.
Can you reproduce this?
If yes, why would this happen?
If not, why does my instance behave differently?
Thanks a lot,
Gabor
July 6, 2010 at 12:32 pm
grevesz (7/6/2010)
Hello Lutz,I was talking about the SELECT statement in this thread posted yesterday at 4:12:48 PM:
...This statement, when I run it, returns case when ts_order.trans_type BUY_SELL, as the 5th element instead of the expected 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',.
Can you reproduce this?
If yes, why would this happen?
If not, why does my instance behave differently?
Thanks a lot,
Gabor
Yes, I can reproduce the bevaior.
It seems like it's caused by some character or character combination within those strings together with entitization issues of the FOR XML PATH clause.
Therefore I added the xml notation.
It also seems like you didn't have the time yet to read the threads I pointed you at... The second is discussing the issue you're facing (at least being close).
I might find the time later on to look closer for the root cause of the strange result. But right now I just have to watch soccer... 😉
July 6, 2010 at 1:49 pm
And I say shame shame shame shame shame, SHAME ON ME! 😉
I should have spotted it right away...
Regarding your questions:
1 Can you reproduce this?
2 If yes, why would this happen?
3 If not, why does my instance behave differently?
A1: Yes, it's easy to reproduce. (as stated before)
A2: The code Lowell provided contains the conversion from text to varchar using
convert ( varchar, column_name )Since there is no length parameter provided, the standard value of 30 will be used. As per BOL (BooksOnLine, the SQL Server help system):
varchar [ ( n | max ) ] ...
When n is not specified when using the CAST and CONVERT functions, the default length is 30.
Therefore, you'll get truncated strings. Not starting with the 5th row. It already starts with row 3 (check the ISNULL function...).
In order to convert the whole string you need to provide a length parameter (e.g. 1000 if you're sure not to have rows longer than 1000 char).
A3: not relevant, since A2 applies 🙂
So, at least this part has nothing to do with entitization issues. Sorry... :blush:
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply