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
)
```

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?

This one is on us!

(Get your first solution completely free - no credit card required)

UNLOCK SOLUTION
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

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

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

- Troubleshooting
- Research
- Professional Opinions

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.