We help IT Professionals succeed at work.

sql select - where column has more than two useable valuues

493 Views
Last Modified: 2014-05-13
Hi Experts,

I am working with the below query, and i need to return results for is TRADECODE is New or Existing, however if its Existing to ignore the rows with value NEW.

I would be grateful if someone could help me with this as I am stuck...  I have looked around online and see the same example with smaller calls, but nothing with anything of this complexity.

and unfortunately this cant be reduced at the moment.

SELECT     
	DISTINCT
	V.ENTITYID,
	V.CUSTOMERMNEMONIC,
	V.PRODUCTID,
	V.TRADENUMBER,
	V.TRADECODE,
	V.TRADEDATE,
	V.VALUEDATE,
	V.CCYPAIR,
	CASE
		WHEN V.PRODUCTID = 'FXOTC' THEN V.DIRECTION
		WHEN SUM(V.CCY1AMOUNT) > 0 THEN 'BUY'
		ELSE 'SELL'
	END DIRECTION,	
	LEFT(CCYPAIR,3) CCYID,
	SUM(V.CCY1AMOUNT) CCYAMOUNT,
	V.TRADERATE,
	SUM(V.CCY2AMOUNT) CCY2AMOUNT,
	V.MTMCCYID,
	CASE 
		WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) <> V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN SUM(MTM.MTMAMOUNT)
        WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) = V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN 0
        ELSE SUM(V.MTMLIMITCCYAMOUNT) 
    END MTMAMOUNT,
	V.MTMSPOTRATE,
	V.MTMCCY1DF,
	V.MTMCCY2DF,
	V.FEECCYID,
	SUM(V.FEECCYAMOUNT)	as FEECCYAMOUNT ,
	V.CCY1REVALRATE,
	V.CCY2REVALRATE,
	V.OPTIONEXPOSURETYPE,
	SUM(V.CCY1OTHERAMOUNT) as CCY1OTHERAMOUNT,
	SUM(V.CCY2OTHERAMOUNT) as CCY2OTHERAMOUNT,
	V.STRIKEPRICE,
	V.PREMIUMDATE,
	V.PREMIUMCCYID,
	V.PREMIUMAMOUNT,
	CASE 
		WHEN V.PRODUCTID = 'FX' THEN
			CASE
				WHEN V.TRADECODE IN ('SETTLED','ROLL_SET') AND LEFT(V.CCYPAIR,3) = CP.QUOTEDCCYID THEN SUM(MTM.MTMAMOUNT) 
				ELSE 0
			END
		ELSE
			CASE	
				WHEN V.TRADECODE IN ('SETTLED','ROLL_SET')THEN SUM(V.MTMLIMITCCYAMOUNT) 
				ELSE 0
			END		
	END MTMREALIZED,
	V.EXTERNALCUSTOMERREFID1,
	V.EXTERNALCUSTOMERREFID2,
	V.INTEXTIND,
	CASE 
		WHEN V.PRODUCTID = 'FXOTC' /* and LEFT(V.DIRECTION,1) = 'B' */ THEN 'N'
		WHEN V.TRADECODE = 'PREMSET' then 'N'
		WHEN LEFT(V.CCYPAIR,3) <> CP.QUOTEDCCYID THEN 'Y'               
		ELSE 'N'
	END EXCLUDECROSSIND,
    CASE 
		WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) <> V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN SUM(MTM.MTMAMOUNT)
        WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) = V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN 0
        ELSE SUM(V.MTMLIMITCCYAMOUNT) 
    END MTMLIMITCCYAMOUNT,
	sum(V.CCY1LIMITCCYAMOUNT) as CCY1LIMITCCYAMOUNT,
	sum(V.CCY1LIMITCCYOTHERAMOUNT) as CCY1LIMITCCYOTHERAMOUNT,
	sum(V.CCY2LIMITCCYAMOUNT) as CCY2LIMITCCYAMOUNT,
	sum(V.CCY2LIMITCCYOTHERAMOUNT) as CCY2LIMITCCYOTHERAMOUNT,
	sum(V.FEELIMITCCYAMOUNT) as FEELIMITCCYAMOUNT,
	sum(V.PREMLIMITCCYAMOUNT) as PREMLIMITCCYAMOUNT,
	sum(V.PAYOUTLIMITCCYAMOUNT) as PAYOUTLIMITCCYAMOUNT,
	sum(V.LOWERPAYOUTLIMITCCYAMOUNT) as LOWERPAYOUTLIMITCCYAMOUNT,
	V.CCY1LIMITCCYRATE
