Joining And Conditional Column Data Help
Dec 21, 2006
I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.
employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)
If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!
SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc
View 3 Replies
ADVERTISEMENT
Sep 6, 2014
I have 2 tables to join.
select ID,FirstName,LastName,Gender from tableA
select ID,BabyFirstName,BabyLastName from tableB
how can I put the babyfirstname into the same row under Firstname from tableA after joining?
i tried this but the babyfirstname appear in new column.
select ID,FirstName,LastName,Gender, babyfirstname
from tableA a
join tableB b on a.id=b.id
View 1 Replies
View Related
Dec 13, 2013
If you need to inner join 2 tables that have some columns names that are the same, how can you have those columns be named differently in the query result without aliasing them individually?
Tried select a.*,b.* from tbldm a,tblap b where a.id=b.id hoping the col names in the result would have the a.s and b.s in front of them but they didn't.
View 2 Replies
View Related
Oct 29, 2014
I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.
[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id
from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)
[Code] .....
View 4 Replies
View Related
Aug 22, 2007
My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
View 1 Replies
View Related
Apr 14, 2004
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:
SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2
I really appreciate any help anyone can provide.
Thanks,
DC Ross
View 5 Replies
View Related
Sep 29, 2015
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
View 2 Replies
View Related
Jan 27, 2006
Hi everyone,
I have a quizzing application where users log in, answer questions, and are ranked relative to each other. For this final ranking, I calculate their score using this formula -
score = (correct Qs answered) * 150 / (total Qs answered) + (total Qs answered)
The SQL query that i use to get this info is -
Code:
SELECT TOP 50 username, (sum(correct) * 150 / count(1) + count(1)) AS score, count(1) as totalq
FROM questionsstats
GROUP BY username
ORDER BY score DESC
This works just fine.
However, on top of this I need to put an additional restriction that only users who have at least answered 20 questions be counted in. How can I do this? Adding a simple 'WHERE totalq > 20' does not work. I get the error "Invalid column name 'totalq'.".
Surely there must be a simple way to do this?
Thanks.
View 2 Replies
View Related
Apr 28, 2008
Would like to know if it is possiblefor any of you to suggest how to conditionally diaplay a Column based on the values of the other columns.
Here's my query
SELECT
[ProductionDay]
,DATEPART(wk,StartTime) AS WeekCount
,(AVG([RNTDuration])) AS AvgRNT
,MIN([RNTDuration]) As LeastRNT
,MAX([RNTDuration]) As WorstRNT
,dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) AS BestRNT
,dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) AS BestRNT_txt
,SUM([LoadingDuration]) AS LoadingTime
,[ProgramNo] AS ProgramNo
,COUNT(RNTID) AS PartsCount
,[SubAssemblyNo]
,[Sheetsize]
FROM [RPMS].[dbo].[List_MachineShopRNT]
where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate
Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]
Here I would like to Run the function dbo.fn_MinimumRNT_sp or dbo.fn_MinimumRNT if the ProgramNo is 100
Thank you
View 3 Replies
View Related
Sep 13, 2007
I have one table with 6 columns (Entrance1,Exit1,Entrance2,Exit2,Entrance3,Exit3).
This columns can take null values.
I want to select just the last one of this columns that is not null.
View 11 Replies
View Related
Jan 29, 2008
Does anyone know how to combine to columns into one using sql?
For example
Say I want to create a column3:
column1 _____ column2
------------------------------
primary _____ secondary
primary _____ [Non Applicable]
tertiary _____ quad..something
and IF column1 = primary AND column2 = secondary THEN column3 = newvalue
This is easy is access (and excel) but it doesn't seem to work in the same way in sql.
View 4 Replies
View Related
Jan 21, 2006
I need to dynamic select a column in which insert a vale based on aparameter value, I have this code, but it throws an incorrect syntaxerror.How do I dinamically select a column to insert based on a parameter?Create PROCEDURE dbo.UpdateDetalleOT (@eotId int,)insert into OT (select Casewhen @eotId = 1 THEN OTFechaBorradorwhen @eotId = 2 THEN OTFechaAAsignarend) values ....Best RegardsFabio Cavassinihttp://www.pldsa.com
View 9 Replies
View Related
Jul 19, 2007
Hi Everyone,
I am trying to hide a column in a matrix table. I have no trouble using conditional formatting to control the visibility of the column, but when I hide the column the main row in the matrix does not shrink. Therefore, I have a big gap€¦ My thought was to use conditional formatting to control the column width of the main matrix row. Listed below is the expression that I used. Can anyone help me with the error message or recommend a better solution?
=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be parsed as a unit because it does not contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
Regards,
A.Akin
View 1 Replies
View Related
Aug 9, 2006
Is there a statement to change the column in a select clause?
For example:
select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.
View 3 Replies
View Related
May 25, 2007
Hey all! I have a bunch of questions, but let's start with this one:
Incoming from my flat file, I have two columns:
employee_id
dept_id
These indicate who did the work, and for which department (people can work for more than one department). In my destination table, I have the following two columns:
employee_id_sales
employee_id_wrhs
I want to map the employee id either to employee_id_sales or employee_id_wrhs, depending on the dept_id from the flat file.
How do I specify conditional column mapping?
I'm really new to SSIS, so I might be missing something obvious.
Thanks!
-- Jim
View 3 Replies
View Related
Mar 5, 2014
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
View 7 Replies
View Related
Apr 12, 2006
HI, I was wondering if there is a possibility to use a confitional if like this:
IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)
into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.
Thank you,
Ccote
View 3 Replies
View Related
Aug 3, 2007
I have a condition where if column5 is equal to 1 then put column6 into the destination column "dest6", if it is not equal to 1 then put column6 in destination column "dest7"
What is the best way to do this in SSIS?
If I have to use the conditional split then do I have to copy my complete mappings, exact change this one column?
Thank for the help this mapping will take me a long time!
View 5 Replies
View Related
Jun 6, 2006
I am using matrix in my report with calculated fields, for example I have 3 columns : Actual amount, Budget and Variance. Variance should be in Red if it is negative. I can set up an expression to change the color on the row level, but not in Subtotal ot Total row. I cannot use sum of Fields! values, because Budget amount is also calculated field based on the "Category" value which is columns group on the matrix.
So anyway, if I am trying to reference ReportItems!Variance.Value in Subtotal Level, it gives me an error about group scope.
How can I access the cells values in subtotal and total group levels? If anybody knows any tricks for this fairly simple task?
Thanks for your help.
Olga
View 15 Replies
View Related
Nov 7, 2007
OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed.
SqlServerGroup Name ID# Item ConditionAAA123 1 a 1AAA123 2 a 1AAA123 3 a 1AAA123 4 a 2AAA123 5 a 2AAA123 1 b 3AAA123 2 b 4AAA123 3 b 3AAA123 4 b 4AAA123 5 b 3BBB123 1 a 1BBB123 2 a 1BBB123 3 a 2BBB123 4 a 2BBB123 5 a 2
OracleGroup Name ID# ValueAAA123 1 50%AAA123 2 55%AAA123 3 60%AAA123 4 80%AAA123 5 70%BBB123 1 35%BBB123 2 45%BBB123 3 50%BBB123 4 50%BBB123 5 80%
I need to be able to get this:Group Name Item Condition ValueAAA123 a 1 55%AAA123 a 2 75%AAA123 b 3 60%AAA123 b 4 67.5%BBB123 a 1 40%BBB123 a 2 60%
Any idea how I can get the data from these two DBs to talk to each other? Thanks.
View 6 Replies
View Related
Mar 19, 2007
I know similar questions have been asked but I wanted to try my luck that my issue is somehow different.
I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all.
Any one have ideas?
View 1 Replies
View Related
Aug 3, 2015
I have 10 columns i.e from Segment1 to Segment10. I need to concatenate it with ".". All 10 segments can be null. If any of the segment is null i do not want to show ".". This is the expression I am using
 (DT_STR,100,1252)((ISNULL(SEGMENT1) ? "" : "." + SEGMENT1) + (ISNULL(SEGMENT2) ? "" : "." + SEGMENT2) + (ISNULL(SEGMENT3) ? "" : "." + SEGMENT3) + (ISNULL(SEGMENT4)
? "" : "." + SEGMENT4) + (ISNULL(SEGMENT5) ? "" : "." + SEGMENT5) + (ISNULL(SEGMENT6) ? "" : "." + SEGMENT6) + (ISNULL(SEGMENT7) ? "" : "." + SEGMENT7)
+ (ISNULL(SEGMENT8) ? "" : "." + SEGMENT8) + (ISNULL(SEGMENT9) ? "" : "." + SEGMENT9) + (ISNULL(SEGMENT10) ? "" : SEGMENT10))
But if any of the column contains Null or Blank I get ...... I do not want to show these .... if any segment is null.
What mistake am I doing in the above expression?
View 4 Replies
View Related
Oct 29, 2015
I'm trying to write a conditional split where I want to bring in only records where the date is less than today, but my problem is that I can't simply do this Column < GetDate() because if something comes in today, it takes the time into account and it will bring that record for today. You can do this in SQL, but I'm not sure how to do that in SSIS
SELECTCAST(CONVERT(char(8),GetDate(),112)ASdatetime)
View 2 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related
Feb 6, 2008
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
View 6 Replies
View Related
Sep 16, 2006
Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.
DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?
Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?
FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
View 4 Replies
View Related
Jun 6, 2008
Can anyone show me how to join 2 different data types in a SQL query. For example, I want to join two tables.
Table 1:
- Field 1 = ID (Varchar,10)
- Field 2 = Balance
Table 2:
- Field 1 = SSN (Numeric, 9)
Field ID should be = to SSN, but the data type is different. PLease help me to join them.
Thanks,
View 7 Replies
View Related
Oct 17, 2007
Hi,
I have multiple table with the same schema as follows
Table1
ID Date
---------------
1 D1
2 D2
3 D3
4 D4
Table2
ID Date
---------------
2 D21
3 D31
4 D41
.....
I just want to build a mater table out of these existing tables . I need a query that builds as faster as possioble as these tables might contains millions of records
The final Table Should be as follows
ID CatName Date
------------------------------
1 Name1 D1
2 Name2 D2
2 Name21 D21
3 Name3 D3
3 Name31 D31
4 Name4 D4
4 Name41 D41
Can any one provide me with the best performace query that results the above output
With Best Regards,
~Mohan
View 2 Replies
View Related
Oct 8, 2007
Hello,
I am using Visual studio 2005 and have such a problem. I have a report that takes most of its date from €śordersview€? that is linked to lots of other fields. The final report has fields such as product name, number, Date, quantity and actual quantity etc. This works fine, but the program that I get the data from can€™t keep all the data in itself, because it slows the time of its work enormously. So we made it so that all the data of orders that are completed ant older that 2 days would be copied to another table €śOrdersArchive€? and deleted from €śOrders€? table.
Now I need to make a report that takes data from €śordersArchive€? if its older that two days, and from €śorders€? if it is new. The fields are the same in both cases and I would like the data to appear in the same table so it could be grouped, sorted and evaluated by the same criteria. Is it even possible and if it is how should I do it?
For now I only managed to create 2 separate tables in the same report that take date from different datasets.
Regards
Darius
View 4 Replies
View Related
May 30, 2008
Dear all,
I have four different tables with three columns. The first two columns are equal for all tables. The third column is different:
Table I
COUNTRY | PRODUCT | SALES VALUE
AAA AAA 10
AAA AAB 10
Table II
COUNTRY | PRODUCT | SALES COST
AAA AAC 10
Table III
COUNTRY | PRODUCT | SALES MARGIN
AAB AAA 10
Table IV
COUNTRY | PRODUCT | SALES XXX
AAA AAA 5
What I would like to accomplish is to obtain a table where the records from these tables would be joined in a single table:
COUNTRY | PRODUCT | SALES VALUE | SALES COST | SALES MARGIN | SALES XXX
AAA AAA 10 0 0 5
AAA AAB 10 0 0 0
AAA AAC 0 10 0 0
AAB AAA 0 0 10 0
Can you advise me on what the best way to achieve this is? Thanks!
Kind regards,
Pedro Martins
View 4 Replies
View Related
Aug 2, 2007
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
View 1 Replies
View Related
Jun 21, 2006
The problem:I'm updating a report to be "multi-language" capable. Previously,any items that had text associated with them were unconditionallypulling in the English text. The database has always been capable ofstoring multiple languages for an item, however.Desired output:Given the test data below, I'd like to get the following resultsselect * from mytestfunc(1)Item_Id, Condition, QuestionText1876, NOfKids <= 10, This many children is unlikely.select * from mytestfunc(2)CheckID, Condition, QuestionText1876, NOfKids <= 10, NULLThe current SQL for my UDF:CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,tblQuestionText.QuestionTextFROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =tblQuestionText.Item_ID)WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR(tblQuestionText.LanguageReference IS NULL)))Test data:CREATE TABLE [dbo].[tblCheck] ([Item_ID] [int] NOT NULL ,[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblCheck] ADDCONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED([Item_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblLanguage] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Priority] [int] NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Spoken] [bit] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADDCONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] ADDCONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR[CreationDate],UNIQUE NONCLUSTERED([Priority]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblQuestionText] ([Item_ID] [int] NOT NULL ,[LanguageReference] [int] NOT NULL ,[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SameAs] [int] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblQuestionText] ADDCONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED([Item_ID],[LanguageReference]) WITH FILLFACTOR = 90 ON [PRIMARY]GOINSERT INTO tblCheck (Item_ID, CheckDescr)VALUES(1876, 'NOfKids <= 10')INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(1,1,'English', 1)INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(2,2,'Espanol', 1)INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)VALUES (1876, 1, 'This many children is unlikely.')Any tips or pointers will be appreciated. Thanks.
View 2 Replies
View Related
Jul 6, 2015
I find that dynamic masking does not work on joining tables in SQL Server 2016 CTP2.1.
For examples, I create the following table:
CREATE TABLE [dbo].[HRM_StaffAppointment](
[StaffID] [nvarchar](11) NOT NULL,
[ApptSeqNo] [smallint] NOT NULL,
[ReportingDept] [nvarchar](10) NULL,
[Code] ...
Then I apply mask on StaffID and RankDesc.
alter [dbo].[HRM_StaffAppointment] alter column [StaffID] add masked with (function='default()')
alter [dbo].[HRM_StaffAppointment] alter column [RankDesc]
add masked with (function='default()')
When User A logged in and query on HRM_StaffAppointment, StaffID and RankDesc are perfectly masked. But User A can remove the masking using another table:
CREATE TABLE [dbo].[staffID](
[staffID] [nvarchar](255)Â
) ON [PRIMARY]
select a.*
from dbo.HRM_StaffAppointment as a
left join dbo.staffID as b
on a.StaffID = b.StaffID
It looks like a security hole to me, or I'm doing anything wrong?
View 4 Replies
View Related