Claudeless
|
| Posted: 05/30/2005, 5:42 PM |
|
This is a Really Nested SUBQUERY..
It Works on any SQL Clients
BUT I GET THIS ERROR When I Publish.
SELECT COUNT(*) FROM assets
I DIDNT ADD THIS..
HELP
.
Database error: Invalid SQL: SELECT COUNT(*) FROM assets INNER JOIN classofsoftware ON (assets.`Orginal MODEL_NO` = classofsoftware.ModelNo) Where ((assets.MasterCusID = 20040556 ) AND (assets.VOIDED = '0') AND (assets.QuoteAsset = '0')) GROUP BY Catagory ) AS LINECOLLECT INNER JOIN pricebook on ((pricebook.level = PLEVEL ) And ( pricebook.Category = CAT )) Where ((assets.MasterCusID = 20040556) AND (assets.VOIDED = 0)AND (assets.QuoteAsset = 0)) GROUP BY CAT
MySQL Error: 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS LINECOLLECT INNER JOIN pricebook on ((pricebook.level = PLEVEL ) And ( ' at line 1)
Session halted.
Select distinct
assets.MasterCusID AS MIDID,
CTYPE,
CAT,
pricebook.Description AS DESCRIB,
QTY,
PLEVEL,
pricebook.Retail AS RTP,
@TRP:= (SELECT QTY * RTP ) AS TRP,
@TRPM:= (SELECT @TRP * .17) AS TRPM
From assets,
( SelecT distinct `Orginal AssetType` AS OTYPE
From assets
Where ((assets.MasterCusID = {MasterCuSIDSTAT}) AND
(assets.VOIDED = 0) AND
(assets.QuoteAsset =0))
) AS NOP,
( Select
`Orginal AssetType` AS CTYPE,
`Orginal MODEL_NO` AS MODEL,
@cat:= Catagory AS CAT,
SUM(assets.MultiNo) as QTY,
@PLEVEL:= CASE
WHEN SUM(assets.MultiNo) = 1 then @pl:= 'A'
WHEN SUM(assets.MultiNo) BETWEEN 2 AND 5 then @pl:= 'B'
WHEN SUM(assets.MultiNo) BETWEEN 6 AND 10 then @pl:= 'C'
WHEN SUM(assets.MultiNo) BETWEEN 11 AND 25 then @pl:='D'
WHEN SUM(assets.MultiNo) BETWEEN 26 AND 50 Then @pl:='E'
WHEN SUM(assets.MultiNo) BETWEEN 51 AND 100 Then @pl:='F'
WHEN SUM(assets.MultiNo) BETWEEN 101 AND 200 then @pl:='G'
WHEN SUM(assets.MultiNo) BETWEEN 201 AND 400 then @pl:='H'
WHEN SUM(assets.MultiNo) BETWEEN 401 AND 800 then @pl:='I'
WHEN SUM(assets.MultiNo) BETWEEN 801 AND 1500 then @pl:= 'J'
else @pl:='K'
END AS PLEVEL
FROM assets
INNER JOIN classofsoftware ON (assets.`Orginal MODEL_NO` = classofsoftware.ModelNo)
Where
((assets.MasterCusID = {MasterCuSIDSTAT} ) AND
(assets.VOIDED = '0') AND
(assets.QuoteAsset = '0'))
GROUP BY Catagory ) AS LINECOLLECT
INNER JOIN pricebook on ((pricebook.level = PLEVEL ) And ( pricebook.Category = CAT ))
Where
((assets.MasterCusID = {MasterCuSIDSTAT}) AND
(assets.VOIDED = 0)AND
(assets.QuoteAsset = 0))
GROUP BY CAT
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 05/30/2005, 5:52 PM |
|
The Count clause is added automatically to the SQL if you use the navigator in your grid. Otherwise the application (or rather the navigator) wouldn't know how many pages of data users can navigate through. If your query doesn't work properly with the COUNT statement added in front of it then you may need to assign the proper SQL statement to the CountSQL variable. Please see http://forums.codecharge.com/search.php?s_keyword=count...[]=21&s_period=
or you can search the Help file for CountSQL.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
claudeless
|
| Posted: 05/30/2005, 7:49 PM |
|
OK I Figured it out... where to Fix it...
But Once you Modify the Code you can not change the your Query..
Unless you do it manually..
Are you Going to add a Place your can Modified it so it doesnt
Cause this Problem.
Thanks
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/30/2005, 10:33 PM |
|
You should never modify the generated code, just use the events to modify CountSQL. AFAIK CountSQL was created specfically for such purpose.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
