AS alias for Results

Databases
1

hi SQL gurus,

I have been working on the query below and all i need help with is giving an alias to the destination:

select distinct t1.pk,t1.title,t1.Unique as EAN,t1.FCF as GB_Flag,
t2.title as Title,t2.destination AS Suggested_Atom, t2.FCF
from db1 t1
inner join db1 t2 on
t1.Unique = t2.Unique
where t1.Unique = t2.Unique
and t1.destination in (9956)
and t1.indexed = 0
and t1.FCF not in(11)
and t1.Unique is not null
and t1.Unique != ‘0000000000000’
and t2.destination in (11125)

all i want displayed for t2.destination would be 9956 as 11125 and 9956 are linked together

is that at all possible?

Thank you :slight_smile:

2

ok so i found a work around:

select distinct t1.pk,t1.title,t1.Unique as EAN,t1.FCF as GB_Flag,
t2.title as Title,t2.destination AS Suggested_Atom, t2.FCF

‘Converted_Atom’ = CASE
when t2.destination = 11125 then " 9956"

from db1 t1
inner join db1 t2 on
t1.Unique = t2.Unique
where t1.Unique = t2.Unique
and t1.destination in (9956)
and t1.indexed = 0
and t1.FCF not in(11)
and t1.Unique is not null
and t1.Unique != ‘0000000000000’
and t2.destination in (11125)

but i’ve got over 100 “matching instances” would this be the best method to follow?

anyone?

3

I don’t understand what you’re trying to do, but there are some peculiar things in your query


select distinct t1.pk,t1.title,t1.Unique as EAN,t1.FCF as GB_Flag,
t2.title as Title,t2.destination AS Suggested_Atom, t2.FCF

[B][COLOR="#FF0000"]'Converted_Atom' = CASE
when t2.destination = 11125 then " 9956"  <-- 1) I don't know what you want to do here, but t2.destination is always 11125 (you say so in the WHERE)
[/COLOR][/B]

from db1 t1
inner join db1 t2 on
t1.Unique = t2.Unique
where [B][COLOR="#FF0000"]t1.Unique = t2.Unique   <-- 2) no need for this condition, you already have it in the ON clause[/COLOR][/B]
and [B][COLOR="#FF0000"]t1.destination in (9956)   <-- 3) if there's only one value, use = instead of IN[/COLOR][/B]
and t1.indexed = 0
and t1.FCF not in(11)
and t1.Unique is not null
and t1.Unique != '0000000000000'
and t2.destination in (11125)

Comments 1 and 3 are valid for this particular query. If the real queries you run have more values, than just ignore them.
I still don’t get what you want to do at point 1 though.
Maybe


CASE WHEN t2.destination = 11125 THEN " 9956"
     ELSE t2.destination
END AS Converted_Atom

Comment 3 is valid (if valid) for all IN’s in the WHERE clause.

4

Hi Guido,

Thank you for the advice, the real query i run is something similar to this:

select distinct t1.pk,t1.title,t1.Unique as EAN,t1.FCF as GB_Flag,
t2.title as Title,t2.destination AS Suggested_Atom, t2.FCF

‘Converted_Atom’ = CASE
when t2.destination = 11125 then " 9956"
when t2.destination = 10738 then “11469”
when t2.destination = 10934 then “11665”
when t2.destination = 11094 then “11825”
when t2.destination = 11096 then “11827”
when t2.destination = 11097 then “11828”
when t2.destination = 11098 then “11829”
when t2.destination = 11099 then “11830”
when t2.destination = 11100 then “11831”
when t2.destination = 11102 then “11833”
when t2.destination = 11103 then “11834”

