Home » SQL & PL/SQL » SQL & PL/SQL » Sequence number
Sequence number [message #298916] Fri, 08 February 2008 04:38 Go to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Hi there, I generally wrote this query:
SELECT
        DISTINCT substr(ph2.old_sys_ref,1,(instr(ph2.old_sys_ref, ':', 1))-1),
        l.regional_group,
        e.employee_id,
        c.credit_limit
        avg(c.credit_limit)  -- for salespersonid (replace with employee_id)
FROM
        location l, ph2_item ph2, ph2_state_region,
        employee e, department d, job j
        , customer c,
        (select avg(c.credit_limit) avgcredlim, p.regional_group from customer c, ph2_state_region p, ph2_item ph2
        where c.state = substr(ph2.old_sys_ref,1,2) AND
        p.state = substr(ph2.old_sys_ref,1,2) group by p.regional_group, c.credit_limit) f
WHERE
        c.credit_limit = f.avgcredlim AND
        f.regional_group = UPPER(ph2_state_region.regional_group) AND
        c.state = substr(ph2.old_sys_ref,1,2) AND
        -- Match the region state
        ph2_state_region.state = substr(ph2.old_sys_ref,1,2)  AND
        -- Match the region group
        l.regional_group = UPPER(ph2_state_region.regional_group) AND
        l.location_id = d.location_id AND
        d.department_id = e.department_id AND
        j.job_id = e.job_id AND
        j.function = 'MANAGER' AND
        d.name = 'SALES' AND
        ph2.customer_id IS NULL
GROUP BY
       ph2.old_sys_ref,
       l.regional_group,
       e.employee_id
ORDER BY
        substr(old_sys_ref,1, (instr(old_sys_ref, ':', 1))-1)
;


This works fine!
However, when I try to add a sequence on it, I get an error saying that ‘sequence number is not allowed here’

drop sequence seq1 ;

-- sequence
create sequence seq1
    start with 228
    minvalue 228
    increment by 1
;


SELECT
        seq1.nextval,
        DISTINCT substr(ph2.old_sys_ref,1,(instr(ph2.old_sys_ref, ':', 1))-1),
        l.regional_group,
        e.employee_id,
        avg(c.credit_limit)  -- for salespersonid (replace with employee_id)
FROM
        location l, ph2_item ph2, ph2_state_region,
        employee e, department d, job j , customer c
WHERE
        c.state = substr(ph2.old_sys_ref,1,2) AND
        -- Match the region state
        ph2_state_region.state = substr(ph2.old_sys_ref,1,2)  AND
        -- Match the region group
        l.regional_group = UPPER(ph2_state_region.regional_group) AND
        l.location_id = d.location_id AND
        d.department_id = e.department_id AND
        j.job_id = e.job_id AND
        j.function = 'MANAGER' AND
        d.name = 'SALES' AND
        ph2.customer_id IS NULL
GROUP BY
        ph2.old_sys_ref,
        l.regional_group,
        e.employee_id
ORDER BY
        substr(old_sys_ref,1, (instr(old_sys_ref, ':', 1))-1)
;


I knew something wrong with the GROUP by and sequence. But I just couldn’t figure out how to select a sequence of numbers for the results displayed.

Please help me.

Thank you.

[Updated on: Fri, 08 February 2008 04:40]

Report message to a moderator

Re: Sequence number [message #298918 is a reply to message #298916] Fri, 08 February 2008 04:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please put code tags around your code, otherwise most people will refuse to read it in the state that it is currently in. Thanks.

[code]your code here [/code]
Re: Sequence number [message #298922 is a reply to message #298916] Fri, 08 February 2008 04:51 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
use sequnce out of the select statement.you cannot use sequence in the query which has the group by or aggregated data.

regards,
Re: Sequence number [message #298925 is a reply to message #298916] Fri, 08 February 2008 04:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What is the purpose of the sequence? What is it that you are actually trying to do?
Re: Sequence number [message #298944 is a reply to message #298925] Fri, 08 February 2008 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the purpose of DISTINCT (which is in the wrong place)?

Regards
Michel
Re: Sequence number [message #298946 is a reply to message #298916] Fri, 08 February 2008 06:02 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
I tried to put it outside the select statement but I’m not sure how exactly this will work, I can’t put
SELECT seq.nextval, SELECT (whole select statement) 

Can I?
Also, For the first code it produces results that contains:
Name, Regional Group, Employee ID and their average salary. But I also want to show a sequence number of their result starting from 228.
Means the result would be something:
229, Ali, London, 23, 2300.00
230, Aer, London, 24, 2500.00
Re: Sequence number [message #298952 is a reply to message #298946] Fri, 08 February 2008 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not 228+rownum?

Regards
Michel
Re: Sequence number [message #298955 is a reply to message #298916] Fri, 08 February 2008 06:14 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SELECT
        seq1.nextval,
        field1,
        field2,
        field3,
        field4,
        field5
from
(select DISTINCT substr(ph2.old_sys_ref,1,(instr(ph2.old_sys_ref, ':', 1))-1) as field1,
        l.regional_group as field2,
        e.employee_id as field4,
        avg(c.credit_limit)as field5  -- for salespersonid (replace with employee_id)
FROM
        location l, ph2_item ph2, ph2_state_region,
        employee e, department d, job j , customer c
WHERE
        c.state = substr(ph2.old_sys_ref,1,2) AND
        -- Match the region state
        ph2_state_region.state = substr(ph2.old_sys_ref,1,2)  AND
        -- Match the region group
        l.regional_group = UPPER(ph2_state_region.regional_group) AND
        l.location_id = d.location_id AND
        d.department_id = e.department_id AND
        j.job_id = e.job_id AND
        j.function = 'MANAGER' AND
        d.name = 'SALES' AND
        ph2.customer_id IS NULL
GROUP BY
        ph2.old_sys_ref,
        l.regional_group,
        e.employee_id
ORDER BY
        substr(old_sys_ref,1, (instr(old_sys_ref, ':', 1))-1))

Re: Sequence number [message #298956 is a reply to message #298916] Fri, 08 February 2008 06:15 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Well the DISTINCT is to select only DISTINCT names. Well I get the results but now I want to add a sequence number for that.

What do you mean 228+rownum? Problem now, I couldn’t put that sequence inside that select statement. I am planning to get that amount and then insert the results onto a table.
Re: Sequence number [message #298957 is a reply to message #298916] Fri, 08 February 2008 06:18 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SELECT
        seq1.nextval,
        field1,
        field2,
        field4,
        field5
from
(select DISTINCT substr(ph2.old_sys_ref,1,(instr(ph2.old_sys_ref, ':', 1))-1) as field1,
        l.regional_group as field2,
        e.employee_id as field4,
        avg(c.credit_limit)as field5  -- for salespersonid (replace with employee_id)
FROM
        location l, ph2_item ph2, ph2_state_region,
        employee e, department d, job j , customer c
WHERE
        c.state = substr(ph2.old_sys_ref,1,2) AND
        -- Match the region state
        ph2_state_region.state = substr(ph2.old_sys_ref,1,2)  AND
        -- Match the region group
        l.regional_group = UPPER(ph2_state_region.regional_group) AND
        l.location_id = d.location_id AND
        d.department_id = e.department_id AND
        j.job_id = e.job_id AND
        j.function = 'MANAGER' AND
        d.name = 'SALES' AND
        ph2.customer_id IS NULL
GROUP BY
        ph2.old_sys_ref,
        l.regional_group,
        e.employee_id
ORDER BY
        substr(old_sys_ref,1, (instr(old_sys_ref, ':', 1))-1))


regards,
Re: Sequence number [message #298959 is a reply to message #298957] Fri, 08 February 2008 06:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This distinct and group by combination does not make sense (if it would even work)
The group by means that you calculate an average for every old_sys_ref. Then you select a substring from that column and do a distinct.
This means that you do expect several substrings to be identical, meaning that you'd be ready to pick a random avg.

Alas, distinct does not work that way; it will do a distinct on all columns of the resultset.
Re: Sequence number [message #298973 is a reply to message #298956] Fri, 08 February 2008 06:48 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What do you mean 228+rownum?

You said: "I also want to show a sequence number of their result starting from 228..." this is what 228+rownum does.

Quote:
I couldn’t put that sequence inside that select statement

What sequence? 228+rownum? you can put it in select and gives the result you expect.

Quote:
I am planning to get that amount and then insert the results onto a table.

Which amount? The number of returned rows? The last value? What?

Regards
Michel
Previous Topic: UTL_FILE
Next Topic: Oracle Import/Export
Goto Forum:
  


Current Time: Sat Dec 03 16:18:46 CST 2016

Total time taken to generate the page: 0.11395 seconds