How can I do this in a single query? 2

[AjitG]

Hi,

Data is extracted from a single table. On giving query on Trantype='IS' I get this output
Ref KeyRef Product Quantity TranType
I95 Q1 PF1 9 IS
I95 Q1 PF2 12 IS
I95 Q1 PF3 6 IS

On giving query on Trantype='PR' I get this output
Ref KeyRef Product Quantity TranType
P63 I95 PF102 2 PR
P63 I95 PF202 3 PR
P63 I95 PF302 2 PR
P81 I95 PF102 5 PR
P81 I95 PF202 4 PR

Is it possible using a single query to merge these 2 outputs(queries) and get an output as this :
Ref KeyRef Product Quantity Ref Product Quantity
I95 Q1 PF1 9 P63 PF102 2
I95 Q1 PF2 12 P63 PF202 3
I95 Q1 PF3 6 P63 PF302 2
I95 - - - P81 PF102 5
I95 - - - P81 PF202 4
The join is on Ref and KeyRef in the same table. A report is finally generated using this query which will show the total of quantities for trantype 'IS' and 'PR'.

 

[tlbroadbent]

Use a left join query to join the two queries as follows. Note that I've aliased some the columns so they can be distinguished in the final result.

Select
Ref=PR.KeyRef,
IS.KeyRef,
IS.Product,
IS.Quantity,
PRRef=PR.Ref,
PRProduct=PR.Product,
PRQuantity=PR.Quantity
From
(Select
Ref, KeyRef, Product,
Quantity, TranType
From YourTable
Where Trantype='PR') PR
Left Join
(Select
Ref, KeyRef, Product,
Quantity, TranType
From YourTable
Where TranType='IS') IS
On PR.KeyRef=IS.Ref Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

Terry, nice suggestion. I think the table alias IS is a keyword, and will probably need to be changed to something else (ISS?)

bp

 

[tlbroadbent]

Brian,

Thanks for catching that. I know better just let it slip by. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

Terry:
uh, I'm getting 15 lines of output, instead of the 5 that AjitG seems to want. This is a tough query (!).


AjitG:
(1) Is this basically a 2-column report, where you want to list the IS tranactions down the left column, and the PR transactions down the left column?

(2) It sort of seems like the item of interest is the Ref, in this case I95. i.e. Would a way to state this query be:

"Here's a Ref(example I95). List me all IS transactions with that Ref down the left side, and all PR transactions with that KeyRef down the right side"

Or if not, perhaps you could try to put it into words for us.
thanx
bp

 

[bperry]

>>Thanks for catching that.
>>I know better just let it slip by

I'm sure you were just testing us!

I'm sorry I wasn't clear a few moments ago. I was meaning that when I run your query here on my machine, I get 15 records in the resultset instead of just 5 that I think he wants.

 

[bperry]

AjitG:
My apologies, I am not thinking straight today and typing confusing messages....

With regard to PR transactions I meant the RIGHT column

(1) Is this basically a 2-column report, where you want to list the IS tranactions down the left column, and the PR transactions down the RIGHT column?

I must learn to proofread what I type. Again, sorry for the confusion there.

bp

 

[tlbroadbent]

The following works but I hope there is a better solution. Could you post the schema of the underlying table? Are the columns you've posted the only columns in the table? Is there a unique column on the table?

------------------------------------------
Set nocount on

Create table #tblpr
(Ref char(3), PrRef char(3),
PrProduct varchar(6), PRQuantity int,
RowNo Int Identity)
Create table #tblis
(Ref char(3), ISKeyRef char(3),
ISProduct varchar(6), ISQuantity int,
RowNo Int Identity)

Insert #tblpr(Ref, PRRef, PRProduct, PRQuantity)
Select KeyRef, Ref, Product, Quantity
From YourTable Where Trantype='PR'

Insert #tblis(Ref, ISKeyRef, ISProduct, ISQuantity)
Select Ref, KeyRef, Product, Quantity
From YourTable Where TranType='IS'

