November 5, 2012 at 8:30 am
Hi,
I have a table that, among other columns, there are three in particular; qualifier, segment, and value.
the combination of data in the segment and qualifier columns produce some sort of "value".
If the combination of qualifier column (PL) and the segment column (SLN), the resulting value in column "VALUE" should be "Line Number"
If the combination of qualifier column (DV) and the segment column (SLN), the resulting value in column "VALUE" should be "Deptartment"
If the combination of qualifier column (91) and the segment column (PID), the resulting value in column "VALUE" should be "Size"
I just can't think of a way to create a query.
Please help.
Thank you
November 5, 2012 at 8:48 am
You could use a case to populate the values eg
Select
CASE
When Qualifer='PL' and Segment='SLN' Then 'Line Number'
When Qualifer='DV' and Segment='SLN' Then 'Department'
When Qualifer='91' and Segment='PID' Then 'Size'
ELSE NULL
END VALUE
From
aTable
Edit :wrong code.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 5, 2012 at 8:51 am
Hi,
Not entirely sure I understand your question, but I think you need to look at the CASE statement.
e.g.:
SELECT col1
, col2
, CASE WHEN (col1 + col2) = 42 THEN 'Department' ELSE 'somethingelse' END
FROM myTable
See http://msdn.microsoft.com/en-us/library/ms181765.aspx
HTH,
B
November 5, 2012 at 9:32 am
Thank you for the suggestions. So this is what I have:
SELECTzzoordrh.BILL_NUM, zzedatawhse.value
CASE
WHEN zzedatawhse.QUALIFIER = 'PL' AND zzedatawhse.SEGMENT = 'SLN' THEN 'Line No'
WHEN zzedatawhse.QUALIFIER = 'DV' AND zzedatawhse.SEGMENT = 'SLN' THEN 'Department'
WHEN zzedatawhse.QUALIFIER = '91' AND zzedatawhse.SEGMENT = 'PID' THEN 'Line No'
END
FROM zzedatawhse INNER JOIN zzoordrh ON zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num
WHEREzzoordrh.BILL_NUM = '113111'
But I am getting:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.
November 5, 2012 at 9:36 am
you need a comma before the word CASE
and you might as well add AS [myColumnName] after END to give a column name to the third column.
B
November 5, 2012 at 9:44 am
Humm,
Thank you B, I added both and I am still getting:
Incorrect syntax near '='.
This is what it looks like now:
SELECTzzoordrh.BILL_NUM, zzedatawhse.value,
CASE zzedatawhse.VALUE
WHEN zzedatawhse.QUALIFIER = 'PL' AND zzedatawhse.SEGMENT = 'SLN' THEN 'Line No'
WHEN zzedatawhse.QUALIFIER = 'DV' AND zzedatawhse.SEGMENT = 'SLN' THEN 'Department'
WHEN zzedatawhse.QUALIFIER = '91' AND zzedatawhse.SEGMENT = 'PID' THEN 'Line No'
END AS zzedatawhse.value
FROMzzedatawhse INNER JOIN
zzoordrh ON zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num
WHEREzzoordrh.BILL_NUM = '113111'
November 5, 2012 at 11:35 am
it's just a syntax issue;
CASE for SQL has two forms :
CASE ValuetoTest
WHEN SomeValue
THEN SomeOtherValue
ELSE DefaultValue
END
--OR
CASE
WHEN {SomeExpression} (ie SomeColumn = SomeValue)
THEN SomeOtherValue
ELSE DefaultValue
END
you werejust mixing them together
I think this is your final query:
SELECT
zzoordrh.BILL_NUM,
zzedatawhse.value,
CASE
WHEN zzedatawhse.QUALIFIER = 'PL'
AND zzedatawhse.SEGMENT = 'SLN'
THEN 'Line No'
WHEN zzedatawhse.QUALIFIER = 'DV'
AND zzedatawhse.SEGMENT = 'SLN'
THEN 'Department'
WHEN zzedatawhse.QUALIFIER = '91'
AND zzedatawhse.SEGMENT = 'PID'
THEN 'Line No'
END AS [zzedatawhse.value]
FROM zzedatawhse
INNER JOIN zzoordrh
ON zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num
WHERE zzoordrh.BILL_NUM = '113111'
Lowell
November 6, 2012 at 1:26 pm
Thank you Lowell!!!!!!!
It works!!!!
I just not sure this this is what I was expecting. See, I am getting three columns: BILL_NUM, VALUE, and zzedatawhse.value.
The table shows all records from table zzedatawhse as "NULL" except the ones in the CASE statement. Each with their appropriate label.
I want to only shows the records called or mentioned in the CASE statement on their own columns.
That is;
BILL_NUM, LINE_NO, DEPARTMENT, SIZE
It gets more complicated as these records must match the line number of an order. I have this part done but I just can't figure out
how to get these values on their own column.
I hope I am explaining myself clearly.
Thank you
November 6, 2012 at 2:20 pm
reliableitservice (11/6/2012)
Thank you Lowell!!!!!!!It works!!!!
I just not sure this this is what I was expecting. See, I am getting three columns: BILL_NUM, VALUE, and zzedatawhse.value.
The table shows all records from table zzedatawhse as "NULL" except the ones in the CASE statement. Each with their appropriate label.
I want to only shows the records called or mentioned in the CASE statement on their own columns.
That is;
BILL_NUM, LINE_NO, DEPARTMENT, SIZE
It gets more complicated as these records must match the line number of an order. I have this part done but I just can't figure out
how to get these values on their own column.
I hope I am explaining myself clearly.
Thank you
Your post was pretty clear...or so I thought until this last post. It is very unclear what you are trying to do here. You might take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply