Solved

SQL Difficulty importing to Excel

Posted on 2014-02-10
4
248 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now