Select
p.Ref,
ISKeyRef=IsNull(ISKeyRef,''),
ISProduct=IsNull(ISProduct,''),
ISQuantity=IsNull(ISQuantity,0),
PRRef,
PRProduct,
PRQuantity
From #tblpr p
Left Join #tblis i
On p.ref=i.ref
And p.RowNo=i.RowNo

Drop table #tblpr
Drop table #tblis
set nocount off Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

>> terry asked:
>> Is there a unique column on the table?

Yes, I was wondering that too. But if there a lot records in this table, he may be better off anyway with the 'temp table' approach that you are suggesting.
bp

 

[bperry]

Hi Terry:
Congrats...that's a beautiful query you have suggested for AjitG.

Please excuse me mentioning one small glitch. In the sample data shown, there are more PRs than ISs: therefore, the left join from PR to IS will work great, because we will run out of ISs before PRs.

But if we get some different data were there are more ISs than PRs, then those extra ISs will not get picked up, due to the left join.

May I suggest changing the left join to a Full Outer Join, so that all records from the temp tables get picked up regardless of which tran type there are more of.

Other than that, she's a dandy.

 

[AjitG]

Many thanks to Terry & Brian for suggesting different ways to solve my query.

Unfortunately since I am not in the office I can not try this out. I will be checking this out tomorrow and will keep you guys posted on the results...

And yes Brian - my requirement is as suggested by you i.e.,
(1) Is this basically a 2-column report, where you want to list the IS tranactions down the left column, and the PR transactions down the RIGHT column?

AG

 

[AjitG]

Thanks again Terry & Brian. The query worked great.

I have modified Terry's query to reflect the one that I am using in the live environment.I guess this will make things clear for everyone.

I for one am not clear as to the use of RowNo Int Identity. If I remove this, the query goes for a toss. How does this work?? This is the first time I have seen this being used in such a situation.


-----------------------------------------------------------
set nocount on

Drop table #tblpr
Drop table #tblis


Create table #tblpr
(Ref char(10), PrRef char(10), PrProduct char(20), PrQuantity float, RowNo Int Identity)

Create table #tblis
(Ref char(10), IsKeyRef char(10), IsProduct char(20), IsQuantity float, RowNo Int Identity)

Insert #tblpr(Ref, PrRef, PrProduct, PrQuantity)
Select lot_number, movement_reference, product, movement_quantity
From stkhstm Where transaction_type = 'PROD' and month(dated)=03

Insert #tblis(Ref, IsKeyRef, IsProduct, IsQuantity)
Select movement_reference, lot_number, product, movement_quantity
From stkhstm Where transaction_type = 'ISSU' and month(dated)=03

Select
p.Ref,
ISKeyRef=IsNull(i.IsKeyRef,''),
ISProduct=IsNull(i.IsProduct,''),
ISQuantity=IsNull(i.IsQuantity,0),
PrRef,
PrProduct,
PrQuantity
From #tblpr p
Left Join #tblis i
On p.Ref=i.Ref
And p.RowNo=i.RowNo
order by p.Ref

Drop table #tblpr
Drop table #tblis
set nocount off
-----------------------------------------------------

 

[bperry]

>>I for one am not clear as to
>>the use of RowNo Int Identity

Hi there.
Terry is using that mechanism to automatically number each row in both of the temporary tables tblIS and tblPR. In other words, all the rows in the tblIS table will be automatically numbered from 1 to n. And all the rows in the tblPR table will also be numbered from 1 to n.

Then these RowNo columns are used to link the two tables together. Imagine looking at a sheet of paper, and all the IS trans are listed down the left column (line1, line2, line3, etc) And all the PR trans are listed down the right column (line1, line2, line3, etc)

Now, how to print/output one line that has a row from one table on the left, and a row from the other table on the right?? By linking the two tables by RowNo!! It's pretty cool, wouldn't you say?

That's why yesterday I suggested changing the Left Join to a Full Outer join. With the Left Join, as the rows are displayed 'down the page' (if I can say it like that), the output will stop as soon as one side runs out of rows. But if the Full Outer Join is used, then all rows from both sides get outputed.

Terry deserves a star for this one.

Rgrds, etc
brian perry


(Terry, was my explanation okay?)