• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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:

0
Mateen
Asked:
Mateen
  • 5
  • 4
  • 4
2 Solutions
 
Shailesh15Commented:
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
 
monosodiumgCommented:
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
 
MateenAuthor Commented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
MateenAuthor Commented:
Hi mono

I should make a habit of writing my name at the end of question.
0
 
monosodiumgCommented:
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
 
MateenAuthor Commented:
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
 
MateenAuthor Commented:
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
 
Shailesh15Commented:
" 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
 
monosodiumgCommented:
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
 
Shailesh15Commented:
Yep... Now when I read again... you are right.. Though didn't like wordings when I read, first thing in the morning.
0
 
monosodiumgCommented:
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
 
MateenAuthor Commented:
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
 
Shailesh15Commented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now