[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Unable to save SQL query as a view to interrogate

Hi,

I am in the middle of producing a report for my directors which shows all jobs raised in the last 7 days from our database system.

I've produced a view for the basic information:

Job Ref
Job Title
Location
Primary Consultant.

Our directors would also like 'skillsets' included on the report. Long story short, these are stored in a separate table like this:

JobId       Description
-----         -------------
17893     Manager
17897     Software Development
17897     Web Development
17897     Web Services
17897     C#
17897     Java
17895     Windows 2008 Server
17895     Exchange 2007
17895     Active Directory.

I needed to list these in columns per job:

JobId       Skills
-----         -------
17893     Manager
17897     Software Development    Web Development     Web Services     C#     Java
17895     Windows 2008 Server     Exchange 2007         Active Directory.

I managed to get this done using the code below:

SELECT t1.[JobId], t1.Description,      
      (SELECT COUNT(t2.[JobId]) FROM TVAttributes t2 WHERE t2.[JobId] = t1.[JobId] AND t2.Description <= t1.Description)AS Ordinal
INTO zztest
FROM  TVAttributes t1
ORDER BY t1.[JobId], t1.Description

DECLARE @sql varchar(8000)

SET @sql = 'SELECT DISTINCT t1.[JobId]'

SELECT @sql = @sql + ', (SELECT t2.Description FROM zztest t2 WHERE t2.[JobId] = t1.[JobId] AND t2.ordinal = ' +
      CONVERT(varchar, ordinal) + ') AS Column' + CONVERT(varchar, ordinal)
FROM zztest
GROUP BY ordinal
ORDER BY ordinal

SET @sql = @sql + ' FROM zztest t1 ORDER BY t1.[JobId]'

EXEC(@sql)
DROP TABLE zztest

My problem is that I am unable to save this query as a view. Is there a way of saving this as a view (tweaking the code if necessary)??

Once it's in a view, I can call it from another query and produce the data that I need to.

I look forward to your comments,

Thanks in advance!
0
robsamuel2k8
Asked:
robsamuel2k8
  • 3
  • 3
2 Solutions
 
Nico BontenbalCommented:
You could create a custom function that retrieves the skills for a jobID as a string. You can then use this function in and SQL statement, and this SQL statement can be saved as a query.
0
 
kamindaCommented:
When a ORDER BY clause is used in a View query it is Mandatory in SQL Server to use the TOP keyword in the SELECT list. You can use TOP (100) PERCENT to get all the result set in your view.

Something like this
SELECT TOP (100) PERCENT t1.[JobId], t1.Description,      
      (SELECT COUNT(t2.[JobId]) FROM TVAttributes t2 WHERE t2.[JobId] = t1.[JobId] AND t2.Description <= t1.Description)AS Ordinal
INTO zztest
FROM  TVAttributes t1
ORDER BY t1.[JobId], t1.Description

Open in new window

0
 
Nico BontenbalCommented:
Here's a complete example that creates the table, fills the table with some test data, creates the function, creates the view and then returns the view. So it should be run in an Empty database.
I used 2008 R2, but I'd be surprised if it didn't work in previous versions.
 
--create table
create table TVAttributes(JobID int, Description varchar(100))

--insert data into table
insert into TVAttributes values(17893,'Manager')
insert into TVAttributes values(17897,'Software Development')
insert into TVAttributes values(17897,'Web Development')
insert into TVAttributes values(17897,'Web Services')
insert into TVAttributes values(17897,'C#')
insert into TVAttributes values(17897,'Java')
insert into TVAttributes values(17895,'Windows 2008 Server')
insert into TVAttributes values(17895,'Exchange 2007')
insert into TVAttributes values(17895,'Active Directory.')

--create the function
go
create function getSkills(@JobID int) returns varchar(8000)
as
begin
    declare @return varchar(8000) = ''
    declare @skill varchar(100)
    DECLARE cur CURSOR FOR SELECT Description FROM TVAttributes WHERE JobID = @JobID
    OPEN cur;

    FETCH NEXT FROM cur into @skill
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @return = @return + ',' + @skill
        FETCH NEXT FROM cur into @skill
    END
    CLOSE cur;
    DEALLOCATE cur;
    set @return = SUBSTRING(@return,2,LEN(@return))
    return @return
end
go

--create the view
create view showSkills
as
select JobID, dbo.getSkills(JobID) as Skills from TVAttributes group by JobID
go

--return the view
select * from showSkills

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Anthony PerkinsCommented:
kaminda,

>>You can use TOP (100) PERCENT to get all the result set in your view.<<
Please don't encourage this.  While it makes it syntactically correct, the author may actually believe that the resultset will be sorted based on the ORDER BY clause.  Nothing can be further from the truth.  The QO is smart enough to know that you have requested all the data and will promptly ignore the ORDER BY clause.
0
 
Anthony PerkinsCommented:
robsamuel2k8,

You may be able to use a VIEW providing that you are using at least SQL Server 2005.
0
 
Nico BontenbalCommented:
0
 
Anthony PerkinsCommented:
Right, that is what I meant by "providing that you are using at least SQL Server 2005"
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now