November 2, 2015 at 6:29 am
Hello Masters,
I need to create a statement that will output a list of business sub classes base on if the business is a Large "L", Small "S", WomanOwned "F", or "N"
The column " V.S_CL_SM_BUS_CD " holds this value. Here is the basic query I have but I'm sure it needs much work -
SELECT V.S_CL_SM_BUS_CD
,V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,VCHR.VCHR_KEY
FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON v.VEND_ID = VCHR.VEND_ID
JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
So for example if V.S_CL_SM_BUS_CD is "F", then I want to show data if any of these sub categories are "Y" -
V.CL_VET_FL
V.CL_SD_VET_FL
V.CL_ANC_IT_FL
V.CL_DISADV_FL
V.CL_WOM_OWN_FL
V.CL_LAB_SRPL_FL
V.CL_HIST_BL_CLG_FL
V.CL_ANC_IT_FL
Any assistance is always greatly appreciated
November 2, 2015 at 6:46 am
SELECT V.S_CL_SM_BUS_CD
,V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,VCHR.VCHR_KEY
FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON v.VEND_ID = VCHR.VEND_ID
INNER JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
WHERE V.S_CL_SM_BUS_CD = 'F'
AND 'Y' IN (V.CL_VET_FL, V.CL_SD_VET_FL, V.CL_ANC_IT_FL, V.CL_DISADV_FL, V.CL_WOM_OWN_FL, V.CL_LAB_SRPL_FL, V.CL_HIST_BL_CLG_FL, V.CL_ANC_IT_FL)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2015 at 7:40 am
This is great. Thanks so much. The problem I'm running into is in the where clause. If I say
WHERE V.S_CL_SM_BUS_CD = 'F'
AND 'Y' IN (V.CL_VET_FL, V.CL_SD_VET_FL, V.CL_ANC_IT_FL, V.CL_DISADV_FL, V.CL_WOM_OWN_FL, V.CL_LAB_SRPL_FL, V.CL_HIST_BL_CLG_FL, V.CL_ANC_IT_FL)
There could be a value with a "N" and not all set to "Y". That gives me no results. Is there a way to specify each case?
I hope this makes more sense.
November 2, 2015 at 7:46 am
mcoleman15902 (11/2/2015)
This is great. Thanks so much. The problem I'm running into is in the where clause. If I sayWHERE V.S_CL_SM_BUS_CD = 'F'
AND 'Y' IN (V.CL_VET_FL, V.CL_SD_VET_FL, V.CL_ANC_IT_FL, V.CL_DISADV_FL, V.CL_WOM_OWN_FL, V.CL_LAB_SRPL_FL, V.CL_HIST_BL_CLG_FL, V.CL_ANC_IT_FL)
There could be a value with a "N" and not all set to "Y". That gives me no results. Is there a way to specify each case?
I hope this makes more sense.
Your first post specified any = 'Y' 🙂
Try
WHERE V.S_CL_SM_BUS_CD = 'F'
AND 'N' NOT IN (V.CL_VET_FL, V.CL_SD_VET_FL, V.CL_ANC_IT_FL, V.CL_DISADV_FL, V.CL_WOM_OWN_FL, V.CL_LAB_SRPL_FL, V.CL_HIST_BL_CLG_FL, V.CL_ANC_IT_FL)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2015 at 8:00 am
Watch out for NULLs:
SELECT A,B,C,D
FROM (VALUES ('Y','Y','Y','Y'),('Y','N','Y','Y'),('Y',NULL,'Y','Y')) d (A,B,C,D)
WHERE 'Y' IN (A,B,C,D)
SELECT A,B,C,D
FROM (VALUES ('Y','Y','Y','Y'),('Y','N','Y','Y'),('Y',NULL,'Y','Y')) d (A,B,C,D)
WHERE 'N' NOT IN (A,B,C,D)
SELECT A,B,C,D
FROM (VALUES ('Y','Y','Y','Y'),('Y','N','Y','Y'),('Y',NULL,'Y','Y')) d (A,B,C,D)
WHERE 'N' NOT IN (ISNULL(A,'Y'),ISNULL(B,'Y'),ISNULL(C,'Y'),ISNULL(D,'Y'))
SELECT A,B,C,D
FROM (VALUES ('Y','Y','Y','Y'),('Y','N','Y','Y'),('Y',NULL,'Y','Y')) d (A,B,C,D)
CROSS APPLY (
SELECT [Min] = MIN(MyVal), [Max] = MAX(MyVal) FROM (VALUES (A),(B),(C),(D)) d (MyVal)
) x
WHERE x.[Min] = 'Y'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2015 at 8:03 am
I think this is on me for not explaining clearly enough. My apologies. So for each case of V.S_CL_SM_BUS_CD, it could be any of the 4 following business types L for Large, S for small, F for Female, and N for unknown. So when it looks at the business type, I want it to pull the columns that are ONLY set to "Y". These are subcategories, such as disabled, veteran, native america, ect...
A business could have no subcategories, or one, or many. The goal is to show each vendor_ID class and subclasses and ULTIMATELY the cost (LNHS.CST_AMT) associated with that vendor. Hope this makes more sense. Thanks for taking the time to assist a newbie:-D
November 2, 2015 at 8:12 am
mcoleman15902 (11/2/2015)
I think this is on me for not explaining clearly enough. My apologies. So for each case of V.S_CL_SM_BUS_CD, it could be any of the 4 following business types L for Large, S for small, F for Female, and N for unknown. So when it looks at the business type, I want it to pull the columns that are ONLY set to "Y". These are subcategories, such as disabled, veteran, native america, ect...A business could have no subcategories, or one, or many. The goal is to show each vendor_ID class and subclasses and ULTIMATELY the cost (LNHS.CST_AMT) associated with that vendor. Hope this makes more sense. Thanks for taking the time to assist a newbie:-D
Yes you can do this with case expressions, but you will have to explain a little more. For instance,
I need to create a statement that will output a list of business sub classes base on if the business is a Large "L", Small "S", WomanOwned "F", or "N"
this looks like it could be four different queries?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2015 at 8:21 am
Yes this could be 4 given the scenario. Will they have to be broken out or can they be done within one.
Thanks!
November 2, 2015 at 8:32 am
If the column count can be different, then they are four different queries - or dynamic SQL.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2015 at 9:40 am
Dynamic query may take too much time given my skill set. Could you provide one of the queries and I can work from that method?
Thanks, Sir.
MC
November 2, 2015 at 9:56 am
mcoleman15902 (11/2/2015)
Dynamic query may take too much time given my skill set. Could you provide one of the queries and I can work from that method?Thanks, Sir.
MC
Actually, I was just going to ask you the same question. If you can construct one of the queries and supply the business rules for the rest, then folks can probably take it from there.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2015 at 11:24 am
Do they have to be separate columns, or can it just be a string of matches in a field.
Something like this will give you a comma separated list of the records matching.
STUFF((SELECT ', ' + (fieldname)
FROM WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
WHERE v.VEND_ID = VCHR.VEND_ID AND (fieldfilter) = 'Y')
FOR XML PATH (''),type ).value('.[1]', 'nvarchar(max)'),1,2,'') as [MergedField]
November 3, 2015 at 8:46 am
I think it needs to be separate columns for reporting purposes because it's going in SSRS. I tried using a case statement like so:
select case when V.S_CL_SM_BUS_CD = 'L' THEN
(Select V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,VCHR.VCHR_KEY )
end
FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON v.VEND_ID = VCHR.VEND_ID
LEFT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
-----------------------------------------------------------------------
This did not work though I got the following error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I think my logic was to say when V.S_CL_SM_BUS_CD = 'L', then used a sub query to select the following columns. I was going to do this for all 4 "V.S_CL_SM_BUS_CD" scenarios.
November 3, 2015 at 8:51 am
mcoleman15902 (11/3/2015)
I think it needs to be separate columns for reporting purposes because it's going in SSRS. I tried using a case statement like so:select case when V.S_CL_SM_BUS_CD = 'L' THEN
(Select V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,VCHR.VCHR_KEY )
end
FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON v.VEND_ID = VCHR.VEND_ID
LEFT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
-----------------------------------------------------------------------
This did not work though I got the following error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I think my logic was to say when V.S_CL_SM_BUS_CD = 'L', then used a sub query to select the following columns. I was going to do this for all 4 "V.S_CL_SM_BUS_CD" scenarios.
This won't work because it's syntactically incorrect as you've found, but as pseudocode to demonstrate what you are attempting to do, it's fine. Can you knock up the other cases i.e. where V.S_CL_SM_BUS_CD = whatever. It should be possible to work it out from there.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 3, 2015 at 9:19 am
How about just using unions between 4 select statements - assuming all the fields are of the same type.
SELECT V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL AS [COL1]
,V.CL_SD_VET_FL AS [COL2]
,V.CL_ANC_IT_FL AS [COL3]
,V.CL_DISADV_FL AS [COL4]
,V.CL_WOM_OWN_FL AS [COL5]
,V.CL_LAB_SRPL_FL AS [COL6]
,V.CL_HIST_BL_CLG_FL AS [COL7]
,V.CL_ANC_IT_FL AS [COL8]
,VCHR.VCHR_KEY
FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR ON v.VEND_ID = VCHR.VEND_ID
LEFT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
WHERE V.S_CL_SM_BUS_CD = 'L'
UNION ALL
SELECT V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.(otherfield) AS [COL1]
,V. (otherfield) AS [COL2]
,V. (otherfield) AS [COL3]
,V. (otherfield) AS [COL4]
,V. (otherfield) AS [COL5]
,V. (otherfield) AS [COL6]
,null AS [COL7]
, null AS [COL8]
,VCHR.VCHR_KEY
FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR ON v.VEND_ID = VCHR.VEND_ID
LEFT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
WHERE V.S_CL_SM_BUS_CD = 'S'
UNION ALL
....
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply