?
Solved

Syntax Error?

Posted on 2006-04-21
12
Medium Priority
?
200 Views
Last Modified: 2010-05-01
I am getting a syntax error from this sql query.  I have been looking at it for over an hour and I just don't see the error.  Can someone take a look and let me know where the error is?


SELECT
    RM00101."CUSTNMBR", RM00101."CUSTNAME", RM00101."ADDRESS1", RM00101."ADDRESS2", RM00101."CITY", RM00101."STATE", RM00101."ZIP", RM00101."PHONE1", RM00101."FAX", RM00101."SLPRSNID",
    RM14009."PN_County",
    RM00301."SPRSNSLN",
    SOP30200."SOPTYPE", SOP30200."SOPNUMBE", SOP30200."DOCDATE",
    SOP30300."ITEMNMBR", SOP30300."ITEMDESC", SOP30300."UOFM", SOP30300."QUANTITY"
FROM
    { oj (((("PION"."dbo"."RM00101" RM00101   INNER JOIN "PION"."dbo"."RM14009" RM14009 ON RM00101."ADRSCODE" = RM14009."ADRSCODE" AND RM00101."CUSTNMBR" = RM14009."CUSTNMBR")  INNER JOIN "PION"."dbo"."SOP30200" SOP30200 ON RM14009."CUSTNMBR" = SOP30200."CUSTNMBR")  INNER JOIN "PION"."dbo"."SOP30300" SOP30300 ON SOP30200."SOPTYPE" = SOP30300."SOPTYPE" AND SOP30200."SOPNUMBE" = SOP30300."SOPNUMBE" )  INNER JOIN "PION"."dbo"."RM00301" RM00301 ON RM00101."SLPRSNID" = RM00301."SLPRSNID" )   }              
WHERE
    SOP30200."DOCDATE" >= {ts '2005-03-01 08:19:37.00'} AND
    SOP30200."DOCDATE" < {ts '2005-03-31 08:19:38.00'} AND
    SOP30200."SOPTYPE" = 2 AND
    RM00101."SLPRSNID" = '04'              
ORDER BY
    RM00101."SLPRSNID" ASC
0
Comment
Question by:Pioneermfg
  • 6
  • 4
  • 2
12 Comments
 
LVL 4

Expert Comment

by:g_johnson
ID: 16509399
what back end?  do you get any more information than "syntax error"?
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16509552
Yes, backend is sql 2000, line 1 '}'
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16509954
why are you putting the curly {} around the FROM ?
0
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!

 
LVL 4

Expert Comment

by:g_johnson
ID: 16509992
i agree, get read of the brackets {}
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16510083
I didn't put them there, Crystal Reports did.
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16510121
I removed the brackets as suggested and now I get the same syntax error only now is shows the "(".
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16510128
Doesn't matter who did it.. but I believe they shouldnt be there .. but is it working now?
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16510145
I also receive a pop up window that states "error detected by database dll"
0
 
LVL 14

Accepted Solution

by:
PockyMaster earned 2000 total points
ID: 16510202
well.. lets break down your query:

SELECT
    RM00101.CUSTNMBR, RM00101.CUSTNAME, RM00101.ADDRESS1, RM00101.ADDRESS2, RM00101.CITY, RM00101.STATE, RM00101.ZIP, RM00101.PHONE1, RM00101.FAX, RM00101.SLPRSNID,
    RM14009.PN_County,
    RM00301.SPRSNSLN,
    SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.DOCDATE,
    SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SOP30300.UOFM, SOP30300.QUANTITY
FROM
    RM00101  
INNER JOIN RM14009 ON (RM00101.ADRSCODE = RM14009.ADRSCODE
            AND RM00101.CUSTNMBR = RM14009.CUSTNMBR)  
INNER JOIN SOP30200 ON (RM14009.CUSTNMBR = SOP30200.CUSTNMBR)  
INNER JOIN SOP30300 ON (SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE )  
INNER JOIN RM00301 ON (RM00101.SLPRSNID = RM00301.SLPRSNID )
WHERE
    SOP30200.DOCDATE >= {ts '2005-03-01 08:19:37.00'} AND
    SOP30200.DOCDATE < {ts '2005-03-31 08:19:38.00'} AND
    SOP30200.SOPTYPE = 2 AND
    RM00101.SLPRSNID = '04'              
ORDER BY
    RM00101.SLPRSNID ASC

Does it work like this?
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16510325
No, but I receive no records nor do I receive any errors.  Let me clarify.  Before I added the rm00301.SPRSNSLN field, this query worked.
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16511074
PockyMaster your answer wasn't quite correct, but it did get me on the right path.  I have fixed the issue and the reports works!  Thanks!!!
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16513690
Good job :D
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

840 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