-
11th October 2010, 09:27 PM
#91
Senior Member
Diamond Hubber
App na and others
I have 3 columns called A,B, and C , I want to merge all the data in these columns and display in one column, how do I do that?
I tried
Select A + B + C from my table
it works, but it does not leave any spaces between the content of A, B and C
pls help
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
11th October 2010 09:27 PM
# ADS
Circuit advertisement
-
11th October 2010, 09:28 PM
#92
Senior Member
Diamond Hubber
Originally Posted by
dev
How to resolve 403-forbidden error?... I am not able to access few sites and I get this 403 error.
Dev
http://en.wikipedia.org/wiki/HTTP_403
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
11th October 2010, 10:09 PM
#93
Senior Member
Platinum Hubber
Originally Posted by
PARAMASHIVAN
App na and others
I have 3 columns called A,B, and C , I want to merge all the data in these columns and display in one column, how do I do that?
Not sure about sql-server again, but in Oracle, you can "pipe" them together with spaces:
select a||' '||b||' '||c
-
12th October 2010, 11:35 AM
#94
Senior Member
Senior Hubber
Originally Posted by
PARAMASHIVAN
App na and others
I have 3 columns called A,B, and C , I want to merge all the data in these columns and display in one column, how do I do that?
I tried
Select A + B + C from my table
it works, but it does not leave any spaces between the content of A, B and C
pls help
If you want leave space between columns you can add as shown below
select A+' '+B+' '+C
if any of the columns already have trailing blank spaces
then use
rtrim(A)+ ' '+rtrim(B)+' '+rtrim(C)
or
ltrim(A)+ ' '+ltrim(B)+' '+ltrim(C)
This can be used only for Char and varchar fields.If the field is of integer or date then u need to convert and join.
Hope this helps
-
12th October 2010, 02:43 PM
#95
Senior Member
Diamond Hubber
many thanks kirukan it worked
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
12th October 2010, 03:09 PM
#96
Senior Member
Diamond Hubber
thanka app na
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
12th October 2010, 03:10 PM
#97
Senior Member
Diamond Hubber
Originally Posted by
app_engine
Not sure about sql-server again, but in Oracle, you can "pipe" them together with spaces:
select a||' '||b||' '||c[/quote]
thanks , but 'pipe' does not work in sql server, it only works in MY SQL and Oracle .
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
13th October 2010, 04:10 PM
#98
Senior Member
Diamond Hubber
Hi All
This script I have written below is highly complicated, there is no syntax error, but it is not updating the tables it should be, this stored procedures have nested stored procedures within in,
My question is why is not updating the following tables, bookings, event_resource, and quote
please help
many thanks
USE [hospices_test]
GO
/****** Object: StoredProcedure [dbo].[hth_conference_import_scheduled] Script Date: 10/13/2010 10:54:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[hth_conference_import_scheduled]
AS
set nocount on
set ansi_warnings off
DECLARE @guids table(guid uniqueidentifier) -- Table Var for Imported Contacts
DECLARE @user uniqueidentifier
DECLARE @current_guid uniqueidentifier
DECLARE @contact_id uniqueidentifier
DECLARE @first_name nvarchar(255)
DECLARE @surname nvarchar(255)
DECLARE @address1 nvarchar(255)
DECLARE @address2 nvarchar(255)
DECLARE @Address3 nvarchar(255)
DECLARE @Town nvarchar(100)
DECLARE @Post_code nvarchar(20)
DECLARE @county nvarchar(100)
DECLARE @email nvarchar(150)
DECLARE @telephone nvarchar(100)
DECLARE @fax nvarchar(100)
DECLARE @m_status INT
DECLARE @last_name nvarchar(255)
DECLARE @special_needs_text nvarchar(255)
DECLARE @dietary_needs_text nvarchar(255)
DECLARE @job_title nvarchar(100)
DECLARE @event_resource_id_tue uniqueidentifier
DECLARE @event_resource_id_wed uniqueidentifier
DECLARE @event_resource_id_all uniqueidentifier
DECLARE @diet nvarchar(200)
DECLARE @pos int
DECLARE @need varchar(200)
DECLARE @lookup_value int
DECLARE @resource_request_id uniqueidentifier
DECLARE @quote_id uniqueidentifier
DECLARE @pricing_option varchar(200)
DECLARE @event_response_id uniqueidentifier
DECLARE @id uniqueidentifier
DECLARE @description nvarchar(255)
DECLARE @cost_price float
DECLARE @list_price float
DECLARE @quote_line_id uniqueidentifier
select @event_resource_id_tue = id
from _event_resource (nolock)
where reference = 'Conf10/Tues1'
select @event_resource_id_wed = id
from _event_resource (nolock)
where reference = 'Conf10/Wed1'
select @event_resource_id_all = id
from _event_resource (nolock)
where reference = 'Conf10/All'
--Get the guids from the import table
INSERT INTO @guids select id from _Conference_import
SELECT TOP 1
@current_guid = GUID
FROM @guids
WHILE @current_guid IS NOT NULL
BEGIN
IF (@current_guid not in (select id from _booking))
BEGIN
-- grab the data from the import
SELECT @first_name=first_name,
@surname=surname,
@address1=address_1,
@address2=address_2,
@address3=address_3,
@town=town,
@post_code=post_code,
@county=county,
@email=email,
@telephone=phone,
@fax=fax,
@user = created_by,
@diet= Dietary_req,
@pricing_option=Pricing_option
FROM _Conference_import
WHERE id = @current_guid
BEGIN
-- find the contact or create a new one
EXEC dbo.hth_get_contact_id
@first_name,
@last_name,
@post_code,
@email,
1,
@contact_id OUTPUT,
@m_status output
-- update the contacts addres info
EXEC hth_update_contact_details
@contact_id,
@address1,
@address2,
@address3,
@town,
@county,
@post_code,
@telephone,
@Fax,
@email,
'Conference import'
--Work out what event response to use
set @event_response_id = case
when @pricing_option like 'two days%'
then @event_resource_id_all
when @pricing_option like 'single day%16 november%'
then @event_resource_id_tue
when @pricing_option like 'single day%17 november%'
then @event_resource_id_wed
else null
end
--Create the booking record
EXEC dbo.hth_create_default_record @current_guid, 'booking', @user
UPDATE [_Booking]
SET [contact_id] = @contact_id
,[event_resource_id] = @event_response_id
,[company_id] = (select top 1
e.id
from _company as e
where e.name = b.Organisation
)
,[job_title] = b.Job_title
,[dietary_needs_text] = b.Other_diet
,[special_needs_text] = b.Special_needs
,[mailing_address_type] = 5
,[mailing_address1] = b.Address_1
,[mailing_address2] = b.Address_2
,[mailing_address3] = b.Address_3
,[mailing_address_town] = b.Town
,[mailing_address_county] = b.County
,[mailing_address_postcode] = b.Post_code
,[correspondance_address1] = b.Address_1
,[correspondance_address2] = b.Address_2
,[correspondance_address3] = b.Address_3
,[correspondance_address_town] = b.Town
,[correspondance_address_county] = b.County
,[correspondance_address_postcode] = b.Post_code
,[mailing_address_iso_country_code] = isnull((
select top 1
d.iso_name
from Country_Locale as c, Country as d
where c.locale = 2057
and d.country_code = c.country_code
and c.country_name = b.Country
),'GBR')
,[booking_tel] = b.Phone
,[contact_type] = 1
,[booking_payment_method] = 2 --Invoice
,[booking_email] = b.Email
,[import_organisation] = b.Organisation
from _Booking as a, _Conference_import as b
where a.id = b.id
and a.id = @current_guid
--Add the lookups for dietary requirements
set @diet = isnull(@diet,'') + ','
set @pos = charindex(',',@diet)
while @pos > 1
begin
set @need = left(@diet,@pos -1)
set @diet = right(@diet,len(@diet)-@pos)
set @pos = charindex(',',@diet)
set @lookup_value = null
select @lookup_value = code
from [l_booking dietray requirements]
where description = @need
if @lookup_value is not null
begin
insert into entity_lookup
select @current_guid,
@lookup_value,
newid(),
0,
'dietary_needs'
end
end
--Add the resource request
SET @resource_request_id = newid()
EXEC dbo.hth_create_default_record @resource_request_id, 'resource_request', @user
UPDATE [_Resource_Request]
SET [class] = 2
,[contact_id] = @contact_id
,[places_requested] = 1
,[event_resource_id] = @event_response_id
,[booking_id] = @current_guid
,[request_no] = 1
,[waiting] = 0
WHERE id = @resource_request_id
--Add the Quote (header)
set @quote_id = newid()
EXEC dbo.hth_create_default_record @quote_id, 'quote', @user
UPDATE [_Quote]
SET [total_price] = 0
,[total_discount] = 0
,[total_margin] = 100
,[total_cost] = 0
,[quote_date] = getdate()
,[net_price] = 0
,[active_quote] = 1
,[contact_id] = @contact_id
,[source_entity] = 'Booking'
,[source_id] = @current_guid
WHERE id = @quote_id
-- Add the quote line
SELECT @id = id,
@description = DESCRIPTION,
@cost_price = cost_price,
@list_price = list_price
FROM product
WHERE ID = CASE
WHEN @pricing_option like 'Two days%standard rate%' then '271FB3FC-610E-4213-A3C3-AEEF6F1C74D0'
WHEN @pricing_option like 'Two days%discounted rate%' then '62354119-2368-4D15-9C9B-12456D647EED'
WHEN @pricing_option like 'Single day%17 November' then '4F1C6C79-840C-4775-B36F-238E298F47CE'
WHEN @pricing_option like 'Single day%16 Novembe' then 'F2CD6A29-2299-4E44-A7AF-238788866771'
ELSE NULL
END
SET @quote_line_id =newid()
EXEC dbo.hth_create_default_record @quote_line_id, 'quote_line', @user
UPDATE [_Quote_Line]
SET [customer_price] = @list_price
,[list_price] = @list_price
,[cost] = @cost_price
,[unit_customer_price] = @list_price
,[unit_list_price] = @list_price
,[unit_cost] = @cost_price
,[margin] = 0
,[margin_pc] = 100
,[discount] = 0
,[discount_pc] = 0
,[quantity] = 1
,[product_id] = @id
,[quote_id] = @quote_id
,[line_no] = 1
,[description] = @description
,[bacs] = 1
WHERE id = @quote_line_id
--TODO Calculate the quote value and update the quote header
UPDATE [_Quote]
SET [total_price] = (select sum(list_price) from _quote_line where quote_id = @quote_id)
,[total_cost] = (select sum(cost) from _quote_line where quote_id = @quote_id)
,[net_price] = (select sum(list_price) from _quote_line where quote_id = @quote_id)
WHERE id = @quote_id
END
END
--delete the current record from the temp table
DELETE
FROM @guids
WHERE guid = @current_guid
SET @current_guid = NULL
SELECT TOP 1
@current_guid = guid
FROM @guids
END
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
-
13th October 2010, 04:34 PM
#99
Senior Member
Platinum Hubber
Param,
Where do you "commit"?
-
13th October 2010, 04:36 PM
#100
Senior Member
Diamond Hubber
Originally Posted by
app_engine
Param,
Where do you "commit"?
hi na
Do I need a commit? when I have not got a 'begin tran' command
Om Namaste astu Bhagavan Vishveshvaraya Mahadevaya Triambakaya Tripurantakaya Trikalagni kalaya kalagnirudraya Neelakanthaya Mrutyunjayaya Sarveshvaraya Sadashivaya Shriman Mahadevaya Namah Om Namah Shivaye Om Om Namah Shivaye Om Om Namah Shivaye
Bookmarks