[Webinar] Streamline your web hosting managementRegister Today

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

Error in an SQL query

I am trying the query below and it generated the following error, can someone please assist in resolving this?? Thanks

Error starting at line 1 in command:
Select product.prodcode, product.producttitle, productprice.retailprice, productprice.vat
from product, productprice
where product.prodcode = productprice.prodcode and productprice.prodpriceid = max(prodpriceid)
Error at Command Line:3 Column:78
Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 -  "group function is not allowed here"
Select product.prodcode, product.producttitle, productprice.retailprice, productprice.vat 
from product, productprice 
where product.prodcode = productprice.prodcode and productprice.prodpriceid = max(prodpriceid)

Open in new window

Relational-Model-v2.pdf
0
czechmate1976
Asked:
czechmate1976
1 Solution
 
ludofulopCommented:
i dont' have oracle here, but try following:

Select product.prodcode, product.producttitle, productprice.retailprice, productprice.vat, max(productprice.prodpriceid) as maxprodpriceid
from product, productprice
where product.prodcode = productprice.prodcode and productprice.prodpriceid = maxprodpriceid
0
 
czechmate1976Author Commented:
THANKS. I tried it but it gave me the following error

Error starting at line 1 in command:
Select product.prodcode, product.producttitle, productprice.retailprice, productprice.vat,
max(productprice.prodpriceid) as maxprodpriceid
from product, productprice
where product.prodcode = productprice.prodcode and productprice.prodpriceid = maxprodpriceid GROUP BY product.prodcode, product.producttitle, productprice.retailprice, productprice.vat
Error at Command Line:4 Column:78
Error report:
SQL Error: ORA-00904: "MAXPRODPRICEID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
0
 
flow01Commented:
table or view productprice is not in your relational model,
neither the column maxprodpriceid
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
leakim971PluritechnicianCommented:
Hello

Try :


SELECT product.prodcode, product.producttitle, productprice.retailprice, productprice.vat 
FROM product, productprice, (SELECT prodcode, MAX(prodpriceid) prodpriceid FROM productprice GROUP BY prodcode) productidmax
WHERE product.prodcode = productprice.prodcode and productprice.prodpriceid = productidmax.prodpriceid

Open in new window

0
 
czechmate1976Author Commented:
Sorry.. I have renamed the relation 'PRICERECORD' to 'PRODUCTPRICE'

Leakim971, thanks, this has not generated an error but gave me 783 records, which should be about 32.. but at least it seems not to generate syntax errors.. Can you please suggest what could be giving multiple results for each row..

To explain a bit what I am trying to do: I have a table with prices for products and I am trying to list all products with their actual prices. Actual price has the highest prodpriceid (thus trying max(...)) or the enddate = null (but if I try:

WHERE product.prodcode = productprice.prodcode AND productprice.enddate = null

it will give me empty result.

I am very grateful for all your suggestions, guys
0
 
leakim971PluritechnicianCommented:
Could you post some duplicates line (three differents prodcode) from the following :




SELECT product.*, productprice.*, productidmax.*
FROM product, productprice, (SELECT prodcode, MAX(prodpriceid) prodpriceid FROM productprice GROUP BY prodcode) productidmax
WHERE product.prodcode = productprice.prodcode and productprice.prodpriceid = productidmax.prodpriceid

Open in new window

0
 
czechmate1976Author Commented:
Thanks, I have run the query.. it does actually selects the latest prices by the max(prodpriceid) but somewhere it duplicates them several times... well, a lot of times. I have attached a textfile with the query result (partial not all 763 records) for you to view. Please let me know if it is not very legible. And once again, really thanks for your effort to help me

It should list about 30 results
Query-result.txt
0
 
leakim971PluritechnicianCommented:
Try this :


SELECT product.*, productprice.*, productidmax.*
FROM product, productprice, (SELECT prodcode, MAX(prodpriceid) prodpriceid FROM productprice GROUP BY prodcode) productidmax
WHERE product.prodcode = productprice.prodcode AND productprice.prodpriceid = productidmax.prodpriceid AND product.prodcode = productidmax.prodcode

Open in new window

0
 
czechmate1976Author Commented:
You have nailed it, Master!!! :-) and made my Christmas :-)

Just one more question:  

SELECT product.*, productprice.*, productidmax.*
FROM product, productprice, (SELECT prodcode, MAX(prodpriceid) prodpriceid FROM productprice GROUP BY prodcode) productidmax
WHERE product.prodcode = productprice.prodcode AND productprice.prodpriceid = productidmax.prodpriceid AND product.prodcode = productidmax.prodcode

Can you please tell me little bit more about the bold sections:

- productidmax.*
- (SELECT prodcode, MAX(prodpriceid) prodpriceid FROM productprice GROUP BY prodcode) productidmax

I'd like to understand the bracketed query part.. don't want to just copy it without understanding it.

Thanks a lot
0
 
leakim971PluritechnicianCommented:
(SELECT prodcode, MAX(prodpriceid) prodpriceid FROM productprice GROUP BY prodcode) productidmax
This is a subquery (or derived table), something like a view and we name it : productidmax

productidmax.*
We want to display all fields of the "table" productidmax
0
 
czechmate1976Author Commented:
Thank you for all your effort!!! I have increased the points awarded to you
0
 
czechmate1976Author Commented:
Perffect solution for a given problem!!
0
 
leakim971PluritechnicianCommented:
Thanks for the points and Merry Christmas! See you soon!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now