z3llin
Epic Member
So SQL is not exactly my ball park & the Tables I have to work with cant be modified.
The following provides ViewD:
Now I need to add information from TableB in to get ViewE.
I can join on the customer and store without an issue, but how do I break out DefiningField values into different columns?
If I use a WHERE DefiningField = 'Is Real' I can return one of the columns, but not both.
TableA
Document,Customer,Store
DOC01,CUST01,STORE2
DOC02,CUST01,STORE2
DOC03,CUST02,STORE1
DOC04,CUST02,STORE1
TableB
Customer,Store,DefiningField,Value
CUST01,STORE2,"Is Real",TRUE
CUST01,STORE2,"STAFF",7
CUST02,STORE1,"Is Real",FALSE
CUST02,STORE1,"STAFF",3
TableC
Document,RandomInfo,Blah1,Blah2,Blah3
DOC01,RandomInfo1,blah,blah,blah
DOC02,RandomInfo2,blah,blah,blah
DOC03,RandomInfo3,blah,blah,blah
DOC04,RandomInfo4,blah,blah,blah
The following provides ViewD:
Select TableA.Customer,TableA.Store,TableA.Store,TableC.RandomInfo,TableC.Blah1,
From TableA LEFT OUTER JOIN TableC ON TableA.Document = TableC.Document
ViewD
Customer,Store,Document,RandomInfo
CUST01,STORE2,DOC01,RandomInfo1
CUST01,STORE2,DOC02,RandomInfo2
CUST01,STORE1,DOC03,RandomInfo3
CUST01,STORE1,DOC04,RandomInfo4
Now I need to add information from TableB in to get ViewE.
ViewE
Customer,Store,Document,RandomInfo,"Is Real","STAFF"
CUST01,STORE2,DOC01,RandomInfo1,Blah,TRUE,7
CUST01,STORE2,DOC02,RandomInfo2,Blah,TRUE,7
CUST01,STORE1,DOC03,RandomInfo3,Blah,FALSE,3
CUST01,STORE1,DOC04,RandomInfo4,Blah,FALSE,3
I can join on the customer and store without an issue, but how do I break out DefiningField values into different columns?
If I use a WHERE DefiningField = 'Is Real' I can return one of the columns, but not both.