Making A View That Shows The Results Of Several Different Queries.
Dec 21, 2005
Hello,
I am trying to create a view that shows the following
Field1: Sum of Amounts from Table A
Field2: Count of Amounts from Table A
Field3: Sum of of Amounts from Table B
Field4: Count of Amounts from Table B
..
..
..
Field3: Sum of of Amounts from Table H
Field4: 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 how
can I do it in a view? A SQL server won't meet my needs in this
situation.
I tried OpenQuery ('myserver', 'exec myprocedure') but get the message
that my server is not configured for data access. I tried the system
stored procedure to set data access to true but nothing seemed to
happen.
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 a
final view that shows them all together? I know I could probably do
something by creating a ton of functions, but it really seems this
should not be that hard...
If I want to download a file, but I don't know if it's available yet (actually positive it won't be available for some time), how do I make FTP Task retry/wait until file shows up in the ftp folder?
I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?
The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?
The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!
ERD above is what my database looks like (ignore it's in Access, database is in SQL 2005)
I have this code:
SELECT orderID, orderAmount = SUM(customerID)/customerID FROM orders GROUP BY orderID -- When Sum of the customer ID (and then) divided by the customer ID > 1 HAVING orderAmount > 1
which doesn't work because I never did find out how you make a column in the results to output your maths in. orderAmount doesn't exist as a column in the database, but for this query it should show only those customers who have ordered more than once with the company.
I have a few questions related to using CONTAINSTABLE in a query that I hope someone can help with.
I am working on a project to add document search capabilities to my companies product using fulltext indexing. Part of this requirement is an ability to breakdown the component parts of of the search query and provide information on *why* documentX ranked higher than documentY. This is a bit convoluted, but taking this (very simple) example - the user wishes to search for 2 skills - "HTML" and/or "XML". The generated query looks a little like :-
Select DOC.DOC_ID, RANK1.RANK, RANK2.RANK, RANK3.RANK from DOCS DOC inner join CONTAINSTABLE(docs, doc, 'HTML AND XML') as RANK1 on RANK1.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'HTML') as RANK2 on RANK2.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'XML') as RANK3 on RANK3.DOC_ID=DOC.DOC_ID
This returns the "overall" rank, and a rank for the 2 component parts, so I can say this doc ranked XXX overall because it scored "rank1" for HTML and scored "rank2" for XML etc....
My question on this part is about the values for the "overall rank". If the query contained an OR it always seems to return the highest of the "rankX" values, and if it doesnt, it returns the lowest. e.g. for the example for java and word and excel and access - the overall ranking is 2 , java=36, word=2, excel=16 and access=36 for java and word or excel and access - the overall ranking is 16 , java=36, word=2, excel=16 and access=36 for (java and word) or (excel and access) - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
So in the first example, regardless of what the other values are, the rank returned is always 2 (the score for "word"). My resultset has 100ish rows, all with a rank of < 5 for word, but all with ranks of 18-100 for the other 3 values - yet the "overall" rank always matched the "word" rank.....?? This doesnt feel right to me somehow, I would expect a different value as if the document ranked really highly for one value but low for the other, it doesnt feel right the value is clamped to the lowest? Or am I just understanding it wrong? If I use "freetexttable" the overall rank is a little more meaningful - but unfortunately I also need to use weighting, which brings me to my next question . . .
This question is about rankings returned from the ISABOUT function. In the following example, select * from documents as DOC inner join containstable(docs,doc,'project') as doc0 on DOC.DOC_ID=doc0."key" inner join containstable(docs,doc,'ISABOUT (project weight (1.0))') as doc1 on DOC.DOC_ID=doc1."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.5))') as doc2 on DOC.DOC_ID=doc2."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.1))') as doc3 on DOC.DOC_ID=doc3."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.0))') as doc4 on DOC.DOC_ID=doc4."key" order by doc0.rank desc
The values I get from the doc1/2/3/4.RANK columns dont seem right. In this example,
doc0.rank = 133 doc1.rank = 150 doc2.rank = 330
doc3.rank = 924
doc4.rank = 0
These values dont make any sense to me, as the rank seems to go UP when the documentation on ISABOUT says it goes down (I think it says somewhere the calculated rank is multiplied by the weight?). Once again, is there something I missed or am I understanding it wrong?
Thanks in advance for any help into understanding the whys of this...
Currently i have set of queries which i run for data extraction and result pasted in Excel table so that my pivot table and Chart gets populated along with the summary in Excel file.
I would like to automate this thing and want to know if i can achieve excel output for dashboard on a click of button.
Hello, I have 2 tables, first is the mineral values, 2nd is ore composition... Minerals table:
Mineral Value
Mineral1 10
Mineral2 20Ore composition table:
Ore Mineral1 Mineral2
Ore1 100 0
Ore2 0 200
Ore3 10 10Now what I need to do is to have a view (or something else that gerenates a table) that will give me the value of each ore. So for example ore 3 would be 10 X mineral1 value plus 10 X mineral2 value. So the result should be:
Ore Value
Ore1 1000
Ore2 4000
Ore3 300Can anyone point me to some tutorial or example of a similar calculation with sql views?
I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.
I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?
what conditions a job step my be showing in the job history as both running AND completed successfully?
I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"')) When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total from order left join orderstatus o1 on order.id = o1.order_id left join orderstatus o2 on order.id = o2.order_id left join org on order.orgid on user.id where o1.status = 'Open -> Assigned' and o2.status = 'Assigned -> Responded' and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query select org.name, NULL AS correct, count(order) AS Total from order left join orderstatus o1 on order.id = o1.order_id left join orderstatus o2 on order.id = o2.order_id left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total org1 324 NULL org1 NULL 423
How can I combine them, or maybe doing it a better way?
Hi all,Here is my problem, I have 3 tables :People-------------IDPeopleFirstnameLastnameCars------------IDPeopleCarnameBoats------------IDPeopleBoatname1 person can have 0 or n car/boatI want to a result set displaying : Firstname, Lastname, NumberOfCars,NumberOfBoatsI have two queries, but i want to merge the results in one. how can i dothis ?This one gives me FIRSTNAME, LASTNAME and CARCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Cars.CarName) AS CARCOUNTFROM dbo.People LEFT OUTER JOINdbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThis one gives me FIRSTNAME, LASTNAME and BOATCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Boats.BoatName) AS BOATCOUNTFROM dbo.People LEFT OUTER JOINdbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThanks in advancePhil
Does anyone know how I can 'join' the results ofone SQL query to the bottom of another?Eg. I have two queries:1. SELECT Name, Surname FROM People WHERE Surname = SmithNAME SURNAMEAdam SmithJohn SmithMichael SmithSteve Smith2. SELECT Name, Surname FROM People WHERE Surname = JonesNAME SURNAMEBob JonesLarry JonesTom JonesWhat I want to produce is:NAME SURNAMEAdam SmithJohn SmithMichael SmithSteve SmithBob JonesLarry JonesTom JonesHowever, if I use UNION like this:SELECT Name, Surname FROM People WHERE Surname = SmithUNIONSELECT Name, Surname FROM People WHERE Surname = Jonesit mixes up all the results:NAME SURNAMEAdam SmithBob JonesJohn SmithLarry JonesMichael SmithSteve SmithTom Jones(I guess it's sorting by the first field, NAME).Is there a way to stop it sorting the results, so that itjust tacks the second query results to the bottom of thefirst query results?(I realise I could use "ORDER BY Surname" to get the same resultin this simple example, but for the more complicated queriesI want to use it won't work).Thanks for any help,Matt.
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT ({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns, Filter (([Store].[Store No].[Store No].Members), ([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows from finance where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] , [Measures].[GL Amount])
"SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1 FROM TBL_STOK INNER JOIN" _
TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id " _
where TBL_BARKOD.barkod=@barkod"
second query :
"SELECT TBL_STOKDEPO.fiyat1 FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id" _
where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod "
i want to merge these queries' results.first query returns 2 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1) second query returns 1 column (TBL_STOKDEPO.fiyat1) .but i want a query that returns 3 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1,TBL_STOKDEPO.fiyat1)
I have been using T-SQL for a while now although the majority of my work required relativley simple queries. I just need to know is there a way to JOIN the results of several SELECT queries, maybe through the use of functions??
A reference to any online article would be most helpful.
I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
I am running a SQL stored procedure which runs 3 queries on 3 different SQL tables. What is my best option to export the results of these 3 queries to excel?
If it matters they are all SELECT queries, and at most will return < 500 rows.
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"
We have a client who runs SQL Server 2000 queries on one database server and performance is approx. 4 seconds. If the database is backed up, no tables in the query or indexes on these tables are modified (we may run a small script that affects stored procedures, views, etc.) the query can run virtually forever.
The customer is runing a cluster and we are running a stand-alone. Although, the two environments that they run in and have these wildly different results in are the same.
The queries are not worth listing (join a couple of tables and views, select a few columns, put on a few conditions--nothing crazy).
Is this normal behavior for MS SQL Server?
I've personally seen where a database is backed up and query plans and performance are different from one server to another, but we are looking at extreme cases here. In fact, on the second server, the majority of the queries are faster and only a couple run very slowly.
Also, the query optimizer seems to be making poor decisions at this custoemer. For example, two tables will be cross joined (forming over 200 million records) and then table scans ensue. The process in some cases will take a 4 second query to 45 minutes.
To me none of this makes any sense. I've been working with SQL Server since 1997 and have not experienced any type of performance problems or variances of this magnitude. Although this is a 6GB database, SQL Server 7 ran on a terabyte without even blinking, so I wouldn't understand why this would have anything to do with it.
Also hampering our efforts is that we do not have easy access to this SQL Server database to get our hands on it and debug these issues.
Does anyone know of a way to examine these issues in a "system wide" manner to determine what the problems could be since the problems are not specific to the database (i.e. .bak file) but seem to be specific to the server?
They have also had database corruption (an index that wouldn't update) and had to roll back the database. Would that indicate that the MDF/LDF's are unstable? Is there a way to figure out if there is some type of MDF/LDF file structure corruption?
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID LEFT JOIN Customers ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF display_term: END OF FILE column_id: 2 document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours FROM dbo.tblEventStaffRequired;
and SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked FROM tblStaffBookings GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
Is there any way to view the queries that a SqlDataSource executes against your chosen data source at runtime? i.e. after all the parameters have been substituted with their values.I'm assigning a number of parameters at runtime to its selectcommand, and i'm getting some exceptions thrown when attempting to update or delete records from a gridview using it (it's a really old Synergy database, which can be quite particular about the structure of queries). It would be a massive help if i could actually see the exact queries the datasource is attempting to use.Cheers for any help
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id, dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note into #temp_group_sessions FROM dbo.group_session_client, dbo.group_session WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services LEFT OUTER JOIN #temp_group_sessions on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id ;
-- Drop temporary tables
DROP TABLE #temp_group_sessions; DROP TABLE #temp_services;
I am able to step into a sql stored proc through the VS IDE. I am able to look at some values in the quick watch window. But I need to examine the results of certain queries. Like a select statement that gets placed into a temp table. How can I view the results of the query? If their is not an elegant way of doing this in the debugger, then should I put sql statements in the stored proc, that would display the results of the query?(Assuming I would remove them after debugging) If the answer is yes, then maybe someone can give me some ideas. Like how would I print to the screen the results of the query, or be able to view the results of the query? My sql skills are a bit weak, so I would appreciate any help I can get. Ralph Goodwin
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:
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!
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.
