Solved

SQL Difficulty importing to Excel

Posted on 2014-02-10
4
258 Views
Last Modified: 2014-03-06
I am trying to bring data into Excel with some SQL code. The code works fine and when I work with it in Microsoft Query it returns the data (while saying the Query cannot be represented graphically). However, when I return the data to Excel all I get is "Query from [Database]" and none of the data. Is there a way to get the data I need into a spreadsheet?

declare @curr int
declare @cstatus int
declare @cnotin int
DECLARE @cepp int
DECLARE @cproduction INT
DECLARE @cdone INT

set @curr= (select min(jobn) from OpenJob where jobStatus in (0,2) and jobN > 0)
SET @cnotin = 0
SET @cepp = 0
SET @cproduction = 0
SET @cdone = 0


WHILE @curr <= (SELECT MAX(jobN) FROM openJob WHERE jobStatus IN (0,2))
BEGIN

	IF EXISTS(SELECT jobn FROM openjob WHERE jobn=@curr AND jobStatus IN (0,2))
		BEGIN

			SELECT @cstatus=StatusCoden FROM statuscode where statuscoden = (select statuscode from JobStatus1 where jobn = @curr and transactionN = (select max(transactionN) from jobStatus1 where jobn=@curr))
			

			if @cstatus IS NULL set @cstatus = ''
			IF @cstatus BETWEEN 35100 AND 35108 SET @cnotin = @cnotin + 1
			ELSE 
				IF @cstatus BETWEEN 35109 AND 35254 SET @cepp = @cepp + 1
				ELSE
					IF @cstatus BETWEEN 35255 AND 78100 SET @cproduction = @cproduction + 1
					ELSE
						IF @cstatus >= 78101 SET @cdone = @cdone + 1	


		END	
	set @curr = @curr + 1
END

SELECT @cnotin AS 'Not In', @cepp AS 'EPP', @cproduction AS 'Production', @cdone AS 'Done';



			IF @cstatus BETWEEN 35100 AND 35108 SET @cnotin = @cnotin + 1
			ELSE 
				IF @cstatus BETWEEN 35109 AND 35254 SET @cepp = @cepp + 1
				ELSE
					IF @cstatus BETWEEN 35255 AND 78100 SET @production = @production + 1
					ELSE
						IF @cstatus >= 78101 SET @done = @done + 1	

Open in new window

0
Comment
Question by:mjburgard
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39849050
The code works fine and when I work with it in Microsoft Query it returns the data
Your code does not return any data, it just sets some local variables.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39850141
Perhaps something got chopped out of your example, like a SELECT-statement.

You could put all the logic and data-gathering into a stored-procedure, and get MS-Excel to run that stored-procedure to produce a result-set.
0
 
LVL 1

Author Comment

by:mjburgard
ID: 39871307
The code accidentally included extra lines. Everything after line 38 shouldn't be there. (Is it possible to edit the question and fix this? I cannot figure out how to.)

The SELECT statement on line 38 returns the data I was talking about.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 39872005
The SELECT statement on line 38 returns the data I was talking about.
My mistake I overlooked that.

So all you are missing is the following code at the top of your script:
SET NOCOUNT ON
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question