Solved

Need help in a sql selct (sql 2000)

Posted on 2004-04-12
13
263 Views
Last Modified: 2008-03-10
Following is a sql designed to return one row per item_code and it is doing so.
but the column op_qty is not returning right answer.



select       t_item_op.item_code,
      item.item_name,
      subname= item_sub_catagory.item_sub_catagory_name,  
      bearing_no = item.bearing_no,
      item.parts_no,
      ledger_no = item.ledger_no + '/'+ item.rpage_no,
      op_qty  = sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0)),
      rec_qty = sum(case when t_rec_iss_adj.tag in('1','3') and t_rec_iss_adj.rec_qty > 0 then
                   isnull(t_rec_iss_adj.rec_qty,0) else 0 end),
      iss_qty = sum(case when t_rec_iss_adj.tag in('2','3') and t_rec_iss_adj.iss_qty > 0 then
                   isnull(t_rec_iss_adj.iss_qty,0) else 0 end),
      bal_qty = sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0)+
              isnull(t_rec_iss_adj.rec_qty,0) - isnull(t_rec_iss_adj.iss_qty,0)),
      measure_unit = item.measure_unit,
      rate    = sum(item.rate),

      amount = sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0)+
              isnull(t_rec_iss_adj.rec_qty,0) - isnull(t_rec_iss_adj.iss_qty,0)) *  sum(item.rate),
      ledger_unit_name.ledger_unit_name,  
   ledger_unit_name.ledger_descr  

      
from
      (select item_code = item_code,
             op_qty    = op_qty
       from       str_item
       where       ledger_no = '1' ) t_item_op,

      (SELECT v1.item_code,
             op_qty = sum(v1.rec_qty - v1.iss_qty)
       FROM       v_opb_str_item2 v1
       WHERE       ( v1.trans_date < '2000-01-01')
         and   ( v1.ledger_no = '1' )
       group by v1.item_code ) t_trans_op,

-- this portion is referred below
      (select item_code,
       rec_qty = sum(rec_qty),
       iss_qty = sum(iss_qty),
       tag=tag
       from v_opb_str_item2
       where ledger_no = '1'
         and trans_date between '2000-01-01' and '2005-05-05'
       group by item_code,
            tag ) t_rec_iss_adj,
      str_item item,
      ledger_unit_name ,
      item_sub_catagory

where t_item_op.item_code *= t_trans_op.item_code
  and t_item_op.item_code *= t_rec_iss_adj.item_code
  and t_item_op.item_code = item.item_code  
  and item.ledger_no = ledger_unit_name.ledger_no
  and item_sub_catagory.item_sub_catagory_code=item.item_sub_catagory

group by
      t_item_op.item_code,
      item.item_name,
      item.parts_no,
      item.ledger_no,
      item.rpage_no,
      item.measure_unit,
      ledger_unit_name.ledger_unit_name,  
      ledger_unit_name.ledger_descr  ,
      item.bearing_no,
      item_sub_catagory_name  
having sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0)) <> 0
    or sum(case when t_rec_iss_adj.tag in('1','3') then isnull(t_rec_iss_adj.rec_qty,0) else 0 end) <> 0
    or sum(case when t_rec_iss_adj.tag in('2','3') then  isnull(t_rec_iss_adj.iss_qty,0) else 0 end) <> 0

order by right('000'+rpage_no,3)


DO NOT GO TO THE COMPLEXITY OR LENGTH OF THE ABOVE CODE JUST UNDERSTAND THE FOLLOWING

str_item table above contains hard opening against each item_code.
one item_code is '01000002' and its frist time opening quantity is 435
so /* a line from above sql*/
op_qty  = sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0))
should return
435
as no other table contain this item code when the date is less than '2000-01-01'
but it is returning
1305 (or 1740 etc) and I have found out why


a portion from the above sql

      select item_code,
       rec_qty = sum(rec_qty),
       iss_qty = sum(iss_qty),
       tag=tag
       from v_opb_str_item2
       where ledger_no = '1'
         and trans_date between '2000-01-01' and '2005-05-05'
      and item_code='01000002' ---
       group by item_code,
            tag   --t_rec_iss_adj
is returning following three rows

item_code       rec_qty  iss_qty tag
01000002      25.00      .00      1
01000002      .00      301.00      2
01000002      56.00      4.00      3

and this  join   t_item_op.item_code *= t_rec_iss_adj.item_code
is multiplying hard opening by number of rows. (435 * 3 = 1305)
I can't remove the column [tag ] as it is required in the column rec_qty and iss_qty.



TO SUM UP HOW TO AVOIDE MULTIPLICATION CAUSING BY THE FOLLOWING JOIN

  and t_item_op.item_code *= t_rec_iss_adj.item_code


