Actually, I was...

If the column count can be different, then they are four different queries - or dynamic SQL.

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...

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...

mcoleman15902 (11/2/2015)

---

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,...

ben.brugman (11/2/2015)

---

Sorry at the moment I do not see a feasible way to do this within a limited amount...

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,...

muralikrishna2489 (11/2/2015)

---

Hi Chris,

Thanks very much for the advise !

I have one doubt, please clarify on the below quotes,

You...

chris92mars (11/2/2015)

---

I had a job running to call the stored procedure to perform action. Normally this action took around 18mins but it run till 2mins hit 3621, the session is...

A query like this

SELECT

rn = ROW_NUMBER() OVER(PARTITION BY AccountNumber, HexaNumber, HexaEffDate, HexaId, TransactionEffDate, TransactionExpDate, HexaCode, CompanyCode, CategoryCode, AdjustmentUserId

ORDER BY (SELECT NULL)),

*

FROM CodeChanges

should identify your dupes. You might have...

ben.brugman (11/2/2015)

---

Just did a checkup on the tables which inspired this thread.

The actual number of columns in the 'covering index' was 10. And yes they are all used in the...

ben.brugman (11/2/2015)

---

GilaMonster (11/1/2015)

Columns included are in the leaf level only, not the key, so the index is smaller than if they were in the key.

They also don't count towards the...

Jeff Moden (10/31/2015)

---

TomThomson (10/31/2015)

---

ChrisM@Work (10/29/2015)

---

Ed Wagner (10/29/2015)

---

yb751 (10/29/2015)

---

I was on my last eye twitch yesterday (so to speak) so I put together this image. (attached)

I was thinking about unleashing it...

Mile Higher Than Sea Level (10/30/2015)

---

The query links a SQL Server table to a SQL Server Linked Server (Oracle) table across a very slow VPN.

A simple Select query against Oracle...

mw112009 (10/30/2015)

---

Select A.* from A inner join B on ( A.ID= B.ID )

I know there is some key word that you use to force SQL server to generate a new...