FROM         
	MARGINEODOPENTRADES V 	
		INNER JOIN CUSTOMER C ON
			V.CUSTOMERMNEMONIC = C.CUSTOMERMNEMONIC	
		 INNER JOIN CURRENCYPAIR CP ON
				((CP.CCYID1 = LEFT(V.CCYPAIR,3) AND CP.CCYID2 = RIGHT(V.CCYPAIR,3)) or
				(CP.CCYID1 = RIGHT(V.CCYPAIR,3) AND CP.CCYID2 = LEFT(V.CCYPAIR,3)))	
				and CP.ORGUNITID = 1
         LEFT OUTER JOIN TEXTMARGINOPENPOS_MTM MTM ON
				V.TRADENUMBER = MTM.TRADENUMBER AND
                V.ENTITYID = MTM.ENTITYID AND
                V.PRODUCTID = MTM.PRODUCTID AND 
                V.MTMCCYID = MTM.MTMCCYID AND
                V.TRADECODE = MTM.TRADECODE AND 
                V.VALUEDATE = MTM.VALUEDATE AND     
                V.GUID = MTM.GUID	
where

 v.TRADENUMBER = '311000375'
--and (v.TRADECODE = 'EXISTING' OR (v.TRADECODE = 'NEW'))
and v.TRADECODE in ('EXISTING','NEW')



/*WHERE;
	((LEFT(CCYPAIR,3) <> LD.LIMITCCYID AND RIGHT(CCYPAIR,3) = LD.LIMITCCYID) 
	OR (RIGHT(CCYPAIR,3) <>  LD.LIMITCCYID AND LEFT(CCYPAIR,3) <> LD.LIMITCCYID))*/		
GROUP BY
	V.GUID,
	V.ENTITYID,
	V.CUSTOMERMNEMONIC,
	V.PRODUCTID,
	V.TRADENUMBER,
	V.TRADEDATE,
	V.VALUEDATE,
	V.CCYPAIR,	
	V.MTMCCYID,
	LEFT(CCYPAIR,3),
	V.MTMSPOTRATE,
	V.MTMCCY1DF,
	V.MTMCCY2DF,
	V.TRADERATE,
	V.TRADEDATE,
	V.TRADECODE,
	V.FEECCYID,
	V.CCY1REVALRATE,
	V.CCY2REVALRATE,
	V.OPTIONEXPOSURETYPE,
	V.STRIKEPRICE,
	V.PREMIUMDATE,
	V.PREMIUMCCYID,
	V.PREMIUMAMOUNT,
	V.DIRECTION,
	V.EXTERNALCUSTOMERREFID1,
	V.EXTERNALCUSTOMERREFID2,
	V.INTEXTIND,
	CP.QUOTEDCCYID,
	V.CCY1LIMITCCYRATE,
	V.MTMLIMITCCYAMOUNT
)

Open in new window

Comment
Watch Question

GanapathiFacets Developer

Commented:
A bit of confusion here.

You want to pull the data for the given TRADECODE. It may be New or Existing.

But I am unable to understand this "however if its Existing to ignore the rows with value NEW" statement.

What you wanted to say here?

Author

Commented:
it is very confusing... don't worry about that..

I have cheated for the meantime and am removing it using vb.net

heres my code

        Dim sqlstr = TextBox1.Text
        Dim dt As New DataTable
        Dim Existing As Boolean = False
        Try
            With sqlcmd
                .Connection = sqlconn
                .CommandText = TextBox1.Text
                .CommandType = CommandType.Text
            End With
            sqlconn.ConnectionString = connstr
            sqlconn.Open()

            Using da As New SqlDataAdapter(sqlcmd)
                da.Fill(dt)
            End Using

            If dt.Rows.Count > 0 Then
                For Each dr As DataRow In dt.Rows
                    If dr.Item(4).ToString = "EXISTING" Then
                        Existing = True
                    End If

                Next

            End If

            If Existing = True Then
                For Each dr As DataRow In dt.Rows
                    If dr.Item(4).ToString = "NEW" Then
                        dr.Delete()

                    End If

                Next

            End If

            DataGridView1.DataSource = dt

        Catch ex As Exception

        End Try
   

        sqlconn.Close()

    End Sub

Open in new window

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you,

we have been given the answer by the vendor now, but I have tested the code and am happy with that thank you.

the way this is done is pretty messed up and the person who created the database should be shot

Author

Commented:
thank you

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.