Mateen:

0
Comment
Question by:Mateen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 10

Accepted Solution

by:
Shailesh15 earned 400 total points
ID: 10804947
I haven't go through your sql completely (it is complex! ... I agree) But is it possible for you to add a count (*) in the query since you already have grouping. Try...

op_qty  =  (sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0))) /count(*) .. It may work... I am not sure though.
0
 
LVL 12

Assisted Solution

by:monosodiumg
monosodiumg earned 100 total points
ID: 10805269
Hello again Mateen,

Have I not chastised you on a previous occasion for using that dodgy antiquated join syntax?? If so I apologise and you should consider yourself duly chastised ;)

Use the ANSI join syntax and it bcomes a lot easier.
You need to join and sum t_item_op and t_trans_op then join the result of that to the other tables ( t_rec_iss_adj in particular).

I'm not going to do that for you unless you first recast the =* stuff into ANSI joins.

mono
P.S. "category" not "catagory"
0
 

Author Comment

by:Mateen
ID: 10805928
Hi monosodiumg :

You r right.

When u see non ANSI join in my question then please consider that in 95 percent cases those sqls were originally created by some one else now left from my office where I am working now.

I do promise , from now on, before putting in EE I will convert those sqls from non ANSI to ANSI.
Even after my promise, if you see non ANSI Join then the questoner would not be me as this accout is used by co-workers as well.

I think

op_qty  =  (sum(isnull(t_item_op.op_qty,0) + isnull(t_trans_op.op_qty,0))) /count(*)
suggested by Shalesh15 may do the trick. Right now I can't give a try as I am using the net from net cafe (It is 8.15 p.m here).

So please wait uptill tomorrorw.

With best regards

Mateen:
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Mateen
ID: 10805950
Hi mono

I should make a habit of writing my name at the end of question.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10808923
Mateen,

Shalesh15's solution should indeed work but it's not very pretty doing the sum with N duplicates then dividing by N. Better not to avoid the problem in the first place than to repair it afterwards.

mono

0
 

Author Comment

by:Mateen
ID: 10811059
Hi mono:

Ok.
I will redesign this and join the tables with proper heirarchy.

For the question given as it is Shalesh15's solution is correct.

Now it's upto me to make it pretty. So I will redesign  and join the tables with proper heirarchy.

Thanks for your positive guidelines.
0
 

Author Comment

by:Mateen
ID: 10811106
Hi Shalesh15.

Shame to me for not being able to find so easy a trick.

This happens always to me whenever I am in time pressure.

Since there was a solution to be found out even with a person having less sql capability then I do, so I feel guilty , becuase the time spent may have been used to help other.

Mateen:
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10812824
" Since there was a solution to be found out even with a person having less sql capability then I do"

Interesting Comment!..  As monosodiumg pointed out when you end up with a query where you need to do a trick just to get the result, It's often a sign of bad SQL. You should spend more time on SQL than giving lame excuses.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10813258
Shailesh15,
 I think Mateen ws beating himself up for not spotting it himsefl.  Mateen is far from being an SQL virgin and I think he meant that someone of his skill should have spotted it himself. Saying that someone of less skill could have figured it is not meant to imply any derogatory about your skill, though the way he puts it does sound like that.

0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10813402
Yep... Now when I read again... you are right.. Though didn't like wordings when I read, first thing in the morning.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10813456
I read it same way as you did at first. Guess we can now make derogatory comments about Mateen's english ;) (just kidding Matteen!!!)
0
 

Author Comment

by:Mateen
ID: 10820286
Hi Shalesh15

It all happened due to my poor english not my native language.

I generally tend to respect every one and I have always thought  that the  TEACHERS should always be respected not only by me but by every one.

And why should I not respect u, it was u who provided me the solution very first.

I do started talking with monosodiumg although your answer came first but I did mention
to monosodiumng in the first comment to him that your answer is correct.

I awarded 400 points to u for your answer and 100 points to monosodiumg for his positive criticism truly proper guideline.

With very best regards
Mateen


 

With best regards
Mateen:
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10823078
Hi Mateen,

As I posted above I should've ignored the comment. I should have read your comments more clearly. Anyway  in the heat of the moment wrote something ,that I shouldn't have. Just forget the whole thing..

About the points.. it really doesn't matter.Often it's the advice you get from other (like monosodiumg) worth more in the long run than the solutions.


Have a nice day!
Shailesh
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql update 2 38
SQL Get Store Procedure Column Name As Row 5 41
SQL Server Express or Standard? 5 30
SQL Server remove line breaks and tabbed 2 8
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question