I have a view that is using UNION ALL to combine common fields of two tables, this is my statement:
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_FRAME_T
UNION ALL
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_ATM_T
This works fine, but I would also like some fields that do not match to appear in the view. It is OK if the value is null for the rows of data from the other table that doesn't have the columns.
The other columns are called DLCI from CT_FRAME_T and then VPI, VCI from CT_ATM_T.
My view would then return ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI (where applicable), VPI and VCI (where applicable). Is this possible?
I've managed to grouped by Category field nicely and added extra calculated column for handling to total plus as well as Total for each group plus added a new row for TOTAL in the TABLE and returned as follow:
Now I want to put another extra row underneath TOTAL for handling the calculated but the business rule is only for displaying from the first group for instance:
TOTAL: $3032 Calculated Average per fruits: $1010.67 ($3032 / 3 UNITS).
3 UNITS is coming from Units for Apple and Banana.
I don't know if I can do this cause i did try to use
But it's successfully compiled but the report result error (#Error) on that field only. Any ideas? Also ... someone mentioned that I should look at the SCOPING but I don't work it how's work anyway.
I thought a simple like this can be handled quite easily instead of creating a new report view type in the the same dataset to handle this particular extra line.
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db. ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is: select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user) I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net. Many thanks
Before I start just wanna say thanks for all the top advice so far - much appreciated. My current quandry is this:-
I have a table called STOCK with columns ITEM_ID, ITEM & PRICE. Most of the stock is my own produce but i do buy-in some items. What i want to do is add some extra columns to STOCK for the supplier details on those items i buy in. The problem is that a lot of the fields in the supplier details columns will be empty. Would this be a problem or would it be better to have a seperate table for supplier details?
I need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.
I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?
This is an example of how I want my report to look (I can already achieve the bits highlighted bold)
Daily Data
Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc €¦ SUBTOTAL (DTD) MTD TOTAL YTD TOTAL
Book Currency =sum(Fields!DTD.VALUE)
........
=sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE)
and this is an example of the data being returned by my Sproc
Book CCY GroupedData DTD MTD YTD
ABC GBP Commission £0.01 £0.09 -£0.10
ABC GBP Interest £0.02 £0.29 £0.11
ABC GBP Brokerage £0.12 £0.06 £0.20
When I use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.
Daily Data
Grouped Data CELL 1 Grouped Data CELL 2
Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL
I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.
I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.
Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.
I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.
This is my code so far:
Create View Table_AB As Select *From Table_A Union All Select*From Table_B
I’m receiving the following message when attempting to run the SQL statement below.
Error report: SQL Command: force view "UIP_SOC"."SEG_VIEW_EWO_2" Failed: Warning: execution completed with warning
----------------------- CREATE OR REPLACE FORCE VIEW "UIP_SOC"."SEG_VIEW_EWO_2" ("CODE", "NAME", "EWO4", "EWO6") AS SELECT DISTINCT code, name FROM ( SELECT seg_value AS code, seg_desc AS name, SUBSTR(seg_value,5,4) AS EWO4, SUBSTR(seg_value,5,6) AS EWO6 FROM UIP_SEGMENT_VALUES WHERE seg_name = 'EWO' AND seg_value IN (SELECT ewo FROM stage_budget_v) UNION SELECT CODE,NAME FROM SEG_VIEW_PARENTS WHERE SEG_NAME = 'EWO' AND NOT (CODE IS NULL) ); ----------------
I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.
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
I have a view V1 created as a plain UNION from 2 tables:
SELECT * FROM T1 UNION SELECT * FROM T2
If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned. However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.
Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?
I attempted to create a view in SQL Server 2000 that Unions twoqueries. The first part of the query gets data from the local server,the second part gets info from a linked server. (The query works finein Query Analyzer.)I received this error when I tried to save the query:ODBC error: [Microsoft][ODBC SQL Server Driver] The operation couldnot be performed because the OLE DB provider 'SQLOLEDB' was unable tobegin a distributed transaction.[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB providerreturned message: New transaction cannot enlist in the specifiedtransaction coordinator.]After a little reading I discovered the "Database limitation":"A view can be created on a table only in the database the viewcreator is accessing".That's my problem... is there a simple solution or alternative tocreating a view?Thanks,Matt
I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this: select name from sysobject where name like 'AT_%'
Now I try to create a view AT which is the union of all these AT_XX tables, such as:
Code Snippet
Create View AT as select * from AT_20080401 union select * from AT_20080402 union ...
but since I'm not sure how many tables there, it would be impossible to write SQL as above. though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure. Create View AT as select * from AT_createView() <-- AT_createView must be a function, not procedure
I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.
So is there any way to achived my goal? any reference would be appreciated, thanks !
-- populate declare @i int set @i = 1000 while @i > 0 begin insert into dbo.test1 select @i, '1.' + cast(@i as varchar(5)) set @i = @i - 1 end
insert into dbo.test2 select 1, '2.1' union all select 2, '2.2' go
-- create view create view dbo.vw_Test as select1 as QueryID, TestName fromdbo.Test1 union all select2 as QueryID, TestName fromdbo.Test2; go
-- this works as i want, only scans table dbo.Test2 select * fromdbo.vw_Test whereQueryId = 2
-- joining to a table triggers scan of both tables in view: declare @table table (QueryID int) insert into @table select 2;
selectvt.TestName fromdbo.vw_Test vt join@table t on vt.QueryID = t.QueryID
Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.
I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null
I need it to dynamically output based off COL1, the output should look like this. When there are more rows for CAT, it should output more columns. Kind of like merging the columns
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
I have two tables Encounter & Encounter_History. They have same columns. One column is of type CLOB. My requirement is to retrieve all the distinct records from both the tables with order by a date column. But problem is, UNION does not work in case of CLOB data type.
I know it will work if I use UNION ALL, but it returns duplicate records.
Please give me suggestion, how to solve this problem.
For example: The following query does not work since column1 is a CLOB data type
select column1 from table1 union select column1 from table2
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100 101 Vol charity 200 101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error: Server: Msg 207, Level 16, State 3, Line 2 Invalid column name 'PRODUCT_CODE'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'.
SELECTp.ID, p.PRODUCT_CODE as Chapt, p.product_code as Dues, p.product_code as Vol from ( SELECT ID, product_code as Chapt, Null as dues, Null as Vol from subscriptions where prod_type = 'chapt' AND BALANCE > 0
union all
SELECT ID, Null as chapt, product_code as Dues, Null as vol from subscriptions where prod_type = 'dues' AND BALANCE > 0
union all
SELECT ID, Null as chapt, Null as dues, product_code as Vol from subscriptions where prod_type = 'vol' AND BALANCE > 0
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns> UNION SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
Any idea why I cannot see (in Enterprise Manager) all the coulmns of a selected table? All I get is * all columns - not each one, meaning I cannot select individual columns in my new view. Could it be the version? I am running SQL 7.0 SP1
I'm having an odd issue with SQL Server. What happens is the following: I create view1. I then create view2 which uses view1. I then create view3 which uses view2. Then, if I make a change in View1, my other views are messed up. What happens is that columns in view3 (and view2) will be switched. Therefore, if a crystal report for example references view3, I'll suddenly get an exception due to a field in the cyrstal report thinking it's going to get an integer but gets a date instead. It's as if the columns were moved around. Is this a known issue with SQL Server 2000? Has someone else had that here? Has someone resolved that here? thx.
Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help
Create View BONUS_PAYOUTS As
Alter Table Employee Add EMP_BONUS Decimal(6,2)
Update Employee Set EMP_BONUS = EMP_PCT / 500000
Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS From Employee Inner Join Position On Employee.POS_ID = Position.POS_ID
I have a view that I created from 4 SQL tables in order to query data for a report. I can't change the format of columns in the original table but would like to format the columns in the view as a different data type.
The original table has the values formated as varchar, the info in the columns is numbers and I would like to have the values changed to decimal when the view is queried.
Is this even possible? Any help would be appreciated.
Hi All, I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join? Table A Table B Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5 A Jan 5 xxx A Jan 1 yyy B Feb 3 C Mar 4 B Mar 4 C Apr 3 C Apr 2 D May 2 E Mar 1
Result Table Column1 Column2 Column3 Column4 Column 5 A Jan 6 (= 5+1) xxx yyyy B Feb 3 B Mar 4 C Mar 4 C Apr 5 (= 2+3) D May 2 E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5. I have published Tables, Views and SPs as articles. One of the views has more than 300 columns. So when i try to replicate it, I'm encountering the
following error Message.
"Error 20068: The article cannot be created on table because it has more than 255 columns."
When a view can be created with more than 255 cokumns, why the problem arises when we replicate
I try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?
Write a select Statement that selects all of the columns for the catalog view that returns information about foreign keys. How many foreign keys are defined in the AP database?
I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted from dbo.fielddata GO
