|
| |
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
 |
MS SQL 2005 Banging my Head... - 12/19/2007 16:27:14
I am trying to run the following script, but it keeps producing errors...Maybe someone knows a better way around it, or maybe someone can help correct the endless errors. I have tried everything but the winning syntax which is puzzling me...here is what I am trying to do:
SELECT SOP30200.CUSTNMBR, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SUM(SOP30300.QUANTITY) AS 'Total_QTY'
FROM SOP30200 Join SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
WHERE Year(docdate) = 2007 AND 30300.SOPTYPE = 3
GROUP BY CUSTNMBR, ITEMNMBR
Here is the error I get?
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
_____________________________
Brian---
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS SQL 2005 Banging my Head... - 12/19/2007 19:03:35
quote:
...SUM(SOP30300.QUANTITY) AS 'Total_QTY'...
Try it without the apostrophe:
...SUM(SOP30300.QUANTITY) AS Total_QTY ...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
|
RE: MS SQL 2005 Banging my Head... - 12/19/2007 21:29:48
same error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
_____________________________
Brian---
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS SQL 2005 Banging my Head... - 12/19/2007 21:41:20
quote:
SELECT SOP30200.CUSTNMBR, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SUM(SOP30300.QUANTITY) AS 'Total_QTY'
FROM SOP30200 Join SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
WHERE Year(docdate) = 2007 AND 30300.SOPTYPE = 3
GROUP BY CUSTNMBR, ITEMNMBR
I'm quite confident there are nio apostrophes when using AS.
Regardless, have you:
1. Checked and double checked for typos?
2. Have legitimate values in your fields? For instance ;docdate' might be one place that an error would show with a null. Have you tried ...WHERE Year(IsNull(docdate,'1/1/1900')) = 2007?
3. Confirmed your field types as in SOPTYPE a number?
Just 3 things that commonly catch me. Lastly, try reducing the criteria to see which is the offending item:
SELECT SOP30200.CUSTNMBR, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SUM(SOP30300.QUANTITY) AS Total_QTY
FROM SOP30200 Join SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
...and...
SELECT SOP30200.CUSTNMBR, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SUM(SOP30300.QUANTITY) AS Total_QTY
FROM SOP30200 Join SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
WHERE Year(docdate) = 2007
..and..
SELECT SOP30200.CUSTNMBR, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SUM(SOP30300.QUANTITY) AS Total_QTY
FROM SOP30200 Join SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
WHERE 30300.SOPTYPE = 3
etc.
Hope that helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
William Lee
Posts: 1179
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: MS SQL 2005 Banging my Head... - 12/20/2007 0:19:40
quote:
ORIGINAL: Bnugent
SELECT SOP30200.CUSTNMBR, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SUM(SOP30300.QUANTITY) AS 'Total_QTY'
FROM SOP30200 Join SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
WHERE Year(docdate) = 2007 AND 30300.SOPTYPE = 3
GROUP BY CUSTNMBR, ITEMNMBR
I'm guessing it should be SOP30300.SOPTYPE.
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
|
MS SQL 2005 Banging my Head...PART 2 - 12/21/2007 8:47:31
As usual, you guys were right on it. Finally got this to work...
fp_sQry="SELECT SUM(SOP30300.QUANTITY) AS 'Total_QTY' FROM SOP30200 Join SOP30300 ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE And SOP30200.SOPTYPE = SOP30300.SOPTYPE WHERE Year(docdate) = 2007 AND SOP30300.SOPTYPE = 3 AND CUSTNMBR = '::CUSTNMBR::' AND SOP30300.ITEMNMBR = 'FNCHO004060' "
But still having problems...
The field SOP30300.QUANTITY is numeric. The sum function is working correctly and I am getting totals correctly when there are results, the issue is that when there are not results (i.e., maybe this customer has never purchased this product, I am not getting my default response displayed. Currently, I have asked it to display 0 if there are no results:
fp_sNoRecords="0"
I even tried text "ZERO" thinking it had to do with a numeric but that doesnt even post. In fact, nothing does. So if the customer purchased those products, it shows the sum of qty which is great. Issue is that when the customer hasnt purchased those products, it just shows a blank...How can I get it to display something else?
_____________________________
Brian---
| |
|
|
|
William Lee
Posts: 1179
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: MS SQL 2005 Banging my Head...PART 2 - 12/21/2007 10:05:33
If you have more than 1 DRW with the same BOTID on the page, you may likely to experience this issue as was encountered by one user in this forum.
Are you able to post your code on that ASP page?
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS SQL 2005 Banging my Head...PART 2 - 12/21/2007 12:25:30
It may be in the JOIN. If the joining field has no matches, a straight JOIN returns no records. How about changing your join to (I suspect anyways) RIGHT OUTER JOIN.
Just another quick guess.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
|
RE: MS SQL 2005 Banging my Head...PART 2 - 12/21/2007 14:20:06
did RIGHT OUT JOIN and nothing changed. No errors, etc...
Yes, I have multiple DRW on the page, although I have spooky dieted them all.
is there another solution such as an if then statement for the result itself, meaning:
<%If FP_FieldVal(fp_rs,"Total_QTY") = "" Then
Response.write "<b>0</b>"
Else
Response.write =FP_FieldVal(fp_rs,"Total_QTY")
End if %>
That didnt do anything different.
also tried this:
<%If FP_FieldVal(fp_rs,"Total_QTY") = "=" Then
Response.write "<b>0</b>"
Else
Response.write =FP_FieldVal(fp_rs,"Total_QTY")
End if %>
Nothing. Any thoughts?
_____________________________
Brian---
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS SQL 2005 Banging my Head...PART 2 - 12/21/2007 16:34:48
At this point I'm guessing without seeing the table structure.
Is the problem due to no records in 1 table related to the other? That would probably be solved by reworking your JOIN statement.
Is the problem due to possible Nulls in the field? That would probably be solved like IsNull(SOP30300.QUANTITY,0).
Have you tried duplicating your results in SQL Express Management? If you get the appropriate results there, the issue is on the page and not in the query.
Hope that helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
William Lee
Posts: 1179
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: MS SQL 2005 Banging my Head...PART 2 - 12/24/2007 8:50:46
If the BOTID of your multiple DRWs are same, try a simple value change of all the BOTID so that they have different ID value and see if your problem goes away.
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
neville
Posts: 1
Joined: 1/24/2008
Status: offline
|
RE: MS SQL 2005 Banging my Head...PART 2 - 1/24/2008 23:05:56
i think this is what you are trying to do:
SELECT
m.CUSTNMBR, d.ITEMNMBR,
d.ITEMDESC, SUM(d.QUANTITY) AS 'Total_QTY'
FROM SOP30200 m
inner Join SOP30300 d on m.SOPNUMBE = d.SOPNUMBE
WHERE Year(m.docdate) = 2007 AND m.SOPTYPE = 3
GROUP BY
m.CUSTNMBR, d.ITEMNMBR, d.ITEMDESC
source: http://gp-dynamics.com/dynamics-gp-command-details.asp?id=64
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
