JOIN Filters And Non-numeric Keys
Jul 26, 2006
I expect that this is not possible but:
I've created a initial filter on our tblLocation.
I've created a join filter on the header table for our inventory data.
I've create a join filter on the header table for our inventory detail data.
The joins between the inventory header table and the inventory details table is on a three field key which are of type nvarchar.
When I try to sync a subscription, I receive an error which indicates that it cannot bulk copy into the inventory details table on the subscriber because it would create duplicate keys. There isn't any duplication at the publishing server so, I expect this has something to do with the data type of the key/join fields.
Am I correct? If so, is there a workaround.
I essentially only want to send that inventory data to a subscriber that the subscriber needs.
View 1 Replies
ADVERTISEMENT
Oct 2, 2006
I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.
The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.
When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.
What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.
The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.
Am I seeing this because I said my partitions will overlap when I created the table articles?
Thanks for any help
Graham
View 2 Replies
View Related
Sep 12, 2014
I need to find;
Card number and date of borrowers earliest loan for all borrowers who had a loan before the 03/Jan/2004 OR who borrowed a book published before 1920
So far my query looks like this but it is bringing back date out after 03/Jan
select cardno, min(l.dateout)
from loan l right outer join book b
on l.isbn = b.isbn
where b.yearpublished < '1920'
or l.dateout < '03/Jan/2004'
group by cardno
order by cardno;
View 4 Replies
View Related
Apr 17, 2007
I have a database column that stores a comma delimited list of foreignkeys. Would someone show me how to do a join using the values from alist stored within a record?For example, a record in tbl_cds.genre_id might have a value of "2,5, 6" corresponding to genre_ids 2 , 5 and 6. I want to jointbl_cds.genre_id to tbl_genre.genre_id using the values in that datafield.It seems I need a loop like this:SELECT * FROM tbl_cdsWHEREBegin Looptbl_cds.genre_id[i] = tbl_genre.genre_idEnd Loop.Would someone give me the correct syntax?Is there an alternative method that would create less overhead?Sorry for such a novice post.
View 2 Replies
View Related
Sep 13, 2015
I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.
I have a table called Patient. It's primary key is pat_id.
I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.
I have a third table called Coverage_History. It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.
I want to get the pat_id and all the coverage information that is current. The coverage table contains specific insurance policy information. The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:
Patient (pat_id and lname)
P123 Monto
P124 Minto
P125 Dento
P126 Donto
Coverage (pat_id, coverage_plan_id, hosp_status, policy_num)
P123 MED1 OP A1499
P123 ACT4 OP H39B
P124 MED1 OP C90009
P124 RAC OP 99KKKK
P124 RAC OP 99KKKK
P124 MED1 OP C90009
P125 ARP OP G190
P126 BCB OP H88
Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to)
P123 OP 1 20150102 MED1 NULL
P123 OP 2 20150102 ACT4 NULL
P124 OP 1 20150203 RAC 20150430
P124 OP 2 20150203 MED1 20150430
P124 OP 1 20150501 MED1 NULL
P124 OP 2 20150501 RAC NULL
P125 OP 1 20150801 ARP NULL
P126 OP 1 20150801 BCB 20160101
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority,
from patient p
left join
( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and
(ch.effective_to is NULL or ch.effective_to >= getdate()
)
) on ch.patient_id = p.patient_id
where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
So I want to see:
P123 Monto MED1 OP 20150102 NULL 1
P123 Monto ACT4 OP 20150102 NULL 2
P124 Minto MED1 OP 20150501 NULL 1
P124 Minto RAC OP 20150501 NULL 2
P125 Dento ARP OP 20150801 NULL 1
P126 Donto BCB OP 20150801 20160101 1
View 6 Replies
View Related
Jun 10, 2014
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
View 2 Replies
View Related
Mar 21, 2006
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
View 6 Replies
View Related
Oct 24, 2007
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
View 18 Replies
View Related
Jul 20, 2006
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
View 1 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
May 16, 2008
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
View 2 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Nov 22, 2007
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
View 6 Replies
View Related
Aug 18, 2006
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
View 1 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
May 7, 2008
Hi all,
I'm using SSAS 2005 with Excel 2007. My data store is very poor at the moment and most of the tables for the dimensions get rebuilt every night. The current result is as follows;
User selects product "Issue May 2008" as a filter from the product dimension, saves and closes the report.
User opens the report after the data has refreshed, the data is different, the filter shows Issue May 2008 but when you click on it the item above it is displayed - this is because the IDs have changed in the source table.
Is there a way to make it so the text/description of the dimension is used rather than the ID?
In my head that explanation makes sense, but I can understand how it might not so please let me know if it doesn't
Regards,
Phil
View 5 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Jun 21, 2007
I was wondering if it's possible to set filters per user. For example we have a client who has offices in 10 different states, we want an admin of an office to only be able to run reports on their state. Is this possible without creating 10 different models/views?
Thanks
View 8 Replies
View Related
Nov 18, 2007
Hello.
Is there a good article on the net for working with filters?
Also, I have a report with 10 parameters.
Each parameter get his default values from a dataset.
The problem is that the report ake a lot of time to load since it need to run all 10 datasets to load the parameters values.
Is there a way to speed things up?
Thanks.
View 1 Replies
View Related
Nov 10, 2006
Hi,
I am creating report using reporting service and datasource is SQL Server 2005 Cube.
I am using query builder and i am creating query parameters in the query builder but when i run the report is showing the filters but data is not filtering based on which i selected from the list.
How do i create filters?? Please help out on this.
Thanks
View 4 Replies
View Related
Nov 27, 2007
Hi.
Is it possible to create a report via SSRS and deilver it via the subscription service in an email such that when user opens their own report (filtered by their user id) they can then dynamically change filters (cbo boxes) a la an excel pivot table which will then refresh the data in the report for them?
I can't seem to find a way to do this. Drill-down is not what is wanted...they want to simply cheange selections in cbo boxes and have the data all refresh.
Thanks!
View 4 Replies
View Related
Nov 14, 2007
I have a report generated through a number of recursive CETs and would like to add a FILTER parameter for the user to select on. (Using Parameters in the query will not work because recursion cannot be limited at the outset by the parameter).
The values to be selectable are:
Sprint 1 - R 1.0
Sprint 2 - R 1.0
Sprint 3 - R 1.0
Sprint 4 - R 2.0
Sprint 5 - R 2.0
Sprint 6 - R 2.0
...etc
I want the report to display only those Releases (R n.0) which the user specifies, eg:
Release 2 would select all data with R 2.0 in the field.
Using Table properties-Filters for the field works IF I use "=", as:
Expression: Operator: Value: An....
=Fields!SprintName.Value = Sprint 4 - R 2.0 or
=Fields!SprintName.Value = Sprint 5 - R 2.0 or
=Fields!SprintName.Value = Sprint 6 - R 2.0
Issues:
1. I am unable to use Like (I could specify Like '%- R 2.0%' and have all Release 2.0 displayed), but it does not work with any of the syntax I have tried. Using only a single expression row, - R 2.0, '%- R 2.0%', "%- R 2.0%" all fail by displaying NO data. CAN Like be used? And what is the syntax?
2. I do not have any control over the And/Or column -- it is always greyed-out, and inserts "or" automatically if the Operation is "=", and "and" if the operation is "Like". How do I control this paramter?
3. And, most important, how do I get user input to the filter field, such that If I enter Release 2 from a drop down (?), I can display the appropirate data?
NB: VS2005 sp1/SQL2005 sp2
Assistance greatly appreciated....
View 4 Replies
View Related
Oct 6, 2006
Dear ppl,
I am using Merge Replication between SQL Server 2005 (Publisher) and Pocket PC (Subscriber). I have a .NET compact framework appliction on the Pocket PC that replicates data from the server.
I am using SqlCeReplication class on the Pocket PC application to synchronise the data. By default, when i call the Syncrhonise() method, it pulls all the data from the server. What I want is to pass a parameter from the Pocket PC and filter the data based on that paramter. E.g. from the Employee table, I want only those Employee that belongs to a CompanyID that I pass as a parameter.
Is there a way to do this, so that i can pass parameters from my PDA application (Windows Mobile), and make the Filters specified in the Publication to use that parameter to filter out the rows.
Regards
Nabeel Farid
View 1 Replies
View Related
Dec 16, 2007
Hi,
Is there a way to create a new mining model from an existing one but with a different filter? I can see that the SELECT INTO DMX statement perform the creation. Is there a syntax for DMX in SQL Server 2008 for setting the filter for the new model?
thanks,
Gustavo Frederico
View 4 Replies
View Related
Apr 18, 2008
Hi, I need help.
I have 4 filters/parameters on top of the report but it looks very untidy.
Is there a way where i can set the boxes sizes & alignment etc.
I would like to make it look more nicer.
Regards,
View 2 Replies
View Related
Oct 20, 2006
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
Thanks,
Patrick Kafka
View 3 Replies
View Related
Jan 9, 2007
Greetings,
I have a fairly simple report that displays one table (table1). The table
has a filter that I created by opening the properties of the table and
entering
=Parameters!abs_comparison.Value
in the Value field of the Filters tab. The filter operates on the
=Fields!Absolute_Comparison.Value
field.
The report displays the row count from the dataset (=CountRows("mfrep_rqw"),
but this value doesn't change when the filter is applied. I'd also like to
display the number of filtered rows, but I can't figure out how to do it.
Any ideas?
Thanks,
Mike Hayes
View 6 Replies
View Related
Aug 10, 2007
Hi,
I have applied two filters for table in the SSRS report, one is StartDate and another is EndDate. But I was unable to choose the Boolean operator (And/Or) to use to combine this expression.
I came to know that the Boolean operator cell activates after we begin to enter an expression in the next row. But by default it is set as "And" operator. Now i want to cahnge that to "Or" operator it is in disable. Is there any way to enable this cell to choose the operator from the cell.
Any help would be appreciated.
Thanks
Dinesh
View 14 Replies
View Related
May 9, 2007
I am converting a crystal report to ssrs 2005.
I would like to know what the best method of handling a rather large record selection task.
Here is the crystal version.
if {?WhseRegion} = 'Western' then {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN']
else if {?WhseRegion} = 'Eastern' then {R_ShippingLog;1.InvUnitSite} in ['AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']
else {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN','AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']
What I trying to figure out is how to incorporate the above in the dataset filter area on the datagrid.
any ideas?
View 9 Replies
View Related
Oct 15, 2007
I have created a report showing both first name and last names with the following filters. Both of them are prompted parametres
Last Name Contains ------------------
First Name Contains ------------------
When users enters both then we get the results back. I want the first name to be optional such that if user enters something for the first name then take it into the search criteria else show all the first names which matches the last name criteria.
Example: Last Name = "SMITH", First Name = (user has not entered and NULL check box is checked). I don't get any results since this filter translates to find all the records which has the last name SMITH and First Name IS NULL),
Instead I want this to be select all the records which has the last name SMITH and First Name <> EMPTY. Is there a way I can do this using the fiter magic?
Thanks.
View 2 Replies
View Related
Mar 23, 2008
I am developing a summary report that will have multiple tables, charts and matrixes, all using the same set of data. However, one table may only show one month of data while another will show three months. If they all use the same Dataset with the same data parameters, is the data only pulled once? And then each component can use Filters to further refine the data? If this is true, this would seem to be the best option.
Or does each report component execute the query independantly?
Before I get too far down the road developing this report, I'd like to know the best way to do it from the beginning.
Thank you for your input.
Rob
View 3 Replies
View Related
Oct 17, 2007
I have created a report showing both first name and last names with the following filters. Both of them are prompted parametres
Last Name Contains ------------------
First Name Contains ------------------
When users enters both then we get the results back. I want the first name to be optional such that if user enters something for the first name then take it into the search criteria else show all the first names which matches the last name criteria.
Example: Last Name = "SMITH", First Name = (user has not entered and NULL check box is checked). I don't get any results since this filter translates to find all the records which has the last name SMITH and First Name IS NULL),
Instead I want this to be select all the records which has the last name SMITH and First Name <> EMPTY. Is there a way I can do this using the fiter magic?
Thanks.
View 2 Replies
View Related
