November 3, 2015 at 9:25 am
Hi, I've added the other 3 cases and the sub query to follow 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,
CASE WHEN V.S_CL_SM_BUS_CD = 'S' 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,
CASE WHEN V.S_CL_SM_BUS_CD = 'F' 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,
CASE WHEN V.S_CL_SM_BUS_CD = 'N' 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
Note that the columns LNHS.CST_AMT is a decimal(14,2) and VCHR.VCHR_KEY is an INT and VCHR.VEND_ID is a VARCHAR(12)
The remaining columns are varchar (1). Hope this helps.
November 3, 2015 at 9:32 am
For each CASE, the column lists look the same to me.
(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 )
I'm lost now - you've gone to some length to tell us that they are different, now they are the same?
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:46 am
Give us a clue as to how you want it to look, because this looks like it could just be a basic query with the Y/N's already in the correct columns
If you only returned the fields with Y's you'd end up with Name,Amt,Y,Y,Y and not know which columns they actually referred to.
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
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
November 3, 2015 at 10:03 am
I want it to say, okay, here is a business class "L", now let's see if there are any other sub classes along with it being a large business.
V.CL_VET_FL is a veteran business subclass (Could be yes or no)
CL_SD_VET_FL is a disabled business subclass (same)
V.CL_WOM_OWN_FL = woman owned subclass (same)
These are other subclasses I don't need to go into detail, but they are yes or no as well.
V.CL_ANC_IT_FL
V.CL_DISADV_FL
V.CL_LAB_SRPL_FL
V.CL_HIST_BL_CLG_FL
V.CL_ANC_IT_FL
The remaining columns show the business name, ID, and an amount on what they have spent.
Sorry to frustrate you guys. Thanks for the patience.
November 3, 2015 at 10:17 am
mcoleman15902 (11/3/2015)
I want it to say, okay, here is a business class "L", now let's see if there are any other sub classes along with it being a large business.V.CL_VET_FL is a veteran business subclass (Could be yes or no)
CL_SD_VET_FL is a disabled business subclass (same)
V.CL_WOM_OWN_FL = woman owned subclass (same)
These are other subclasses I don't need to go into detail, but they are yes or no as well.
V.CL_ANC_IT_FL
V.CL_DISADV_FL
V.CL_LAB_SRPL_FL
V.CL_HIST_BL_CLG_FL
V.CL_ANC_IT_FL
The remaining columns show the business name, ID, and an amount on what they have spent.
Sorry to frustrate you guys. Thanks for the patience.
How does your requirement differ from this:
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 IN ('L','S','F','N')
I'm not seeing an intelligent question yet.
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 10:24 am
Can't see from what you are saying why a straight query does not work.
SELECT V.VEND_NAME_EXT
,LNHS.CST_AMT
,V.S_CL_SM_BUS_CD
,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
LEFT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
Which would return something like the below
Name, Amount, Bus, Vet, Disable, Female, others....
ABC CO, 1500, L, Y, N, N, N
BBC CO, 2500, S, N, Y, Y, N
Unless what you actually want is a description of what the field represents as below
Name, Amount, V.S_CL_SM_BUS_CD, Veteran (If Y), Disabled (If Y), ...
eg
Name, Amount, Bus, Column1, Column2
ABC CO, 1500, L, Veteran
BBC CO, 2500, S, Disabled, Female
November 3, 2015 at 10:37 am
You guys are 100% correct and that's why you're the masters. This query looks good now. I do have one more question about it since it's returning values now.
A vend_ID can have multiple VCHR_KEY and each VCHR_KEY can have a different LNHS.CST_AMT . Is there a way to sum the total LNHS.CST_AMT on the VEND_ID ?
November 3, 2015 at 10:46 am
Only if you drop the VCHAR_KEY, as I take it the VCHAR_KEY isn't always the same for each VCHAR.VEND_ID
SELECT V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT)
,V.S_CL_SM_BUS_CD
,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
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
GROUP BY V.VEND_NAME_EXT
,V.S_CL_SM_BUS_CD
,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
November 3, 2015 at 11:04 am
SUM() OVER() would preserve rows.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 3, 2015 at 12:36 pm
You guys ROCK. I'm gonna analyze the data closer to make sure the joins are correctly pulling the right amount of records. If I have any more questions, I'll reach out.
Thanks
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply