Adding new column to view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding new column to view

Author

Topic

edhead
Starting Member

3 Posts

Posted - 2012-10-18 : 17:49:36

I have a view built and want to add a new custom column that contains the result of an expression.

The new column would be 'rework' with an expression of wodt.fitem = wodt.partno THEN wodt.qty ELSE 0

Below is the code used to create the view, I thought I could do it with a CASE statement but can't seem to get it correct. What do I need to do to create this column into the view?
Thanks!

SELECT wo.batqty AS [Batch Qty], wo.bin, wo.clsdate AS [Work Order Closed Date], wo.compqty AS [Completed Qty], wo.custno AS [Customer #],
wo.custreqdte AS [Customer Required Date], wo.item AS [Manufactured Item], wo.loctid AS [Mfg Location Id], wo.lupdate AS [Last Update Date],
wo.overhead AS [Overhead Code], wo.projqty AS [Needed Qty], wo.qty AS [Mfg Allocated Qty], wo.rawloc AS [Raw Material Location], wo.reqdte AS [Required Date],
wo.route AS [Route Id], wo.schedpri AS [Schedule Priority], wo.schedule AS [Schedule Type], wo.sono AS [SO #], wo.status AS [WO Status], wo.strtdte AS [Start Date],
wo.version AS [BOM Version], wo.wono AS [Work Order #], wo.wostat AS [Work Order Status], wo.yield, wo.amount, wo.ctrno AS [Work Center #],
wo.flotno AS [Manufactured Lot #], wo.fovamt AS [Fixed Overhead Amount], wo.labamt AS [Labor Amount], wo.lbrtype AS [Labor Type], wo.lotno AS [Lot #],
wo.machhrs AS [Machine Hours], wo.matamt AS [Material Amount], wo.partno AS [Item #], wo.phantom AS [Phantom Y/N], wo.phitem AS [Phantom Parent Item],
wo.dqty AS [Issued Qty], wo.qtyaloc AS [Allocated Qty], wo.rtestep AS [Route Step], wo.scrap, wo.sfovamt AS [Std Fixed Overhead Amount],
wo.slabamt AS [Std Labor Overhead Amount], wo.smatamt AS [Std Material Overhead Amount], wo.svovamt AS [Std Variable Overhead Amount],
wo.trancode AS [Transaction Type], wo.trandte AS [Transaction Date], wo.trantime AS [Transaction Time], wo.ttranno AS [Transaction #],
wo.vovamt AS [Variable Overhead Amount], i.itmdesc AS [Mfg Item Description], i.plinid AS [Product Line], i.itmclss AS [Mfg Item Class], i.code AS [Mfg Item Misc Code],
ic.itmdesc AS [Item Description], ic.itmclss AS [Item Class], ic.code AS [Item Misc Code],
CASE wo.tranCode WHEN 'MI' THEN wo.dqty WHEN 'MR' THEN wo.dqty * - 1 ELSE 0 END AS [Material Issued]
FROM (SELECT h.adddate, h.addtime, h.adduser, h.batqty, h.bin, h.bmdtid, h.bmhdid, h.bomrev, h.clsdate, h.compqty, h.custno, h.custreqdte, h.dynforder, h.item, h.lckdate,
h.lckstat, h.lcktime, h.lckuser, h.loctid, h.lot, h.lupdate, h.note, h.overhead, h.projqty, h.qty, h.rawloc, h.reqdte, h.rft_note, h.rodtid, h.rohdid, h.route,
h.schedpri, h.schedule, h.serial, h.sono, h.status, h.store, h.strtdte, h.tranlineno, h.type, h.version, h.wono, h.wostat, h.yield, d.amount, d.bqty, d.chkdate,
d.chktime, d.chkuser, d.cr_acct, d.ctrno, d.dr_acct, d.fgpost, d.findnum, d.fitem, d.flotno, d.fovamt, d.ingrid, d.labamt, d.lbrtype, d.lotno, d.machhrs, d.matamt,
d.partno, d.pfindnum, d.phantom, d.phitem, d.qty AS dqty, d.qtyaloc, d.rdlot, d.rtestep, d.scrap, d.seq, d.sfovamt, d.slabamt, d.smatamt, d.source, d.subitem,
d.svovamt, d.trancode, d.trandte, d.tranrec, d.trantime, d.ttranno, d.vovamt
FROM dbo.ppwohd AS h INNER JOIN
dbo.ppwodt AS d ON h.wono = d.wono
UNION ALL
SELECT h.adddate, h.addtime, h.adduser, h.batqty, h.bin, h.bmdtid, h.bmhdid, h.bomrev, h.clsdate, h.compqty, h.custno, h.custreqdte, h.dynforder, h.item, h.lckdate,
h.lckstat, h.lcktime, h.lckuser, h.loctid, h.lot, h.lupdate, h.note, h.overhead, h.projqty, h.qty, h.rawloc, h.reqdte, h.rft_note, h.rodtid, h.rohdid, h.route,
h.schedpri, h.schedule, h.serial, h.sono, h.status, h.store, h.strtdte, h.tranlineno, h.type, h.version, h.wono, h.wostat, h.yield, d.amount, d.bqty, d.chkdate,
d.chktime, d.chkuser, d.cr_acct, d.ctrno, d.dr_acct, d.fgpost, d.findnum, d.fitem, d.flotno, d.fovamt, d.ingrid, d.labamt, d.lbrtype, d.lotno, d.machhrs, d.matamt,
d.partno, d.pfindnum, d.phantom, d.phitem, d.qty AS dqty, d.qtyaloc, d.rdlot, d.rtestep, d.scrap, d.seq, d.sfovamt, d.slabamt, d.smatamt, d.source, d.subitem,
d.svovamt, d.trancode, d.trandte, d.tranrec, d.trantime, d.ttranno, d.vovamt
FROM dbo.ppywhd AS h INNER JOIN
dbo.ppywdt AS d ON h.wono = d.wono) AS wo INNER JOIN
dbo.icitem AS i ON wo.item = i.item LEFT OUTER JOIN
dbo.icitem AS ic ON wo.partno = ic.item

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 21:42:27

have you posted the full code? i cant even spot table with alias wodt in current query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edhead
Starting Member

3 Posts

Posted - 2012-10-19 : 16:43:31

Sorry was going cross eyed by the time I typed that out. Correct expression should be d.fitem = d.partno THEN d.qty ELSE 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 22:26:50

[code]
SELECT wo.batqty AS [Batch Qty], wo.bin, wo.clsdate AS [Work Order Closed Date], wo.compqty AS [Completed Qty], wo.custno AS [Customer #],
wo.custreqdte AS [Customer Required Date], wo.item AS [Manufactured Item], wo.loctid AS [Mfg Location Id], wo.lupdate AS [Last Update Date],
wo.overhead AS [Overhead Code], wo.projqty AS [Needed Qty], wo.qty AS [Mfg Allocated Qty], wo.rawloc AS [Raw Material Location], wo.reqdte AS [Required Date],
wo.route AS [Route Id], wo.schedpri AS [Schedule Priority], wo.schedule AS [Schedule Type], wo.sono AS [SO #], wo.status AS [WO Status], wo.strtdte AS [Start Date],
wo.version AS [BOM Version], wo.wono AS [Work Order #], wo.wostat AS [Work Order Status], wo.yield, wo.amount, wo.ctrno AS [Work Center #],
wo.flotno AS [Manufactured Lot #], wo.fovamt AS [Fixed Overhead Amount], wo.labamt AS [Labor Amount], wo.lbrtype AS [Labor Type], wo.lotno AS [Lot #],
wo.machhrs AS [Machine Hours], wo.matamt AS [Material Amount], wo.partno AS [Item #], wo.phantom AS [Phantom Y/N], wo.phitem AS [Phantom Parent Item],
wo.dqty AS [Issued Qty], wo.qtyaloc AS [Allocated Qty], wo.rtestep AS [Route Step], wo.scrap, wo.sfovamt AS [Std Fixed Overhead Amount],
wo.slabamt AS [Std Labor Overhead Amount], wo.smatamt AS [Std Material Overhead Amount], wo.svovamt AS [Std Variable Overhead Amount],
wo.trancode AS [Transaction Type], wo.trandte AS [Transaction Date], wo.trantime AS [Transaction Time], wo.ttranno AS [Transaction #],
wo.vovamt AS [Variable Overhead Amount], i.itmdesc AS [Mfg Item Description], i.plinid AS [Product Line], i.itmclss AS [Mfg Item Class], i.code AS [Mfg Item Misc Code],
ic.itmdesc AS [Item Description], ic.itmclss AS [Item Class], ic.code AS [Item Misc Code],
CASE wo.tranCode WHEN 'MI' THEN wo.dqty WHEN 'MR' THEN wo.dqty * - 1 ELSE 0 END AS [Material Issued],
CASE WHEN wo.fitem = wo.partno THEN wo.qty ELSE 0 END
FROM (SELECT h.adddate, h.addtime, h.adduser, h.batqty, h.bin, h.bmdtid, h.bmhdid, h.bomrev, h.clsdate, h.compqty, h.custno, h.custreqdte, h.dynforder, h.item, h.lckdate,
h.lckstat, h.lcktime, h.lckuser, h.loctid, h.lot, h.lupdate, h.note, h.overhead, h.projqty, h.qty, h.rawloc, h.reqdte, h.rft_note, h.rodtid, h.rohdid, h.route,
h.schedpri, h.schedule, h.serial, h.sono, h.status, h.store, h.strtdte, h.tranlineno, h.type, h.version, h.wono, h.wostat, h.yield, d.amount, d.bqty, d.chkdate,
d.chktime, d.chkuser, d.cr_acct, d.ctrno, d.dr_acct, d.fgpost, d.findnum, d.fitem, d.flotno, d.fovamt, d.ingrid, d.labamt, d.lbrtype, d.lotno, d.machhrs, d.matamt,
d.partno, d.pfindnum, d.phantom, d.phitem, d.qty AS dqty, d.qtyaloc, d.rdlot, d.rtestep, d.scrap, d.seq, d.sfovamt, d.slabamt, d.smatamt, d.source, d.subitem,
d.svovamt, d.trancode, d.trandte, d.tranrec, d.trantime, d.ttranno, d.vovamt,d.fitem,d.partno ,d.qty
FROM dbo.ppwohd AS h INNER JOIN
dbo.ppwodt AS d ON h.wono = d.wono
UNION ALL
SELECT h.adddate, h.addtime, h.adduser, h.batqty, h.bin, h.bmdtid, h.bmhdid, h.bomrev, h.clsdate, h.compqty, h.custno, h.custreqdte, h.dynforder, h.item, h.lckdate,
h.lckstat, h.lcktime, h.lckuser, h.loctid, h.lot, h.lupdate, h.note, h.overhead, h.projqty, h.qty, h.rawloc, h.reqdte, h.rft_note, h.rodtid, h.rohdid, h.route,
h.schedpri, h.schedule, h.serial, h.sono, h.status, h.store, h.strtdte, h.tranlineno, h.type, h.version, h.wono, h.wostat, h.yield, d.amount, d.bqty, d.chkdate,
d.chktime, d.chkuser, d.cr_acct, d.ctrno, d.dr_acct, d.fgpost, d.findnum, d.fitem, d.flotno, d.fovamt, d.ingrid, d.labamt, d.lbrtype, d.lotno, d.machhrs, d.matamt,
d.partno, d.pfindnum, d.phantom, d.phitem, d.qty AS dqty, d.qtyaloc, d.rdlot, d.rtestep, d.scrap, d.seq, d.sfovamt, d.slabamt, d.smatamt, d.source, d.subitem,
d.svovamt, d.trancode, d.trandte, d.tranrec, d.trantime, d.ttranno, d.vovamt,d.fitem,d.partno ,d.qty
FROM dbo.ppywhd AS h INNER JOIN
dbo.ppywdt AS d ON h.wono = d.wono) AS wo
INNER JOIN
dbo.icitem AS i ON wo.item = i.item LEFT OUTER JOIN
dbo.icitem AS ic ON wo.partno = ic.item
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edhead
Starting Member

3 Posts

Posted - 2012-10-29 : 15:16:46

Thanks, I forgot the END on my CASE. All good now!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 16:37:30

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources