I have created 2 feed tables which 1

[Cpreston]

I have created 2 feed tables which collate information on orders.

Each order as a line type from 1 to 3.

Line type 1 gives me the Quantity but not the name and sub name
Line Type 2 gives me the Name and Sub Name but not the Quantity.

I need to create a view using both tables so I can see the quantity and Name and sub name. I cannot work out how to do this due to the different line types. The line types are from tables that are designed by our systems people.

As anyone and idea how I can do this please. I have attached code which at the moment shows the quantity but not the names. I have tried all kinds of joins but no luck, thanks in advance

SELECT        dbo.[148-vwWOVolumewaitingforprocessFeed1CP].WorksOrderLineID, dbo.[148-vwWOVolumewaitingforprocessFeed1CP].Quantity, dbo.[148-vwWOVolumewaitingforprocessFeed2CP].Name, 
                         dbo.[148-vwWOVolumewaitingforprocessFeed2CP].SubName
FROM            dbo.[148-vwWOVolumewaitingforprocessFeed1CP] LEFT OUTER JOIN
                         dbo.[148-vwWOVolumewaitingforprocessFeed2CP] ON dbo.[148-vwWOVolumewaitingforprocessFeed1CP].WorksOrderLineID = dbo.[148-vwWOVolumewaitingforprocessFeed2CP].WorksOrderLineID

 

[Andrzejek]

It would be more helpful if you would provide a sample of the 2 tables with some data representing the problem, and the sample of the outcome out of those 2 tables you expect to get.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi Ok sorry I will try and explain better and thanks for the reply.

The first feed query gives me the following result and the code is also below

Feed 1 as the following code

SELECT        dbo.WorksOrderLine.WorksOrderLineID, dbo.WorksOrderLine.LineType, dbo.WorksOrderLine.WorksOrderID, dbo.WorksOrderHeader.WorksOrderStatus, dbo.WorksOrderLine.Quantity, dbo.AVO.Name, 
                         dbo.AVOOption.Name AS SubName
FROM            dbo.WorksOrderLine LEFT OUTER JOIN
                         dbo.AVOOption ON dbo.WorksOrderLine.AVOOptionID = dbo.AVOOption.AVOOptionID LEFT OUTER JOIN
                         dbo.AVO ON dbo.WorksOrderLine.AVOID = dbo.AVO.AVOID LEFT OUTER JOIN
                         dbo.WorksOrderHeader ON dbo.WorksOrderLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID
WHERE        (dbo.WorksOrderLine.WorksOrderID = 246171) AND (dbo.WorksOrderLine.LineType = 1)

And as the output of

The second feed as the code of

SELECT        dbo.WorksOrderLine.WorksOrderLineID, dbo.WorksOrderLine.LineType, dbo.WorksOrderLine.WorksOrderID, dbo.WorksOrderHeader.WorksOrderStatus, dbo.WorksOrderLine.Quantity, dbo.AVO.Name, 
                         dbo.AVOOption.Name AS SubName
FROM            dbo.WorksOrderLine LEFT OUTER JOIN
                         dbo.AVOOption ON dbo.WorksOrderLine.AVOOptionID = dbo.AVOOption.AVOOptionID LEFT OUTER JOIN
                         dbo.AVO ON dbo.WorksOrderLine.AVOID = dbo.AVO.AVOID LEFT OUTER JOIN
                         dbo.WorksOrderHeader ON dbo.WorksOrderLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID
WHERE        (dbo.WorksOrderLine.WorksOrderID = 246171) AND (dbo.WorksOrderLine.LineType = 2)

This give the result of

What is missing in the Feed 2 is the Quantity, this is due to the Line Type and what is available in the tables in the database I am drawing the data from. If I had the quantity showing in Feed 2 for example I would have the full data required.

Hope this explains things better, thanks.

 

[Cpreston]

Hi

Been trying a few things. One thin I have noticed I should have tried joining on the worksorderid and not the worksorderlineid. The worksorderlineid changes as the line status changes so they would never match.

As you can see from the view I get duplicate row results, if I could get rid of the duplicate rows then this would work. Any ideas please, many thanks

 

[Cpreston]

Hi

Ok tried some more and tried to make it simple and used the below SQL.

Select Distinct 
	NAME,
	SubName,
	Worksorderid,
	worksorderstatus,
	Line,
	LineType
 	--Quantity
	from [148-vwWOVolumewaitingforprocessFeed3CP]

Without Quantity it results with 2 expected lines

If I activate the Quantity filed I then get 4 lines

I cannot see why the Quantity is causing the issue, sorry for the amount of information I have posted. Any help would be grateful. Thanks

 

[Andrzejek]

Select Distinct 
	NAME,
	SubName,
	Worksorderid,
	worksorderstatus,
	Line,
	LineType[blue]
(Select Quantity From AnotherTable Where...) As[/blue] Quantity
	from [148-vwWOVolumewaitingforprocessFeed3CP]

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Sorry struggling with the code.

I have the below

Select Distinct
	NAME,
	SubName,
	Worksorderid,
	worksorderstatus,
	Line,
	LineType,
 	(Select Quantity from dbo.WorksOrderLine where  dbo.WorksOrderLine.LineType = 1 )AS Quantity
	from [148-vwWOVolumewaitingforprocessFeed3CP]

I am getting this error

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

No 100% sure I have the sub select statement correct, will keep trying. THanks

 

[Andrzejek]

Yes, the 'sub select' has to return just one record, otherwise the error.
You need to add some more criteria to the WHERE part of it.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Just this minute got it working, linking the worksorderid in the where statement also. This gave me the right information to use so I could link in a Crsytal report where I could group and sum things.

Thanks for your advice it is appreciated.