SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need to adjust the data of this view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AdamWest
Constraint Violating Yak Guru

USA
353 Posts

Posted - 08/16/2013 :  08:44:58  Show Profile  Reply with Quote
I have a problem, it's more of a ERP data issue but as you can see here we select the 2 of warehouse location types (PAL, RAK ) from one table (ICBLDTIR). However, the user is able to change the other type (PCK) which we get from the second table (ICBALMIE). He changed a PCK there to a RAK. Now the Crystal report is getting a duplicate location. Since this can happen for purposes of report we only want to show locations and QTY. So I was thinking is there a way to code in this view that, If we already have a same location (location is the 3 columns IRLOC1-3 or IELOC1-3 so if on the second table we already have that location then dont' include it a second time.
True the ERP should not allow it, but it's got that possibility.

CREATE VIEW astccdta.acbalmpk AS (
(SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'')
as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#,
'' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as
IELOC3, '' as IERIDC, '' as IEWHS#
FROM
(SELECT LMLTPC, LMLOC1, LMLOC2, LMLOC3 FROM ASTDTA.ICLOCMLM WHERE
LMLTPC IN ('PAL', 'RAK' ) )t1
left outer join
(SELECT IRLOC1, IRLOC2, IRLOC3, IRPRT#, IRQOH#, IRWHS# FROM
ASTDTA.ICBLDTIR ) t2
On LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 )
UNION ALL
(SELECT ' ' as LMLTPC, ' ' as IRLOC1, ' ' as IRLOC2, ' ' as IRLOC3,
'' as IRPRT#, '.00' as IRQOH#, '' as IRWHS#, IEPRT#, IEQOH#, IELOC1,
IELOC2, IELOC3, IERIDC,
IEWHS# FROM ASTDTA.ICBALMIE) )


bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/16/2013 :  08:46:29  Show Profile  Reply with Quote
Can you provide sample data and expected output for that data...?

--
Chandu
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

USA
353 Posts

Posted - 08/16/2013 :  08:53:32  Show Profile  Reply with Quote
Aisle Bay Pos Type Part# Part Desc Vendor QTH
11 05 E01 RAK PAMY811 8x11" MYLAR SHEETS OL9885 456
11 05 E01 RAK PAMY811 8x11" MYLAR SHEETS OL9885 456


You see they 2 rows are coming from 2 diff tables. Some of the columns are coming from other tables this part is in the Crystal Report but the basic data is coming from that view.


quote:
Originally posted by bandi

Can you provide sample data and expected output for that data...?

--
Chandu

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/16/2013 :  11:50:11  Show Profile  Reply with Quote
I'm not sure why asking for sample data is like pulling teath. But, here ae some links that can help you prepare your question (with sample data) so that others can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000