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?