Link to home
Start Free TrialLog in
Avatar of Mateen
Mateen

asked on

Need help in a sql selct (sql 2000)

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:

ASKER CERTIFIED SOLUTION
Avatar of Shailesh15
Shailesh15
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of monosodiumg
monosodiumg
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mateen
Mateen

ASKER

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:
Avatar of Mateen
Mateen

ASKER

Hi mono

I should make a habit of writing my name at the end of question.
Avatar of monosodiumg
monosodiumg
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

Avatar of Mateen
Mateen

ASKER

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.
Avatar of Mateen
Mateen

ASKER

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:
Avatar of Shailesh15
Shailesh15
" 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.
Avatar of monosodiumg
monosodiumg
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.

Avatar of Shailesh15
Shailesh15
Yep... Now when I read again... you are right.. Though didn't like wordings when I read, first thing in the morning.
Avatar of monosodiumg
monosodiumg
I read it same way as you did at first. Guess we can now make derogatory comments about Mateen's english ;) (just kidding Matteen!!!)
Avatar of Mateen
Mateen

ASKER

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:
Avatar of Shailesh15
Shailesh15
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