asked on

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.

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

Open in new window

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

Russ Suter

So you're saying this won't work?

select left(pay_notes, charindex(';', pay_notes)-1) AS [MyAlias]

Tina K

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.

Tina K

ASKER

Just to clarify - I do not want to update the table, I just want to manipulate how the data is returned. Thanks

Russ Suter

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]

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Olaf Doschke

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

left(convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)) ,charindex(';',convert(varchar(8000), convert(binary(8000), PAYABLE_BINARY.BITS)))-1)

Open in new window

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.

PortletPaul

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

Tina K

ASKER

Works perfect, Paul - thank you!!