The following code works fine -
select
-
concat(
-
case t1.testColumn
-
when 'value1' then t1.column1
-
when 'value2' then t1.column2
-
when 'value3' then t1.column3
-
else t1.column4
-
end
-
) as outputColumn
-
My question is this (because I could not get the following code to work), is is possible to construct the case clause where the concat( ) is located inside the case statement, which would allow for multiple columns to be returned with just one case statement.
Here is my code that did not work. -
select
-
case t1.testColumn
-
when 'value1'
-
then
-
concat(t1.column1) as outputColumn1,
-
concat(t1.column2) as outputColumn2,
-
concat(t1.column3) as outputColumn3
-
when 'value2'
-
then
-
concat(t1.column4) as outputColumn1,
-
concat(t1.column5) as outputColumn2,
-
concat(t1.column6) as outputColumn3
-
else
-
concat(t1.column7) as outputColumn1,
-
concat(t1.column8) as outputColumn2,
-
concat(t1.column9) as outputColumn3
-
end
-
The purpose of my question is to see if it possible to include several columns in one case statement rather than constructing one for each column I wish to output.
The practical application applies to a billing source reference. A job may be billed to the requesting party, the actual client or some 3rd party unique to that job.
5 13330 Luuk 1,047
Expert 1GB
What do you try to accomplig with i.e. line #5 - concat(t1.column1) as outputColumn1,
concat is a function, and will concatenat all parameters it receives....
But you only give 1 parameter?
Returning multiple columns in a CASE WHEN is not possible
Maybe you can do: -
select t1.column1, t1.column2,t1.column3
-
where t1.tesColumns='value1'
-
union
-
select t1.column4, t1.column5,t1.column6
-
where t1.tesColumns='value2'
-
union
-
select t1.column7, t1.column8,t1.column9
-
where t1.tesColumns='value3'
-
zmbd 5,501
Expert Mod 4TB
If not the union then perhaps:
remove the CONCAT() and break the three sections you have down to individuals:
so for, an example using just the first section: - select
-
case t1.testColumn
-
when 'value1' then
-
t1.column1 as outputColumn1,
-
case t1.testColumn
-
when 'value1' then
-
t1.column2 as outputColumn2,
-
case t1.testColumn
-
when 'value1' then
-
t1.column3 as outputColumn3
-
(... repeat for each of your
-
sub sections... yes it's 9 lines ...)
There is the case..when..then..else construct; however, I don't see how to push that forward for what you have.
Just to clarify what Luuk is saying about Concat()
SYNTAX: Concat(str1,str2[,str3,][...])
resolves as "str1str2str3..."
thus if str1 = "a", str2 = "b", and str3 = "c"
then the return is "abc"
I think Luuk has summed it up "Returning multiple columns in a CASE WHEN is not possible"
Perhaps it has to be done with sub-queries.
Each record contains 3 sets of Company information (name, address, city, state, zip code etc. etc.)
1. Requesting party
2. Actual client
3. Where to send the bill
For the form I am developing, I want to select one SET of company information. So I was trying to return multiple columns in one case statement, instead of creating multiple case statements.
I can simply return all 3 company sets of information and then select the piece of the data I want to use with my php code. I was simply experimenting with developing a better sql query that would require less coding downstream.
Thank you both for you suggested help.
Luuk 1,047
Expert 1GB
Your original database design is wrong...
if you have a table with 3 sets of company info, you should change that. One record should only containt 1 set of company info
if you have (simplified): - select
-
name1, address1,
-
name2, address2,
-
name3, address3
-
from mytable
you should change this to: -
select
-
1 as id, name1, address1
-
from mytable
-
union
-
select
-
2 as id, name2, address2
-
from mytable
-
union
-
select
-
3 as id,name3, address3
-
from mytable
this way you can see all your requesting party's - select * from view where id=1
or all your actual clients - select * from view where id=2
check i.e. how to create a view
Yes I am familiar with normalizing data in relational data base system and that structure has been implemented. That was not really the question for this exercise.
But I thank you for your input.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Guy Hocking |
last post by:
Hi there,
I have a problem in my ASP/SQL Server application i am developing, i hope
you guys can help.
I have a ASP form with list boxes populated by SQL tables. When a user
selects a value...
|
by: brett |
last post by:
Here is my SQL string:
"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON...
|
by: John Hall |
last post by:
We need to read a SQL database containing a mix of English words and Chinese
Characters. We think we need to use the N'xxxx' to read the Unicode.
We have one place where the SELECT statement...
|
by: GIS Analyst |
last post by:
Hi to all
I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).
ie
declare @theTest1 nvarchar(10)
set...
|
by: grunar |
last post by:
After some thought on what I need in a Python ORM (multiple primary
keys, complex joins, case statements etc.), and after having built
these libraries for other un-named languages, I decided to...
|
by: patang |
last post by:
I am displaying the records from a table in a grid. The simpel SELECT
statement works fine. However, I have to use this select statement
select right("00000" + Column1,2), column2, column3 FROM...
|
by: sangam56 |
last post by:
Hello!I am using following sql statement:
SELECT Menu.MenuID,Menu.TextUrl FROM Menu WHERE
Menu.MenuID= (SELECT Permissions.MenuID FROM Permissions WHERE
Permissions.RoleID=(SELECT Roles.RoleID...
|
by: ankitmathur |
last post by:
Hi All,
I'm facing a problem I'm unable to find a solution for. I hope fellow members would be able to help me out.
System Info:
PHP v5
MSSQL 2008
Linux box
|
by: ncsthbell |
last post by:
Once again... I am supporting another Access database that I did not build. The previous 'owner' of this built the tables with spaces in the table names as well as the column names. I am having a...
|
by: thewebdude |
last post by:
Hi,
I am doing a project with dreamweaver and i ran into a hitch with my select statement.
I have this
SELECT *
FROM client, `statement`
WHERE client.client_id =...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |