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

db2 query string to be used in .NET odbc C# app

I ws given a query run on a tool for querying db2 on an as400.  I need to convert the query to something usable by .NET/C# ODBC connection.  This is the string:
SELECT mmitno.mitmas, mmitds.mitmas, SUM(obrnqt) as holds FROM mitmas,ooline,oohead INNER JOIN  mmcono.mitmas=obcono.ooline and mmitno.mitmas=obitno.ooline and obcono.ooline=oacono.oohead and obdivi.ooline=oadivi.oohead and oborno.ooline=oaorno.oohead and obfaci.ooline=oafaci.oohead and obwhlo.ooline=oawhlo.oohead WHERE   (mmstat.mitmas=20 AND mmitty.mitmas='FG') AND (oaoblc.oohead>0 AND oaoblc.oohead<9) AND (oborst.ooline=22 AND obrnqa.ooline>0) GROUP BY mmitno,mmitds

it currently chokes at the first "=" character as invalid.
0
mbowles
Asked:
mbowles
  • 4
  • 3
2 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

That's simply because you're missing the ON clause in your join.

e.g.
from file1 f1
join   file2 f2
  on  f2.someColumn = f1.someColumn
join   file2 f2
  on  f1.someOtherColumn = f2.someOtherColumn

HTH,
DaveSlash
0
 
ocgstylesCommented:
Hi,

I looks like you are missing the ON keyword in your INNER JOIN clause.  That would be right before the first "=" sign.

Hope that helps

Keth
0
 
mbowlesAuthor Commented:
what if the tables are not listed inthe from and multiple new tables are listed after the inner join?

from file1 f1
join   file2 f2
  on  f2.someColumn = f1.someColumn
join   file2 f2
  on  F3.someOtherColumn = f2.someOtherColumn  

where would F3 be mentioned?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
ocgstylesCommented:
Someone hear an echo?  ;-)  

Doh!
0
 
ocgstylesCommented:
You can only join two tables at a time, so its easier to reflect that in your statement:

from (file1 f1
join   file2 f2
  on  f2.someColumn = f1.someColumn)
join   file3 f3
  on  F3.someOtherColumn = f2.someOtherColumn  

Keith
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
> where would F3 be mentioned?

Pretty simple ...

from file1 f1
join   file2 f2
  on  f2.someColumn = f1.someColumn
join   file2 f2
  on  f1.someOtherColumn = f2.someOtherColumn
join   file3 f3
  on  f3.yetAnotherColumn = f2.yetAnotherColumn

-- DaveSlash

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

In looking at your query more, you'll probably want something more like this:

SELECT mmitno.mitmas,
             mmitds.mitmas,
             SUM(obrnqt) as holds
FROM   mitmas m
INNER JOIN  ooline ol
  on  ol.obcono = m.mmcono
 and  m.mmitno = ol.obitno
INNER JOIN oohead oh
  on  ol.obcono = oh.oacono
 and  ol.obdivi = oh.oadivi
 and  ol.oborno = oh.oaorno
 and  ol.obfaci = oh.oafaci
 and  ol.obwhlo = oh.oawhlo
WHERE (mmstat.mitmas=20 AND mmitty.mitmas='FG')
  AND (oaoblc.oohead>0 AND oaoblc.oohead<9)
  AND (oborst.ooline=22 AND obrnqa.ooline>0)
GROUP BY mmitno,
                   mmitds

HTH,
DaveSlash
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Heck, I'd even remove the "INNER" part of the join (since that's implied):

SELECT mmitno.mitmas,
             mmitds.mitmas,
             SUM(obrnqt) as holds
FROM   mitmas m
JOIN     ooline ol
  on  ol.obcono = m.mmcono
 and  m.mmitno = ol.obitno
JOIN     oohead oh
  on  ol.obcono = oh.oacono
 and  ol.obdivi = oh.oadivi
 and  ol.oborno = oh.oaorno
 and  ol.obfaci = oh.oafaci
 and  ol.obwhlo = oh.oawhlo
WHERE (mmstat.mitmas=20 AND mmitty.mitmas='FG')
  AND (oaoblc.oohead>0 AND oaoblc.oohead<9)
  AND (oborst.ooline=22 AND obrnqa.ooline>0)
GROUP BY mmitno,
                   mmitds
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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