• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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