aebstract
-
Posts
1,105 -
Joined
-
Last visited
Posts posted by aebstract
-
-
if($crank != 0) { header("Location: /index.php"); }Something similar to that, put it at the top. If crank isn't 0, then it'll shoot them to whatever page you choose. Could be back to the page they previously were at or whatever.
-
It does, they are in decimal form. Mostly xxx.xx, some xx.x / x.x and some at 0.0. Some (very few) are going to be negative, but that won't be under "fabricated" it will be in another query I run.
-
Okay, that's something I was unaware that I couldn't do, right now I have this:
SELECT customfieldview.info, customfieldview.recordid, product.partid, soitem.totalprice AS totalprice, soitem.productid, product.id FROM customfieldview JOIN product ON customfieldview.recordid = product.partid JOIN soitem ON soitem.productid = product.id WHERE customfieldview.info = 'Fabricated' ORDER BY product.id
Which is displaying every product.id the correct amount of times, but the value (totalprice) is "null" for every row. Something to do with how it is joined?
-
Fixed this paragraph:
What I'm trying to do is take every product that has a "customfieldview.info" = "Fabricated", get the product.partid that matches the customfieldview.recordid(may result in 1-3 or so rows), use the id column from the product table to get a sum of soitem.totalprice where soitem.productid = product.id, for every product.id that was returned from "Fabricated". I'm needing the value of the total sum for all rows that match these requirements.
Any more info needed let me know, thanks.
-
SELECT customfieldview.info, customfieldview.recordid, product.partid, sum(soitem.totalprice) AS totalprice, soitem.productid, product.id FROM customfieldview JOIN product ON customfieldview.recordid = product.partid JOIN soitem ON soitem.productid = soitem.productid WHERE customfieldview.info = 'Fabricated'
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)and if I remove the quotes from fabricated I get:
Column unkownFABRICATEDAt line 7, column 30What I'm trying to do is take every product that has a "customlistitem.name" = "Fabricated", get the product.partid (may result in 1-3 or so rows), use the id column from the product table to get a sum of soitem.totalprice for every product.id that was returned from "Fabricated". I'm needing the value of the total sum for all rows that match these requirements.
Any more info needed let me know, thanks.
-
I don't have access to see how it was created, or if I do I don't know how to get to it. So that leaves me to either finding out from the manufacturer if it is auto increment or make it so it grabs the next available number. I can only run through like 1 query at a time I think, not sure if what you explained will work if it's two completely separate queries?
edit: gonna try and accomplish what I'm after in a different way. Realized I can skip a huge step.
-
I just got this error
validation error for column ID, value "*** null ***"from this query
INSERT INTO CUSTOMSET (CUSTOMFIELDID, INFO, RECORDID) SELECT 5, C.INFO, C.RECORDID FROM CUSTOMSET AS C WHERE C.CUSTOMFIELDID = 3
So maybe it isn't auto increment? In the case that it isn't, can I easily just use the next available number for the column ID, for each row created? Would that cause that error?
-
Okay, I think this will work but I have to do it to a different table/columns now. The first column is called ID and its auto increment. Do I put SELECT , c.customfield, c.info, etc? Or what?
EDIT: I can just remove the id field completely, and it'll create a new one for every row that is created, right?
-
INSERT INTO CUSTOMFIELDVIEW (CUSTOMFIELDVIEW.CFID, CUSTOMFIELDVIEW.CFNAME, CUSTOMFIELDVIEW.CFDESCRIPTION, CUSTOMFIELDVIEW.CFSORTORDER, CUSTOMFIELDVIEW.CFTABLEID, CUSTOMFIELDVIEW.CFTYPEID, CUSTOMFIELDVIEW.CFREQUIRED, CUSTOMFIELDVIEW.RECORDID, CUSTOMFIELDVIEW.INFO) (SELECT 5, CUSTOMFIELDVIEW.CFNAME, CUSTOMFIELDVIEW.CFDESCRIPTION, 1, 97022306, CUSTOMFIELDVIEW.CFTYPEID, CUSTOMFIELDVIEW.CFREQUIRED, CUSTOMFIELDVIEW.RECORDID, CUSTOMFIELDVIEW.INFO FROM CUSTOMFIELDVIEW WHERE CUSTOMFIELDVIEW.CFID = 3)
I just want to make sure this is gonna do what I want it to do. There are 9 columns in the customfieldview table, I need to make a copy of every one of them that has a cfid of 3, changing 3 column's information and keeping the rest the same. The three changes that need to be made are the cfid to 5, cfsortorder to 1 and tableid to 97022306. I want it to do this for every row that has a cfid of 3.
-
Turned out that there was some sort of protection on these things. Had to run a "GRANT SELECT on totals2 to USER" to get access. Thanks for your help, hopefully I can get the ball rolling now.
-
That doesn't give me an error, it shows the results. Which is why I was thinking it would be a software issue. Everything else seems to be working correctly, thanks!
-
So create an actual table instead of creating a view? The only reason I made a view was because the company said it would be safer to do it that way.
-
This is odd, but with that view created, when I try and pull information from that table I get this error:
Error code 335544352, SQL state HY000: GDS Exception. 335544352. no permission for read/select access to TABLE TOTALS2
Line 1, column 1
Execution finished after 0 s, 1 error(s) occurred.
Wondering if this is strictly a software issue or something that can be solved in the query? Also is there a way for me to change the view created so that blank spaces show up as 0.0?
Note: The error shows when I simply run "SELECT * FROM TOTALS2"
-
Column unknown
A2.TOTALPRICE
At line 2, column 27
-
CREATE VIEW totals AS SELECT A1.ID SOID, SUM(A2.TOTALPRICE) TOTAL, SUM(A3.AMOUNT) PAYED FROM SO A1, SOITEM A2, POSTRANSACTION A3 WHERE A1.ID = A2.SOID AND A1.ID = A3.SOID GROUP BY A1.ID
This creates a view that is close to what I need but not quite right. It is suppose to take all rows from the table soitem and add the totalprice column together as well as do the same thing under postransaction table with the amount column. The TOTAL column ends up being the correct amount, the total of all the lines where soitem.soid match so.id. The PAYED total ends up being a number of (TOTAL x # of rows in soitem where soitem.soid and so.id match). So a total of 732.07 from the lines (594.81 and 137.26) result in a PAYED amount of 1464.14. Here is the thing, for that order there is one line in postransaction with the correct soid and its total is 732.07 for column amount. That is problem 1, problem 2 is in my query and what's wrong is that I need to show the PAYED as 0.0 if there are no results in the postransaction table with the corresponding soid. Right now if there isn't a result in that table, it just doesn't add it to my view. Hope someone knows what I'm trying to do and it's an easy fix!
-
I've got this:
SUM(postransaction.amount) AS PAYMENTSAMOUNT
in this query:
SELECT SO.num AS "SO", SO.dateCompleted AS "DateCompleted", SO.dateIssued as "DateIssued", COALESCE(SO.totalTax, 0) as "Tax", COALESCE(POSTransaction.Amount, 0) AS "POSAmount", POSTransaction.dateTime as "POSDate", PaymentMethod.name AS "PaymentName", Customer.name as "Customer", so.customerpo as "customerpo", COALESCE(SUM(SOItem.totalPrice), 0) AS totalPrice, MAX(SOItem.dateScheduledFulfillment) AS DateScheduledFulfillment, PaymentTerms.netDays as "NetDays", PaymentTerms.nextMonth as "NextMonth", PaymentMethod.Name as "PaymentType", company.name AS company, COALESCE(postcatch.totalpaid, 0) AS totalpaid, COALESCE(postcatchcount.countpaid,1) as catchcount, SUM(postransaction.amount) AS PAYMENTSAMOUNT FROM Customer, SOItem, PaymentTerms, SO LEFT JOIN POSTransaction ON POSTransaction.soID = SO.ID LEFT JOIN PaymentMethod ON PaymentMethod.ID = POSTransaction.paymentMethodID JOIN company ON company.id = 1 LEFT JOIN (SELECT SUM(amount) AS totalpaid, soid FROM postransaction GROUP BY soid) postcatch ON postcatch.soid = so.id LEFT JOIN (SELECT Count(amount) AS countpaid, soid FROM postransaction GROUP BY soid) postcatchCount ON postcatchCount.soid = so.id WHERE Customer.ID = SO.customerID AND SOItem.SOID = SO.ID AND PaymentTerms.ID = SO.paymentTermsID AND SOItem.typeID <> 40 AND Customer.ID LIKE $P{customerID} AND SOItem.statusID <> 70 AND SO.statusID IN($P{ckIssued},$P{ckInProgress},$P{ckFulfilled},$P{ckClosedShort}) GROUP BY SO.num, SO.dateCompleted, SO.dateIssued, SO.statusID, SO.totalTax, POSTransaction.amount, POSTransaction.dateTime, PaymentMethod.name, Customer.name, PaymentTerms.netDays, PaymentTerms.typeID, PaymentTerms.nextMonth, company.name, postcatch.totalpaid, postcatchcount.countpaid, so.customerpo ORDER BY Customer.name, SO.num, POSTransaction.dateTimeI'm attempting to take the payments and add them together to see how much has been payed towards the order. I've made a variable out of the PAYMENTAMOUNTS results. It is giving me odd results though, for example on a specific order that I am looking at. The total is 1809.69. The customer has made a payment of 465.69 which should leave us at 1344. Right now I am just trying to display the total payments made, which is 465.69. So when I call that variable, I am getting 3259.83. I am not sure what is causing this to happen. Does anyone have an idea of what might cause this?
-
HAVING has to be after GROUP, didn't notice that.
-
Okay I'm needing to do some basic math to determine whether or not to grab the results. What I have right now is:
WHERE SO.TOTALTAX+SUM(SOITEM.TOTALPRICE)-SUM(POSTRANSACTION.AMOUNT) > 0
I'm getting an error saying:
CANNOT USE AN AGGREGATE FUNCTION IN A WHERE CLAUSE, USE HAVING INSTEADI tried remove this line from the what section and added
HAVING SO.TOTALTAX+SUM(SOITEM.TOTALPRICE)-SUM(POSTRANSACTION.AMOUNT) > 0
With this I get a new error message:
DYNAMIC SQL ERRORSQL ERROR CODE = -104 TOKEN UNKOWN - LINE 31, COLUMN 1GROUP -
Deleted the new one that I added since I didn't notice the first. Seems to have worked. Thanks
-
CREATE VIEW sopaymentstatus AS SELECT SO.ID, POSTRANSACTION.ID FROM SO, POSTRANSACTION WHERE SO.ID = POSTRANSACTION.SOID
Error:
GDS Exception. 335544351. unsuccessful metadata update
STORE RDB$RELATION_FIELDS failed
attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_15"
-
Okay I tried adding the bit you put in to the code, and got an error. Here is the code to look at + the error:
SELECT SO.num AS "SO", SO.dateCompleted AS "DateCompleted", SO.dateIssued as "DateIssued", COALESCE(SO.totalTax, 0) as "Tax", COALESCE(POSTransaction.Amount, 0) AS "POSAmount", POSTransaction.dateTime as "POSDate", PaymentMethod.name AS "PaymentName", Customer.name as "Customer", so.customerpo as "customerpo", COALESCE(SUM(SOItem.totalPrice), 0) AS totalPrice, MAX(SOItem.dateScheduledFulfillment) AS DateScheduledFulfillment, PaymentTerms.netDays as "NetDays", PaymentTerms.nextMonth as "NextMonth", PaymentMethod.Name as "PaymentType", company.name AS company, COALESCE(postcatch.totalpaid, 0) AS totalpaid, COALESCE(postcatchcount.countpaid,1) as catchcount FROM Customer, SOItem, PaymentTerms, SO LEFT JOIN POSTransaction ON POSTransaction.soID = SO.ID LEFT JOIN PaymentMethod ON PaymentMethod.ID = POSTransaction.paymentMethodID JOIN company ON company.id = 1 LEFT JOIN (SELECT SUM(amount) AS totalpaid, soid FROM postransaction GROUP BY soid) postcatch ON postcatch.soid = so.id LEFT JOIN (SELECT Count(amount) AS countpaid, soid FROM postransaction GROUP BY soid) postcatchCount ON postcatchCount.soid = so.id LEFT OUTER JOIN postransaction ON SO.ID = POSTRANSACTION.SOID WHERE Customer.ID = SO.customerID AND SOItem.SOID = SO.ID AND PaymentTerms.ID = SO.paymentTermsID AND SOItem.typeID <> 40 AND Customer.ID LIKE $P{customerID} AND SOItem.statusID <> 70 AND SO.statusID IN($P{ckIssued},$P{ckInProgress},$P{ckFulfilled},$P{ckClosedShort}) AND POSTRANSACTION.SOID IS NULL GROUP BY SO.num, SO.dateCompleted, SO.dateIssued, SO.statusID, SO.totalTax, POSTransaction.amount, POSTransaction.dateTime, PaymentMethod.name, Customer.name, PaymentTerms.netDays, PaymentTerms.typeID, PaymentTerms.nextMonth, company.name, postcatch.totalpaid, postcatchcount.countpaid, so.customerpo ORDER BY Customer.name, SO.num, POSTransaction.dateTimeError:SQL problems:GDS Exception. 335544569. Dynamic SQL ErrorSQL error code = -204Ambiguous field name between table POSTRANSACTION and table POSTRANSACTION SOID
-
Title sucks, not sure what to name it. Anyway, I've got a database with lots of tables and lots of information. (our inventory management software) I'm doing something where I am returning results of orders, and I need to only return it if "SO.ID" does not show up in "POSTRANSACTION.SOID". Basically in the query I have "AND POSTRANSACTION.SOID = SO.ID" which did the exact opposite of what I needed (obviously). So it is only showing the orders that are in the postransaction table right now, and it needs to only show ones that AREN'T. Is there a simple query word or two I can use to do this?
-
Appreciate it, guys.. gonna get all the information stored now.
-
It's just text, which I am going to use to generate a menu tree. Category/name is basically all that will be used and maybe the unique ID.
Advice about database Music Structure
in MySQL Help
Posted
Here's the problem, and it's not necessarily a problem with your setup: We don't know what you're trying to achieve with this database. You can structure a database several ways and each way be just as good as the other, it really just depends on how you're gonna be using it and what you're gonna be doing with it.