Can someone help me with this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can someone help me with this query?

Author

Topic

sharaiqa
Starting Member

7 Posts

Posted - 2007-02-06 : 09:47:17

I have 2 million rows of data. I am trying to use this select statement but I can't seem to get it to work. I need three examples of each state by state_code. Can someone look at this and tell me what I am doing wrong...

EXEC SQL BEGIN DECLARE SECTION;
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;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 09:48:12

again ? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78659

KH

sharaiqa
Starting Member

7 Posts

Posted - 2007-02-06 : 09:51:43

i have the correct forum now

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 10:25:47

[code]SELECT s.State,
s.State_Code
FROM (
SELECT DISTINCT State_Code,
State
FROM Ethnic_Vendor_Data
) AS s
CROSS JOIN (
SELECT 1 AS u
UNION ALL
SELECT 2
UNION ALL
SELECT 3
) AS x
ORDER BY s.State,
s.State_Code[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-06 : 10:53:59

"i have the correct forum now"

So you are not using Oracle any more? !!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 20:53:42

I guess sharaiqa did not gets his question on Oracle answered, has switched to SQL Server.

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources