CODEWITHSUNDEEP
sql
Dave Edmonds
Dave Edmonds

Reputation: 87

Syntax error using LEN() with varchar

can anyone advise the correct syntax for below.

@test len(right(@test, charindex('/',@test) 1)1)  + ' ' + left(@test, charindex('/',@test) 1)  + ' ' +invoice_booking.dptr_date +  ' ' + invoice_booking_detail.destination_name [AS] Description

I added the len() into it as I want to limit the first result to 1 character

I have also declared @test as 

DECLARE @test nvarchar(100)
SET @test = invoice_booking_detail.principal_passenger

Cheers

Dave

Upvotes: 0

Views: 97

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Wrong Syntax near LEN(),LEFT(),RIGHT() 

@test LEN(RIGHT(@test, CHARINDEX('/',@test)))  + ' ' + LEFT(@test, CHARINDEX('/',@test))  + ' ' +invoice_booking.dptr_date +  ' ' + invoice_booking_detail.destination_name [AS] Description

Upvotes: 1

Dave Edmonds
Dave Edmonds

Reputation: 87

here is the complete statement:

    DECLARE @test nvarchar(100)
    SET @test = invoice_booking_detail.principal_passenger
    SELECT
    CASE WHEN 
    (Invoice_booking_detail.destination_country_name  = ‘United Kingdom’)     then ‘T1’
ELSE ‘6130’
END AS [Nominal Code],
‘F200’ AS [Cost Centre],
Case when
(Invoice_booking.booking_type_description = ‘Flight’ )then ‘T2’
When
(Invoice_booking.booking_type_description = ‘Hotel’) then ‘T3’
When
(Invoice_booking.booking_type_description = ‘Rail’ )then ‘T9’
Else ‘T999’
END AS [Travel Project Code],
‘0’ AS  [Always ‘0000],
‘ ‘,
‘ ‘,
‘ ‘,
‘ ‘,
‘ ‘,
Case when 
(invoice_booking_detail.invoice_nett_amount < 0) then 0
When
(invoice_booking_detail.invoice_nett_amount >= 0) then     invoice_booking_detail.invoice_nett_amount
END AS [Amount DR],
Case when 
(invoice_booking_detail.invoice_nett_amount >= 0) then 0
When
(invoice_booking_detail.invoice_nett_amount < 0) then     invoice_booking_detail.invoice_nett_amount
END AS [Amount CR],

‘ ‘,
‘ ‘,

@test len(right(@test, charindex(‘/’,@test) 1)1)  + ' ' + left(@test,     charindex(‘/’,@test) 1)  + ' ' +invoice_booking.dptr_date +  '  ' +     invoice_booking_detail.destination_name [AS] Description

FROM TEST.invoice_booking invoice_booking,
TEST.invoice_booking_detail invoice_booking_detail

WHERE invoice_booking.trans_number = invoice_booking_detail.trans_number AND
invoice_booking.company = invoice_booking_detail.company AND
invoice_booking.branch_code = invoice_booking_detail.branch_code AND 
invoice_booking.trip = invoice_booking_detail.trip AND 
invoice_booking.booking = invoice_booking_detail.booking AND 
invoice_booking_detail.bill_to_customer_id like 'TEST ACCOUNT%'
ORDER BY invoice_booking_detail.principal_passenger

Upvotes: 0

Related Questions