from db1 t1
inner join db1 t2 on
t1.Unique = t2.Unique
where t1.Unique = t2.Unique
and t1.destination in (9956)
and t1.indexed = 0
and t1.FCF not in(11)
and t1.Unique is not null
and t1.Unique != ‘0000000000000’
and t2.destination in (11125,
9678,
10738,
10934,
11094,
11096,
11097,
11098,
11099,
11100,
11102,
11103,
11104,
11105,
11106,
11107,
11108,
11109,
11111,
11112,
11114,
11115,
11116,
11117,
11118,
11119,
11120,
11125,
11126,
11127,
11128,
9677,
11130,
11131,
11133,
11134,
11135,
11136,
11137,
11138,
11139,
11140,
11141,
11145,
11146,
11148,
11150,
11151,
11152,
11153,
11154,
11155,
11156,
11157,
11158,
11159,
11160,
11161,
11163,
11164,
11165,
11095,
11144,
11166,

12591,
12200,
12396,
12556,
12558,
12608,
12560,
12561,
12562,
12564,
12565,
12566,
12567,
12568,
12569,
12570,
12571,
12573,
12574,
12576,
12571,
12578,
12579,
12580,
12581,
12582,
12587,
12588,
12589,
12590,
12577,
12592,
12593,
12595,
12596,
12597,
12606,
12599,
12600,
12601,
12602,
12603,
12607,
12608,
12610,
12612,
12613,
12614,
12615,
12616,
12617,
12618,
12619,
12620,
12621,
12622,
12623,
12625,
12626,
12627,
12557,
12598,
12628)

but when there around another 200 more “when t2.destination = X then X” and when i run the query it informs me of the following:

Server message number=102 severity=15 state=0 line=0 text=ASA Error -131: Syntax error near ‘parse stack overflow’ on line 125

but there is no syntax error whatsoever ??

Thank you :slight_smile:

5

whent2.destination = 11095 then “11826”
when t2.destination = 11144 then “11875”
ELSE t2.destination
END AS Converted_Atom

i keep getting an error when i apply what you’ve suggested:

Server message number=102 severity=15 state=0 line=0 text=ASA Error -131: Syntax error near ‘AS’ on line 68

:confused:

6

You’re missing a comma after t2.FCF

And I think there’s a limit to the lenght of the query text. If those decodifications are always the same, you might want to create a decod table and get the values from there.

7

hi guido, i removed t2.FCF and i still get the error?

8

repost the query please

9

select distinct t1.oid,t1.title,t1.ean13 as EAN,t1.final_classify_flag as Original_Classify_Flag,
t2.title,t2.final_atom AS Suggested_Atom,
‘Converted_Atom’ = CASE
when t2.final_atom = 9678 then “9727”
when t2.final_atom = 10738 then “11469”
when t2.final_atom = 10934 then “11665”
when t2.final_atom = 11094 then “11825”
when t2.final_atom = 11096 then “11827”
ELSE t2.final_atom
END AS Converted_Atom

readjusted for different fields :slight_smile:

10 

select distinct t1.oid,t1.title,t1.ean13 as EAN,t1.final_classify_flag as Original_Classify_Flag,
t2.title,t2.final_atom AS Suggested_Atom,
CASE
when t2.final_atom = 9678 then "9727"
when t2.final_atom = 10738 then "11469"
when t2.final_atom = 10934 then "11665"
when t2.final_atom = 11094 then "11825"
when t2.final_atom = 11096 then "11827"
ELSE t2.final_atom
END AS Converted_Atom

Get rid of ‘Converted_Atom’ =

11

this is a microsoft sql server error message

plus, assigning the column alias like ‘Converted_Atom’ = expression is also unique to microsoft sql server

would you please confirm that you are actually trying to run a microsoft sql server query against an oracle mysql server (the forum you posted in)

12

im using an inhouse front end client (sybase “heliqs”) and i still get the error when i remove the ‘converted_atom’ =

i get the following error:

Server message number=21 severity=14 state=0 line=0 text=ASA Error -1009145: Data exception - data type conversion is not possible. In CASE expression, incompatible data type at result expression 6, t2.Final_Atom, – (dfe_Case.cxx 807)

:S

13 

case 
    when t2.final_atom = 9678 then 9727
    when t2.final_atom = 10738 then 11469
    when t2.final_atom = 10934 then 11665
    when t2.final_atom = 11094 then 11825
    when t2.final_atom = 11096 then 11827
    else t2.final_atom end as Converted_Atom

You should have compatible types for all results in a case expression.

14

Thank you swampboogie, it works perfectly however when i add more when clauses i get the following error:

Server message number=102 severity=15 state=0 line=0 text=ASA Error -131: Syntax error near ‘parse stack overflow’ on line 126

is there any way to bypass this?

Thank you so much! :slight_smile:

15

I quote myself to answer your question :slight_smile: