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

Joining Two Tables - Column To Row

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

Joining Tables With Some Column Names That Are Same

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

Select Distinct Column While Joining Tables

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

Need To Find Instances Of Duplicates Within A Column; Joining 2 Tables.

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

Joining On And Grouping By CASE Function Column Alias (URGENT)

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

SQL Server 2008 :: Joining Two Tables - Split Rows Into Column

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

Conditional On A Derived Column

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

Conditional Display Of A Column

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

Conditional Column Selection

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

New Column With Conditional Values

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

Conditional Column Name On Insert

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

Conditional Column Formatting

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

Conditional Column Select

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

Conditional Column Mapping

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

Inner Join Based On Conditional Column

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

Conditional IF In A Derived Column Transform

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

SSIS Conditional Column Mapping

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

Conditional Color In Matrix Subtotal Column

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

Joining MS SqlServer Data With Oracle Data

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

Joining Summary Data With Detail Data

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

Integration Services :: Conditional Concatenation In Derived Column?

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

Integration Services :: Conditional Split Column Less Than Today

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

SQL Server 2012 :: Data Grouping On 2 Levels But Only Returning Conditional Data

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

Conditional Number Format On Specific Matrix Column Group

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

Derived Column Transformations - DT_STR Operand Cannot Be Used With The Conditional Operation

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

Joining Data Types

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

Best Way Of Joining Tables Data

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

Joining Same Data From Two Different Tables

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

Joining Different Table Data

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

Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating

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

Problem Joining Child Data (JOIN, Subquery, Or Something Else?)

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

SQL Security :: Data Masking Removed After Joining With Other Tables In CTP 2.1?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved