Difference between revisions of "SQL"
Jump to navigation
Jump to search
Line 5: | Line 5: | ||
:Concatenate 2 strings | :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 | Example query | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
select int_id, | select int_id, | ||
− | 'fieldspec1' as ' | + | 'fieldspec1' as Alias, |
− | subquery.field as VALUE_SUBQUERY | + | (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.field as VALUE_SUBQUERY | ||
from | from | ||
(Select distinct fielsspec, | (Select distinct fielsspec, | ||
− | + | from table1 tabalias | |
− | + | Inner Join table2 on table2field = tabalias.field | |
− | |||
− | |||
− | |||
− | from table1 | ||
− | Inner Join table2 on table2field = | ||
Inner Join table3 on table3field = table2field | Inner Join table3 on table3field = table2field | ||
And table3filed = value | And table3filed = value |
Revision as of 13:00, 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.field as VALUE_SUBQUERY
from
(Select distinct fielsspec,
from table1 tabalias
Inner Join table2 on table2field = tabalias.field
Inner Join table3 on table3field = table2field
And table3filed = value
Where (field1 > value And (field2 = 1 And field3 = 'value'))
or (field > value )
) as subquery
Inner Join jointable1 On jointable1.field = subquery.field
Where subquery.field <> jointable1.field