Create DB View To Show Results
Sep 10, 2007
I have 2 Table
Authors
ID Name
1 Clint
2 Voke
Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.
thanks,
View 15 Replies
ADVERTISEMENT
May 9, 2005
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
View 1 Replies
View Related
Sep 10, 2007
I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
View 2 Replies
View Related
Mar 11, 2004
This is the same issue as deinfed in http://www.dbforums.com/t987543.html
I've done some additional testing and got it down to the below
SQL2000 DB
Linked Server to DB2 using client access odbc, and MS OLE DB for ODBC
Varied results depending on the row size of the views.
See the below examples.
CREATE VIEW BLHDR_TEST
AS
SELECT STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNST CHAR(25)
TOTAL ROW WIDTH (25)
SELECT * FROM BLHDR_TEST
Returns 20971 rows * 25 = 524,725
CREATE VIEW BLHDR_TEST
AS
SELECT STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (37)
SELECT * FROM BLHDR_TEST
Returns 14169 rows * 37 = 524,253
CREATE VIEW BLHDR_TEST
AS
SELECT STNADD1, STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNADD1 CHAR(40)
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (77)
SELECT * FROM BLHDR_TEST
Returns 6808 rows * 77 = 524,216
CREATE VIEW BLHDR_TEST
AS
SELECT STNCTY,STNADD1, STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNCTY CHAR(25)
STNADD1 CHAR(40)
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (102)
SELECT * FROM BLHDR_TEST
Returns 5140 rows * 102 = 524,280
Test #1 Returns 20971 rows * 25 = 524,725
Test #2 Returns 14169 rows * 37 = 524,253
Test #3 Returns 6808 rows * 77 = 524,216
Test #4 Returns 5140 rows * 102 = 524,280
With the similarity of the total byte count returned, I would assume that a buffer or something is being overrun, is this a configuration parameter possibly.
If I perform the select against the linked table as such
Select * (for fields list)
From LURCH_PARADB.S102D4LM.PARADB.BLHDR
I get all rows returned. The issue only exist when accessing the views that are created against the linked server.
Any thoughts or ideas are appreciated.
View 1 Replies
View Related
Nov 3, 2015
I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.
View 5 Replies
View Related
Oct 29, 2007
I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.
SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'
View 5 Replies
View Related
Feb 5, 2015
My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?
Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]
[Code] ....
View 1 Replies
View Related
Aug 22, 2014
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
Oct 19, 2015
I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"
[Code] ....
View 2 Replies
View Related
Nov 19, 2007
Hi there
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
Can someone help please.
Please ask if I haven't explained myself.
thanks
Dianne
View 9 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Feb 4, 2015
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR
View 5 Replies
View Related
Oct 31, 2007
Hello Frds,
This is my query
Select Assessment_Id,Question_Id,Question_short_description,
(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer
from dbo.viewX where assessment_id ='xxxx'
Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query.
Answer_description and Answer_text are the two different fields,
i need to embed a ":" (colon) between the two data if Answer_text is having any data
else display only answer_description.
Thanks in advance..
Phani.
View 17 Replies
View Related
Jul 18, 2007
A view I created won't show up in "views' in Object Explorer, but will only show up when I script for it (Select * from blah). It also won't go into the ODBC. why?
View 8 Replies
View Related
Jan 19, 2012
I have a view with 2 columns and 2 rows(No 1). I want to show the result with 3 columns in 1 row(No 2).
No1:
The view result :
ID | SubjectID
-- ---------
13279 | 900
13279 | 910
No2:
I want to show that result in this structure :
ID | SubjectID | SubjectID2
-- --------- ----------
13279 | 900 | 910
View 1 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
Jun 3, 2014
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc
View 5 Replies
View Related
Jul 20, 2005
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
View 2 Replies
View Related
Nov 2, 2015
I have a requirement to display project start date and finish date in timescale view. The data in database table available as per below -The start date and project finish date should fill color in the timeline view as per below example, by quarters for a current financial year, last year and plus two years a head.
View 6 Replies
View Related
Apr 12, 2006
Hi,
I have currently a problem with setting up the permissions for some developers. My configuration looks like this.
DB A is the productive database.
DB B is a kind of "development" database.
Now we have a couple of users call them BOB, DAVID, ...
who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.
Therefor I added them to the db role db_owner for db b.
For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message
'Msg 262, Level 14, State 1, Procedure Test, Line 3
CREATE VIEW permission denied in database 'b'.'
I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.
What's my mistake?
Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.
Regards,
Stefan
View 8 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Apr 10, 2007
Dear sir or madam
I have a problem related to create or show relationship digram in sql server 2005. Especially, I want to show relationship diagram that I established in sqlserver 2000 in sql server 2005 but I can't and I don't how to do it.
I look forward to hearing.
Thank you in advance!
Best regard,
seyha moth
View 3 Replies
View Related
Jun 21, 2007
Hi
I Need To Load Files From A Library Show Them In The Report
And After, TO be Able To Click On one Of These Files And See Them.
The File Types Are Jpeg, Gif, Doc, Txt....
Thanks
Roy
View 1 Replies
View Related
Nov 15, 2004
I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:
id child1 child2 child3
1 sam bob chris
and i would like it like this......
id child
1 sam
1 bob
1 chris
Can anybody help me? Thanks in advance,
Bob
View 3 Replies
View Related
Mar 10, 2006
Sigh, probably simple, but somehow I just can't get it to work..
I have a complex view which generated about 9000 results, and I use
a SP to select certain results from that view. How can I modify the
results of the SP? for example I want to add a zero to every single
companynumber the SP gives me...
*it's friday, i know*
Any help would be appreciated!
/Erwin
View 1 Replies
View Related
Jul 20, 2005
Bit of an obscure one here, so please bear with me. I have two copiesof a database which should be identical. Both have a complex viewwhich is identical. I can open the views and the data is as expectedand match. I can query it in several ways as detailed below. The 5thversion of the simple query below based on the second copy of the viewfails, but works under the first copy./*1 Statement below works*/SELECT *FROM AgentHierarchyWHERE AdviserId = 6069819/*2 Statement below works*/SELECT *, AH.AdviserLastName, AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819/*3 Statement below works*/SELECT *, AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819/*4 Statement below works*/SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AH/*5 Statement below fails*/SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819The error I get is to do with conversion of data within the view. It'sa little complex, but the view works fine. It looks to me like when Irun the 5th statement above, it re-runs the view and then finds anerror.So, I took the complex view and ran that with the data output into atemporary table with the queries above run against that, and it worksfine. The problem is that the statement I need is based around the 5thone above (part of an update statement).I'm struggling to understand why some of the queries above work andone doesn't. If you look at 3 and 5 I'd expect them both to fail. Ifit failed consistently I could get further into it.The problem is that it's a little difficult to get the view itselfchanged as it was supplied by a third party, but if it hasn't changedand the data hasn't changed then it's got to be something else causingthe problem.Anyway, as I said, it's a bit obscure, but if this sounds familiar I'dbe interested in your opinion.Thanks in advance.
View 4 Replies
View Related
Mar 25, 2008
We have a large number of applications that use various sql databases. They need to validate and do lookups of information such as employees and addresses. Currently, we load data from oracle into our main sql server and push the data out to various servers. This could perhaps be simplified with replication, but is now done with some dts packages.
We are wondering if there could be a better way to do this. We would like to go directly against oracle for the address data and to sql for the employee info.
An idea I had was to have our programmers create a web service that could be called from our .net applications. That works fine for in house applications, but some vendor applications need to see a table or view. We can write a CLR proc that can connect to the web service and provide data in table format I believe.
How could we get this into a view? I've tried creating a function that calls the proc and returns the table-valued variable to the view, but it seems the functions doesn't like using 'exec myProc' in the select_statement parameter.
I suppose we could have them make an additional connection to the lookup information, stored on a central database...they are probably doing this anyway to get to the validation db on the same server.
View 5 Replies
View Related
Nov 4, 2014
I have a view over 5 tables that has started giving unreliable results. There are three records that should be different, but in a production Access database, the view is giving three identical records where there should be three unique records. I have tested the view within SQL Server Management Studio and it gives the correct records there. But, I have attached this same view into the same Access database with two separate names. One instance of the view within Access database gives the correct records, and the other gives the incorrect (duplicated) records. I have attached screen shots that show these two separately named incarnations of the same SQL View, with the duplicated data, or the unique data highlighted.
I have also included the SQL query specs for this view.
what I can do to this view in order for it to always give us the unique records that we need, rather than sometimes the correct records, and sometimes the incorrect records.
Correct 3 records:
Incorrect 3 records:
[URL]
View 4 Replies
View Related
Dec 5, 2007
show me demo of how to create foreign key in SQL server 2000
thank you
maxs
View 1 Replies
View Related
Apr 29, 1999
Wendy,
I do not know if you can see my reply after yours. Any way I try it here again.
In the DTS Export Wizard, after select the source (SQL Server)and destination (Excel),
I have 2 choices:Table copy or query.
When I use query, I can place my sp there, and the export works fine exactly as you recommended.
Thanks a lot.
But when I selected a table copy, I was given a "Select Source Tables" popup form, I can
see all the table names there, but can not find any views in the database. I do not know why.
Am I in the right place this time?
Thank you very much for your quick help.
Charlie
View 1 Replies
View Related
Aug 7, 2014
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
View 2 Replies
View Related
Dec 21, 2005
Hello,I am trying to create a view that shows the followingField1: Sum of Amounts from Table AField2: Count of Amounts from Table AField3: Sum of of Amounts from Table BField4: Count of Amounts from Table B......Field3: Sum of of Amounts from Table HField4: Count of Amounts from Table H......Things are a bit more complex but this is the gist.I am using SQL 2000.I know how to do this pretty easily using a stored procedure. But howcan I do it in a view? A SQL server won't meet my needs in thissituation.I tried OpenQuery ('myserver', 'exec myprocedure') but get the messagethat my server is not configured for data access. I tried the systemstored procedure to set data access to true but nothing seemed tohappen.I also tried Select * from (Select Statement1, select statement2)but got syntax error at the comma between statement1 and statement2.Trying to use select Statement1 as ABC to does not seem to work either.Is there a way to do what I want without making 15 views and then afinal view that shows them all together? I know I could probably dosomething by creating a ton of functions, but it really seems thisshould not be that hard...I am definitely open to any easy suggestions!Thanks,Ryan
View 3 Replies
View Related
Sep 17, 2006
Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua
View 2 Replies
View Related
