Difference between revisions of "SQL"
Jump to navigation
Jump to search
(Created page with ";LPAD(<value>,<no>,<char>) :Left fill <value> with <char> until <no> length ;concat(<value1>,<value2>) :Concatenate 2 strings") |
|||
Line 4: | Line 4: | ||
;concat(<value1>,<value2>) | ;concat(<value1>,<value2>) | ||
:Concatenate 2 strings | :Concatenate 2 strings | ||
+ | |||
+ | Example query | ||
+ | <syntaxhighlight lang='sql'> | ||
+ | select int_id, | ||
+ | 'fieldspec1' as 'Parameter', | ||
+ | subquery.field as VALUE_SUBQUERY, | ||
+ | from | ||
+ | (Select distinct fielsspec, | ||
+ | (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 | ||
+ | from table1 | ||
+ | Inner Join table2 on table2field = table1field | ||
+ | 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 | ||
+ | </syntaxhighlight> |
Revision as of 12:52, 10 February 2018
- LPAD(<value>,<no>,<char>)
- Left fill <value> with <char> until <no> length
- concat(<value1>,<value2>)
- Concatenate 2 strings
Example query
select int_id,
'fieldspec1' as 'Parameter',
subquery.field as VALUE_SUBQUERY,
from
(Select distinct fielsspec,
(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
from table1
Inner Join table2 on table2field = table1field
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