Page 10 of 20 FirstFirst ... 89101112 ... LastLast
Results 91 to 100 of 197

Thread: IT Professionals

  1. #91
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    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

  2. # ADS
    Circuit advertisement
    Join Date
    Always
    Location
    Advertising world
    Posts
    Many
     

  3. #92
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    Quote 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

  4. #93
    Senior Member Platinum Hubber
    Join Date
    Oct 2004
    Posts
    10,586
    Post Thanks / Like
    Quote 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

  5. #94
    Senior Member Senior Hubber kirukan's Avatar
    Join Date
    Nov 2004
    Location
    udal koodu
    Posts
    600
    Post Thanks / Like
    Quote 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

  6. #95
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    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

  7. #96
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    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

  8. #97
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    Quote 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

  9. #98
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    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

  10. #99
    Senior Member Platinum Hubber
    Join Date
    Oct 2004
    Posts
    10,586
    Post Thanks / Like
    Param,

    Where do you "commit"?


  11. #100
    Senior Member Diamond Hubber PARAMASHIVAN's Avatar
    Join Date
    May 2009
    Location
    Kailash
    Posts
    5,541
    Post Thanks / Like
    Quote 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

Page 10 of 20 FirstFirst ... 89101112 ... LastLast

Similar Threads

  1. IT Professionals
    By Raghu in forum Miscellaneous Topics
    Replies: 206
    Last Post: 19th July 2007, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •