SQL left() argument using alias
HI Experts -
I have crystal report that is using a sql query as a command. My sql looks at a binary table and converts a note field into text.
Works great, no problem.
However, I cannot seem to get accounting to play nice and put ONLY the data needed in the notes field, so it turns out I'm converting a lot of garbage along with the data I need. I have gotten them to agree to put 'my' data first, followed by a ';'. So, I normally would do the following:
select left(pay_notes, charindex(';', pay_notes)-1)
Nice thought, but sql will not let me use an alias here, even though I would do them all in the same select statement.
Before anyone slaps my hand for doing this in sql rather than crystal - this query is the command data for a subreport and the note field happens to be the data needed to link to the primary report, so must come in clean for a proper link.
Any help/suggestions are greatly appreciated!
Tina
I have crystal report that is using a sql query as a command. My sql looks at a binary table and converts a note field into text.
select payable.voucher_id, convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)) as pay_notes, payable_binary.bits
from payable inner join
payable_binary on payable.voucher_id = payable_binary.voucher_id
Works great, no problem.
However, I cannot seem to get accounting to play nice and put ONLY the data needed in the notes field, so it turns out I'm converting a lot of garbage along with the data I need. I have gotten them to agree to put 'my' data first, followed by a ';'. So, I normally would do the following:
select left(pay_notes, charindex(';', pay_notes)-1)
Nice thought, but sql will not let me use an alias here, even though I would do them all in the same select statement.
Before anyone slaps my hand for doing this in sql rather than crystal - this query is the command data for a subreport and the note field happens to be the data needed to link to the primary report, so must come in clean for a proper link.
Any help/suggestions are greatly appreciated!
Tina
Last Comment
ASKER
Hi Russ - the problem is that pay_notes is an alias set when I did the conversion. SQL does not recognize it as a valid column name. I'm not sure how else to trim my converted data.
ASKER
Just to clarify - I do not want to update the table, I just want to manipulate how the data is returned. Thanks
Ah, gotcha (I think). I've encountered similar problems in the past. You can resolve it by turning your query into a subquery. Then the aliases become valid column names. Something like this:
select
[mySubQuery].[voucher_id],
[mySubQuery].[pay_notes],
[mySubQuery].[bits]
from
(
select payable.voucher_id, convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)) as pay_notes, payable_binary.bits
from payable inner join
payable_binary on payable.voucher_id = payable_binary.voucher_id
) as [mySubQuery]
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Well, APPLY may help, but a simple solution is using the expression itself insteadd of the alias:
convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)) as pay_notes
So you'd have
It is less maintainable, but then how often would you maintain (eg change) this? Also possible would be defining a function you call passing in PAYABLE_BINARY.BITS and returning just a scalar value, the left portion of the binary data converted to string up to the semicolon.
It seems to me you can also simplify the expression, but I don't intend to go into such details.
Bye, Olaf.
convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)) as pay_notes
So you'd have
left(convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)) ,charindex(';',convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)))-1)
It is less maintainable, but then how often would you maintain (eg change) this? Also possible would be defining a function you call passing in PAYABLE_BINARY.BITS and returning just a scalar value, the left portion of the binary data converted to string up to the semicolon.
It seems to me you can also simplify the expression, but I don't intend to go into such details.
Bye, Olaf.
however
if there is no semi-colon the charindex returns zero, it deducts one from that and then the LEFT() will fail
at the very least add the ';' as I suggested
if there is no semi-colon the charindex returns zero, it deducts one from that and then the LEFT() will fail
at the very least add the ';' as I suggested
ASKER
Works perfect, Paul - thank you!!
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
select left(pay_notes, charindex(';', pay_notes)-1) AS [MyAlias]