Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

SQL Select create column

Hi Experts

I need to create a SQL Select that will pull all the fields as per the code below and have an additional field based on the field Pay or Receive.

If field is Pay new field says 'Pay by Position'
If field says receive, new field says pay by customer

can you please help

select p.PORTFOLIOMNEMONIC, td.TRADEDIRECTION, cf.CUSTOMERID, cf.CUSTOMERMNEMONIC, TP.TRADECCY1AMOUNT
from CUSTOMERFAMILY cf 
left join CUSTOMER c on c.CUSTOMERID = cf.CUSTOMERID 
left join PORTFOLIO p on p.VERIFICATIONCODE = c.VERIFICATIONCODE
left join TRADEDETAIL td on td.CUSTOMERID = c.CUSTOMERID
left join TRADEPOSITION tp on td.TRADEDIRECTION = tp.TRADEDIRECTION 
where 
--p.PORTFOLIOMNEMONIC in( 'MAIN') and 
cf.CUSTOMERID like '%IFE%' and
td.tradedirection in( 'PAY', 'RECEIVE')

Open in new window

0
SimonPrice33
Asked:
SimonPrice33
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
select 
   p.PORTFOLIOMNEMONIC, 
   td.TRADEDIRECTION, 
   cf.CUSTOMERID, 
   cf.CUSTOMERMNEMONIC, 
   TP.TRADECCY1AMOUNT, 
   CASE td.tradedirection     -- < -- Looky here
      WHEN 'PAY' THEN 'Pay by Position' 
      WHEN 'RECEIVE' THEN 'Pay by Customer' END as column_name_goes_here
from CUSTOMERFAMILY cf 
   left join CUSTOMER c on c.CUSTOMERID = cf.CUSTOMERID 
   left join PORTFOLIO p on p.VERIFICATIONCODE = c.VERIFICATIONCODE
   left join TRADEDETAIL td on td.CUSTOMERID = c.CUSTOMERID
   left join TRADEPOSITION tp on td.TRADEDIRECTION = tp.TRADEDIRECTION 
where cf.CUSTOMERID like '%IFE%' and td.tradedirection in( 'PAY', 'RECEIVE')

Open in new window

For more info on CASE blocks there's an article out there on SQL Server CASE Solutions if that helps.
0
 
SimonPrice33Author Commented:
awesome... i thought it would be a case but wasnt quite sure how i would write it

will letyou konw how it works.
0
 
SimonPrice33Author Commented:
works a charm!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now