[Webinar] Streamline your web hosting managementRegister Today

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

Help with Converting an SQL query statement

I have a program in VB 6 that uses a query that I need to pull out and move into Microsoft Access or just in SQL. I keep getting some syntax errors. Can someone help translate this for me?

SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR " & _"FROM PRODDTA.F4101 INNER JOIN ((PRODDTA.F4102 INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU) AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM)) INNER JOIN PRODDTA.F0101 ON PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8) ON PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM " & _
    "GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND, PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT "
 
0
Nmark80
Asked:
Nmark80
  • 5
  • 2
  • 2
  • +1
2 Solutions
 
dbaSQLCommented:
Is this the entire query?  I see a number of oddities, but first, what is the error you are receiving?
0
 
Nmark80Author Commented:
Well when I type it into the SQL editor in Access it states invalid syntax. The tables and structure here is frustrating which is what makes it harder for me when trying to convert this. THe error states "the select statement contains a reserved word ..."
0
 
dbaSQLCommented:
Well, not sure about that, but try this.  Your double quotes and &'s are gone -- syntactically, this is acceptable.  


SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR
FROM PRODDTA.F4101
INNER JOIN ((PRODDTA.F4102 INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU) AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM))
INNER JOIN PRODDTA.F0101 ON PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8) ON PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM
GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
         PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ee_rleeCommented:
try this
SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR FROM PRODDTA.F4101 INNER JOIN ((PRODDTA.F4102 INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU) AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM)) INNER JOIN PRODDTA.F0101 ON PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8) ON PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND, PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT

Open in new window

0
 
Nmark80Author Commented:
It is now stating that there is a Syntax error in the join operation. Any thoughts?
0
 
ee_rleeCommented:
how about this one?
SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR 
FROM PRODDTA.F4101 
INNER JOIN PRODDTA.F4102 ON (PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM)
INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU AND PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM) 
INNER JOIN PRODDTA.F0101 ON (PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8)  
GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND, PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT

Open in new window

0
 
dbaSQLCommented:
again, hard to say, as i don't have the access editor, but try this:

SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR FROM PRODDTA.F4101 INNER JOIN PRODDTA.F4102
                                                       ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU)
                                                   AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM)
                                                      INNER JOIN PRODDTA.F0101
                                                              ON PRODDTA.F0101.ABAN8=PRODDTA.F4102.IBVEND
                                                             AND PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM
GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
         PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT
0
 
Anthony PerkinsCommented:
You do realize the SQL syntax is different in MS Access as opposed to MS SQL Server, right?
0
 
dbaSQLCommented:
yep.  to my knowledge, the OUTER JOIN is not supported by Access.  aside from that, i believe the access join has the ON condition just as sql does.  and the right/left is handled the same, is it not?

>>>>>>>>>  Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

http://msdn2.microsoft.com/en-us/library/bb208854.aspx
http://msdn2.microsoft.com/en-us/library/bb208894.aspx

0
 
Anthony PerkinsCommented:
My comment was really directed at the questioner.

>>i believe the access join has the ON condition just as sql does.  and the right/left is handled the same, is it not?<<  
Yes but I don'e believe that MS Access supports a 2 part naming convention (owner name and table name) for the FROM clause.

0
 
dbaSQLCommented:
>>My comment was really directed at the questioner.
silly me.  sorry bout that
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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