SimonPrice33
asked on
sql select - where column has more than two useable valuues
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.
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
)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
thank you
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?