Home » SQL & PL/SQL » SQL & PL/SQL » Can someone pls help me with this one... (merged)
Can someone pls help me with this one... (merged) [message #218039] Tue, 06 February 2007 09:19 Go to next message
sharaiqa
Messages: 7
Registered: February 2007
Junior Member
I have a database with 2 million records. On this table, one of the columns that I need is the state_code. There are 50 states codes and I need three records for each state code. I am playing around with the following query but I can seem to get it to work.. I am new to sql and I am not sure if this is the proper syntax for what I am attempting to do...Can someone take a look and tell me what I am doing wrong...

DECLARE @sql varchar(8000;
v_counter INTEGER DEFAULT 0;
v_given VARCHAR(12);
v_surname CHAR(22);
v_surname VARCHAR(60);
v_city VARCHAR(13);
v_state VARCHAR(2);
v_zip VARCHAR(5);
v_census_tract VARCHAR(12);
v_matchcode VARCHAR(22);
v_state VARCHAR(2);
v_state_code VARCHAR(2);
v_c1 CURSOR FOR
EXEC SQL END DECLARE SECTION;
SELECT given, surname, address, city, state, zip, census_tract, matchcode, state_code, state
FROM ethnic_vendor_data;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET counter = -1;
OPEN c1;
fetch_loop:
LOOP
FETCH c1 INTO v_given; v_surname; v_lastname; v_surname; v_city; v_state; v_zip; v_census_tract; v_matchcode; v_state; v_state_code;
IF v_state = ' ' THEN
LEAVE fetch_loop;
END IF;
SET v_counter = v_counter + 1;
END LOOP fetch_loop;
SET counter = v_counter;
CLOSE c1;
BEGIN
...
END;
Re: Pls help with the query.. [message #218044 is a reply to message #218039] Tue, 06 February 2007 09:41 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

.

[Updated on: Tue, 06 February 2007 10:15]

Report message to a moderator

Re: Pls help with the query.. [message #218046 is a reply to message #218044] Tue, 06 February 2007 09:50 Go to previous messageGo to next message
sharaiqa
Messages: 7
Registered: February 2007
Junior Member
no becuase the way the query is written, it only return less than three state_codes. I need three records for each of the 50 state codes... but thanks for looking into it.
Can someone pls help me with this one.... [message #218058 is a reply to message #218039] Tue, 06 February 2007 10:56 Go to previous messageGo to next message
sharaiqa
Messages: 7
Registered: February 2007
Junior Member
I have a database with 2 million records. On this table, one of the columns that I need is the state_code. There are 50 states codes and I need three records for each state code. I am playing around with the following query but I can seem to get it to work.. I am new to sql and I am not sure if this is the proper syntax for what I am attempting to do...Can someone take a look and tell me what I am doing wrong...

DECLARE @sql varchar(8000;
v_counter INTEGER DEFAULT 0;
v_given VARCHAR(12);
v_surname CHAR(22);
v_surname VARCHAR(60);
v_city VARCHAR(13);
v_state VARCHAR(2);
v_zip VARCHAR(5);
v_census_tract VARCHAR(12);
v_matchcode VARCHAR(22);
v_state VARCHAR(2);
v_state_code VARCHAR(2);
v_c1 CURSOR FOR
EXEC SQL END DECLARE SECTION;
SELECT given, surname, address, city, state, zip, census_tract, matchcode, state_code, state
FROM ethnic_vendor_data;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET counter = -1;
OPEN c1;
fetch_loop:
LOOP
FETCH c1 INTO v_given; v_surname; v_lastname; v_surname; v_city; v_state; v_zip; v_census_tract; v_matchcode; v_state; v_state_code;
IF v_state = ' ' THEN
LEAVE fetch_loop;
END IF;
SET v_counter = v_counter + 1;
END LOOP fetch_loop;
SET counter = v_counter;
CLOSE c1;
BEGIN
...
END;
Re: Can someone pls help me with this one.... [message #218061 is a reply to message #218058] Tue, 06 February 2007 11:10 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It appears that you do not know the difference between SQL & PL/SQL.
Why are you posting PL/SQL code is the SQL EXPERT forum?
Why are you using PL/SQL when straight SQL will provide solution?

Desperate for a solution?
http://www.dbforums.com/showthread.php?t=1614752

[Updated on: Tue, 06 February 2007 11:12] by Moderator

Report message to a moderator

Previous Topic: ORA-00904: "ROWID" invalid identifier
Next Topic: Full path to the spfile ?
Goto Forum:
  


Current Time: Thu Dec 08 04:21:58 CST 2016

Total time taken to generate the page: 0.06889 seconds