SELECT * FROM
(SELECT row_number() OVER (ORDER BY custname) as resultNum,
t_enduser.id,substring(t_enduser.custcode,1,3), custname,city1,state1,
t_enduser.createdby,t_enduser.createdtime as ct, t_details.serialnumber,
t_details.package,startdate,endDate,endProduct
from t_enduser,t_master,t_details
where t_enduser.id=t_master.custcode
and t_master.t_ref=t_details.t_ref
and lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories' and status='valid'
and t_details.t_ref in
(SELECT rh.t_ref FROM t_details rh,
(SELECT max(endDate) as maxdate, serialNumber FROM t_details
where lOWER(package)<>'pdahardwareonly' and
lOWER(endproduct)<>'accessories' and status='valid'
GROUP BY serialNumber
)maxresults
WHERE rh.serialNumber = maxresults.serialNumber AND
rh.endDate= maxresults.maxdate
)
)as numberResults where resultNum '1' and '10'
It's difficult to say what's wrong seeing without seeing error messages or sample data.
One thing that stands out, however, is the WHERE clause:
where resultNum '1' and '10'
Should this be:
where resultNum IN('1', '10')
or if resultNum is of a numerical datatype:
where resultNum IN(1, 10)
?
Chris
|||The first line says
Select * from
but there is nothing in the from clause.
There is a lot more wrong with this query.
|||The query will be perfectly valid once the final WHERE clause has been sorted out.
Chris
|||Firstly, I hope the code is better formatted on your end. I know it is pretty ugly to past directly into the forums, so consider pasting to text first. I would consider reformatting for our value if you post such amounts of code as youi have a very messy query. I have highlighted a few things that seem troublesome, but nothing overly horrible.
You are going to have to give more information...
SELECT *
--alias all names. We can't help you if you won't give all information.
FROM (SELECT row_number() OVER (ORDER BY custname) as resultNum,
t_enduser.id,
substring(t_enduser.custcode, 1, 3),
custname, city1, state1, t_enduser.createdby,
t_enduser.createdtime as ct,
t_details.serialnumber, t_details.package,
startdate, endDate, endProduct
from t_enduser
,t_master --is this table_master, or does t_ have other meaning. Master is a weak name
--master, what?
,t_details
--learn about the JOIN criteria instead of using the comma based cartesion product style join
--syntax, it is easier to follow, and certainly to debug
where t_enduser.id = t_master.custcode --Customers are also endusers? This seems odd
and t_master.t_ref = t_details.t_ref --t_ref? Is this the pkey of one of your tables? Or all?
--Why lower? Is your database case sensitive? If so, then use a constraint to make sure
--package and products forllow the rules.
and LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid'
and t_details.t_ref in (
SELECT rh.t_ref
FROM t_details rh
,(SELECT max(endDate) as maxdate,
serialNumber
FROM t_details
WHERE LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid'
GROUP BY serialNumber) maxresults
WHERE rh.serialNumber = maxresults.serialNumber
AND rh.endDate = maxresults.maxdate))
as numberResults
where resultNum between 1 and 10 --this is the code that needed fixed
--also you can redo the in clause using the rowNumber function to make it a little easier...
select getRows.tref
from (SELECT rh.t_ref, row_number() over (partition by serialNumber order by endDate) as rowNum
FROM t_details
WHERE LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid') as getRows
where getRows.rowNum = 1
Louis thanks for you tips. As what you suggested “between” is the problem. Basically
I want to combine three tables and those three tables cross-reference each other.
My task requirement in such I need to pull the transactions in one
stretch customer details and his last product purchased that is by expiry date.
1. Find the product and its expiry date (maximum end date is the exp date)
2. Get the customer details for that reference
3. Pagination
4. Get row number for each records
Customer Details TABLE NAMES (T_ENDUSER)
Transaction TABLE NAMES (T_MASTER,T_DETAILS)
After I made the correction, The following query can work for me. But as what you suggested my join criteria,
I feel like dump. Can u please tune up for me?
SELECT * FROM (
SELECT row_number() OVER (ORDER BY custname) as RESULTNUM,
T_ENDUSER.id,T_MASTER.t_ref
from T_ENDUSER,T_MASTER,T_DETAILS
where T_ENDUSER.id=T_MASTER.custcode
and T_MASTER.t_ref=T_DETAILS.t_ref
and lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories'
and status='valid'
and T_DETAILS.t_ref in
(SELECT rh.t_ref FROM T_DETAILS rh,
(SELECT max(endDate) as maxdate, serialNumber FROM T_DETAILS where lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories'
and status='valid' GROUP BY serialNumber
) as EXPDATE
WHERE rh.serialNumber = EXPDATE.serialNumber
AND rh.endDate= EXPDATE.maxdate)
)as NUMRESULTS where RESULTNUM between '1' and '20'
I was just suggesting that you use the new style joins instead:
SELECT *
FROM (SELECT row_number() OVER (ORDER BY custname) as RESULTNUM,
T_ENDUSER.id, T_MASTER.t_ref
from T_ENDUSER
JOIN T_MASTER
on T_ENDUSER.id = T_MASTER.custcode
JOIN T_DETAILS
on T_MASTER.t_ref = T_DETAILS.t_ref
where lOWER(package) <> 'pdahardwareonly'
and lOWER(endproduct) <> 'accessories'
and status = 'valid'
and T_DETAILS.t_ref in (
SELECT rh.t_ref
FROM T_DETAILS rh
join (SELECT max(endDate) as maxdate,
serialNumber
FROM T_DETAILS
where lOWER(package) <> 'pdahardwareonly'
and lOWER(endproduct) <> 'accessories'
and status = 'valid'
GROUP BY serialNumber) as EXPDATE
ON rh.serialNumber = EXPDATE.serialNumber
AND rh.endDate = EXPDATE.maxdate))
as NUMRESULTS
where RESULTNUM between 1 and 20 --Also, these should be numbers, not characters
The version you did will work just fine, but this is a lot easier to follow, and is far better for outer joins...
No comments:
Post a Comment