Solved

Need help in a sql selct (sql 2000)

Posted on 2004-04-12
13
257 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
  • 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now