August 8, 2008 at 2:06 am
Hi, here is my problem:
As the following codes shown, I want to retrieve some data from several tables to a temprary table "#tmp_437", the problem is "asset_name" could not store two or more "asset.asset_name" under one condition:
select
uo.policy_number,
uo.ulp_order_id,
case when charindex('(', asset.asset_name) > 0 then
left(asset.asset_name, charindex('(', asset.asset_name) - 1)
else
asset.asset_name
end
asset_name,
......................
into #tmp_437
from
ulp_order uo,
asset,
............
where
........
For one "ulp_order_id", there may be several "asset_name" assigned. I want to get all the "asset.asset_name" and store them into "asset_name":
declare policy_product_cur cursor for
select uo.policy_number,
case when charindex('(', a.asset_name) > 0 then
left(a.asset_name, charindex('(', a.asset_name) - 1)
else
a.asset_name
end
asset_name
from ulp_order uo, asset a
where uo.product_id = a.asset_id and
uo.order_type = 'R'
order by ulp_order_id
for read only
open policy_product_cur
fetch policy_product_cur
into @tmp_policy_number, @tmp_asset_name
while @@sqlstatus != 2
begin
if @@sqlstatus = 1
begin
return
end
update #tmp_437
set asset_name = asset_name + '#' + @tmp_asset_name
where #tmp_437.policy_number = @tmp_policy_number
fetch policy_product_cur
into @tmp_policy_number, @tmp_asset_name
end
I add a "#" between two "asset_name" for late use in Crystal Report. The problem is "asset_name" is not longer enough to store all the "asset.asset_name". For example, for a certain ulp_order_id, the "asset_name" should be "Premier Life Critical Illness Protector#Hospital Income Benefit", however, the result I get is "Premier Life Critical Illness Protector#Hospital Income Bene", it could not store all the characters.
In table "asset", the "asset_name" is varchar(60), I'm afraid when I do select at the beginning, "asset_name" is also defaultly declared as varchar(60), so it could not store variables longer than 60 characters.
So is there any way to declare "asset_name" as varchar(100) during "select"? Or is there any simple way to solve this problem?
I tried to "create table #tmp_437" before "select" and decalre "asset_name" as varchar(100), however, error message always shown as "Attempt to insert NULL value into column 8 in work table (table id -xxxxxx)"...There will be a lot of things need to be modified...
So I ask for some simple methods to store all the "asset.asset_name" without using "create table...".
Thanks a lot!
August 8, 2008 at 4:10 am
I would be inclined to use a function. Something like:
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE FUNCTION dbo.GetAssetNames
(
    @policy_number varchar(20) -- or whatever the datatype of policy_number is.
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @AssetNames varchar(8000)
    SET @AssetNames = ''
    SELECT @AssetNames = @AssetNames
        + CASE
            WHEN CHARINDEX('(', asset_name) > 0
            THEN LEFT(asset_name, CHARINDEX('(', asset_name) - 1)
            ELSE asset_name
        END
        + '#'
    FROM asset
    WHERE policy_number = @policy_number
    RETURN LEFT(@AssetNames, LEN(@AssetNames) - 1)
END
GO
SELECT
    policy_number
    ,ulp_order_id
    ,dbo.GetAssetNames(policy_number) AS asset_name
FROM ulp_order
August 11, 2008 at 1:35 am
Thanks, Ken
Someone else suggests a much simpler way:
change
case when charindex('(', asset.asset_name) > 0 then
left(asset.asset_name, charindex('(', asset.asset_name) - 1)
else
asset.asset_name
end
asset_name,
to
CAST(CASE WHEN charindex('(', asset.asset_name) > 0 THEN
LEFT (asset.asset_name, charindex('(', asset.asset_name) - 1)
ELSE asset.asset_name
END AS VARCHAR(1000)) AS asset_name,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply