Difference between revisions of "SQL"
Jump to navigation
Jump to search
m |
|||
Line 11: | Line 11: | ||
'fieldspec1' as Alias, | 'fieldspec1' as Alias, | ||
(Case | (Case | ||
− | When table1.field1 > 1 And table2.field1 = ' | + | When table1.field1 > 1 And table2.field1 = 'value' Then x |
− | When table1.field1 > 2 And table2.field1 = ' | + | When table1.field1 > 2 And table2.field1 = 'value' Then y |
Else z | Else z | ||
End) As CaseResult, | End) As CaseResult, | ||
− | subquery. | + | subquery.field1 as VALUE_SUBQUERY |
from | from | ||
(Select distinct fielsspec, | (Select distinct fielsspec, | ||
from table1 tabalias | from table1 tabalias | ||
− | Inner Join table2 on table2field = tabalias. | + | Inner Join table2 on table2field = tabalias.field1 |
Inner Join table3 on table3field = table2field | Inner Join table3 on table3field = table2field | ||
− | And table3filed = | + | And table3filed = avalue |
− | Where (field1 > | + | Where (field1 > 1value And (field2 = 1 And field3 = 'value')) |
− | or ( | + | or (field1 > avalue ) |
) as subquery | ) as subquery | ||
− | Inner Join jointable1 On jointable1. | + | Inner Join jointable1 On jointable1.field2 = subquery.field1 |
− | Where subquery. | + | Where subquery.field1 <> jointable1.field1 |
</syntaxhighlight> | </syntaxhighlight> |
Revision as of 13:07, 10 February 2018
- LPAD(<value>,<no>,<char>)
- Left fill <value> with <char> until <no> length
- concat(<value1>,<value2>)
- Concatenate 2 strings
The example shows how subquery results can be handled as a table, aliasing of field and tablenames, a case statement and inner joins. Example query
select int_id,
'fieldspec1' as Alias,
(Case
When table1.field1 > 1 And table2.field1 = 'value' Then x
When table1.field1 > 2 And table2.field1 = 'value' Then y
Else z
End) As CaseResult,
subquery.field1 as VALUE_SUBQUERY
from
(Select distinct fielsspec,
from table1 tabalias
Inner Join table2 on table2field = tabalias.field1
Inner Join table3 on table3field = table2field
And table3filed = avalue
Where (field1 > 1value And (field2 = 1 And field3 = 'value'))
or (field1 > avalue )
) as subquery
Inner Join jointable1 On jointable1.field2 = subquery.field1
Where subquery.field1 <> jointable1.field1