[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Simpler Sql

I could write this in a cursor or .net and loop thru this but I am thinking there has to be a simpler way to do this.

Is there?  
Here is my info:

--Do as .net reader with this
select serlnmbr,locncode
from iv00200

--for Each element in reader
select iv30400.serltnum, iv30300.trnstloc, iv30300.docdate
from iv30400
      join iv30300 on (iv30400.ivdoctyp = iv30300.doctype and iv30400.docnumbr = iv30300.docnumbr)
where iv30400.serltnum = rs.serlnmbr   --  FROM RECORDSET ABOVE
and iv30300.trnstloc = rs.locncode          --  FROM RECORDSET ABOVE
and iv30300.docdate <  '5/6/11'            -- More than 7 days, I can do this, a dateadd -7, etc...

can i just write one query and do this?  thanks.
0
purdyra1
Asked:
purdyra1
1 Solution
 
mrjoltcolaCommented:
select iv30400.serltnum, iv30300.trnstloc, iv30300.docdate
from iv30400
      join iv30300 on (iv30400.ivdoctyp = iv30300.doctype and iv30400.docnumbr = iv30300.docnumbr)
      join iv00200 on ( iv30400.serltnum = iv00200.serlnmbr and  iv30300.trnstloc = iv00200.locncode)
where iv30300.docdate <  '5/6/11'      
0
 
HainKurtSr. System AnalystCommented:
run two queries

select serlnmbr,locncode from iv00200

select iv30400.serltnum, iv30300.trnstloc, iv30300.docdate
from iv30400
      join iv30300 on (iv30400.ivdoctyp = iv30300.doctype and iv30400.docnumbr = iv30300.docnumbr)
      join (select serlnmbr,locncode from iv00200) rs
where iv30400.serltnum = rs.serlnmbr   --  FROM RECORDSET ABOVE
and iv30300.trnstloc = rs.locncode          --  FROM RECORDSET ABOVE
and iv30300.docdate <  '5/6/11'            -- More than 7 days, I can do this, a dateadd -7, etc...

now, you have two resultsets... loop through 1 and filter the other one in memory using RowFilter or using select methods on datatable/dataview

or create relation from resultset1 to resultset2 so filtering will be done for you automatically...

or another method: join two queries into one table and work on that, but it may be difficult for you... you may need to select distinct values, and do the same filtering bla bla bla...
0
 
purdyra1Author Commented:
@MrJoltCola

Thx for the query.  There was one small part missing but I added it back in.  I'll post it in case anyone looks at this.

MIssing:                 and locncode like '%-%t'

select iv30400.serltnum, iv30300.trnstloc, iv30300.docdate
from iv30400
      join iv30300 on (iv30400.ivdoctyp = iv30300.doctype and iv30400.docnumbr = iv30300.docnumbr)
      join iv00200 on ( iv30400.serltnum = iv00200.serlnmbr and  iv30300.trnstloc = iv00200.locncode and locncode like '%-%t')
where iv30300.docdate <  '5/6/11'
order by 3  
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
mrjoltcolaCommented:
Welcome. In general a join is just exactly what you first implemented in code, a "foreach"

foreach(query1)
   query2

When you write a join (or even a correlated subquery using IN or EXISTS) the database may choose to execute it as a nested loop (foreach) or some sort of merge (hash merge, sort merge), but in any case, letting the database do it is at least an order of magnitude faster than doing it in code, because of all the latency with each parsed SQL statement.
0
 
8080_DiverCommented:
As a passing observation, using the numeric sequence number for the column in your ORDER BY clause is a really bad habit.  If someone doesn't happen to notice that you've udone that and adds in a line, you could really get some screwy results. ;-)

For instance, your code is:
SELECT iv30400.serltnum
  ,iv30300.trnstloc
  ,iv30300.docdate
FROM iv30400
JOIN iv30300
ON(
        iv30400.ivdoctyp = iv30300.doctype
        AND iv30400.docnumbr = iv30300.docnumbr
    )
JOIN iv00200
ON(
        iv30400.serltnum = iv00200.serlnmbr
        AND iv30300.trnstloc = iv00200.locncode
        AND locncode LIKE '%-%t'
    )
WHERE iv30300.docdate < '5/6/11'
ORDER BY 3

Open in new window


But someone says, "Hey, I want the DocDate as the first column."  So, Joe JuniorProgrammer comes along and changes the query so that it looks like:
SELECT iv30300.docdate
  ,iv30400.serltnum
  ,iv30300.trnstloc
FROM iv30400
JOIN iv30300
ON(
        iv30400.ivdoctyp = iv30300.doctype
        AND iv30400.docnumbr = iv30300.docnumbr
    )
JOIN iv00200
ON(
        iv30400.serltnum = iv00200.serlnmbr
        AND iv30300.trnstloc = iv00200.locncode
        AND locncode LIKE '%-%t'
    )
WHERE iv30300.docdate < '5/6/11'
ORDER BY 3

Open in new window


Now what happens? ;-)

Or Joe inserts another column:
SELECT iv30400.serltnum
  ,iv30300.trnstloc
  ,iv30300.doctype
  ,iv30300.docdate
FROM iv30400
JOIN iv30300
ON(
        iv30400.ivdoctyp = iv30300.doctype
        AND iv30400.docnumbr = iv30300.docnumbr
    )
JOIN iv00200
ON(
        iv30400.serltnum = iv00200.serlnmbr
        AND iv30300.trnstloc = iv00200.locncode
        AND locncode LIKE '%-%t'
    )
WHERE iv30300.docdate < '5/6/11'
ORDER BY 3

Open in new window


0
 
purdyra1Author Commented:
This solution was correct but make sure that you see my last comment if you are looking at this code .
0

Featured Post

Technology Partners: 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!

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