I am having trouble getting my head round participation for some scenarios. Consider the following:
River MUST flow through 1 or more cities.
City MUST have at least 1 river.
This is represented the pk of River being put as a fk in city AND (to represent that city must have a river), the foreign key does NOT allow nulls.
So how about...
River MAY flow through 0,1 or more cities. City MAY have ONLY 1 river.
This time we allow the foreign key in city to have NULLs, but how about the otherside, the pk of river. How do I represent the MAY here, and a pk value cannot be null.
I have a reporting database just for reports. There are SSIS jobs which downloads data from Transaction database to reporting database. I have 5 tables in reporting database with no primary key hence no clustered index. Is it really mandatory to have primary key.
Example A master table say EmployeeMaster with columns PK_EmpID, EmpName and with clustered index on column PK_EmpID
There is other table say EmployeeAddress with columns FK_EmpID, City with no clustered index but non-clustered index on column FK_EmpID which is a foreign key reference to EmployeeMaster.PK_EmpID
In my report SQL queries when I need to SELECT data from EmployeeAddress, I always join EmployeeMaster with EmployeeAddress on EmployeeAddress.FK_EmpID = EmployeeMaster.PK_EmpID.
My question, do I really need a Primary key on table EmployeeAddress though no where I am using it? I am looking for better option from performance point of view. In the tables there will be upto 1 lakh records.
I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:
Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.
One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table CaseID Patient (Sample data: 101,SallyDoe 102,JohnDoe)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases) CaseID CrnaID TimeIn TimeOut (Sample data: 102,Jack,0800,1030 102,Bart,1030,1130 102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
Can someone tell me what this Where clause (from MS Access) should be T-SQL?
Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.
Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1
My question is on an appropriate approach to take.
We have an very large company database that supports all of current software in Sql Server. A current requirement is to take data from a Oracle Database that has "agreed upon" tables and to "Translate" that to SQL Server and place them in agreed upon "Temp" tables. Then our other software will know what to do with these temp tables and do their thing.
Upon launching this translator application, it will delete all of those temp tables and do (what I just specified above).
My initial thought was to dump each Oracle table into a DataTable. Then delete any temp tables in our database. Then create the temp tables and begin updating our Sql Server by iterating through the DataTable performing updates?
I am wondering if someone could give some advice about how I could translate the following query in MS Access into T-SQL. Any help is greatly appraciated.
Gary writes "I'm imported the query from Access (where it worked perfectly) to SQL View where the "Iif" statements caused an issue.
Here's a SMALL portion of the original: SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, [TransBuyerPropSalePrice]* (IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA], IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, ......
Below, are my changes so far, but I'm stuck. SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA,
[TransBuyerPropSalePrice]* If([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA], If([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA]/100)) AS VAFunding, ........
It appears that I may not be able to put an IF into a SQL SELECT. If not, how can I get the same effect ?
Thanks. Gary
Listed Below is the Full Access Query:
SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, tblTransactionBuyer.TransBuyerEntryDate, qryUserAlphaList.UserName, qryUserAlphaList.UserAreaCode, qryUserAlphaList.UserPhone, tblTransactionBuyer.TransBuyerName, tblTransactionBuyer.TransBuyerVaEligible, tblTransactionBuyer.TransBuyerPropAddress, tblTransactionBuyer.TransBuyerPropCity, tblTransactionBuyer.TransBuyerPropSalePrice, ([TransBuyerPropSalePrice]*([DefBasePercentFHA])/100) AS LoanBaseFHA, ([TransBuyerPropSalePrice]*([DefBasePercentCONV])/100) AS LoanBaseCONV, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, ([TransBuyerPropSalePrice]*([DefBasePercentJumbo])/100) AS LoanBaseJumbo, [LoanBaseFHA]*[DefMipFHA]/100 AS MipFHA, [LoanBaseCONV]*[DefMipCONV]/100 AS MipCONV, [LoanBaseJumbo]*[DefMipJumbo]/100 AS MipJumbo, [TransBuyerPropSalePrice]*(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, [LoanBaseFHA]-[DownOverMinFHA]+[MipFHA] AS LoanTotalFHA, [LoanBaseCONV]-[DownOverMinCONV]+[MipCONV] AS LoanTotalCONV, [LoanBaseJumbo]+[MipJumbo]-[DownPaymentJumbo] AS LoanTotalJumbo, [LoanBaseVA]+[VAFunding]-[DownPaymentVA] AS LoanTotalVA, [TransBuyerPropSalePrice]*([DefMinDownPayFHA]/100) AS MinDownFHA, [TransBuyerPropSalePrice]*[DefMinDownPayCONV]/100 AS MinDownCONV, [TransBuyerPropSalePrice]*[DefMinDownPayVA]/100 AS MinDownVA, [TransBuyerPropSalePrice]*[DefMinDownPayJumbo]/100 AS MinDownJumbo, tblTransactionBuyer.TransBuyerIntRate, tblTransactionBuyer.TransBuyerLoanYears, tblTransactionBuyer.TransBuyerHazardIns, tblTransactionBuyer.TransBuyerFloodIns, tblTransactionBuyer.TransBuyerClosingDate, IIf(([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice]>[TransBuyerConvDownPayCash],([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice],[TransBuyerConvDownPayCash]) AS ConvDownPay, tblDefaults.DefBuyAppraisal, tblDefaults.DefBuyCreditReport, tblDefaults.DefBuyJumboUnderwriting, tblDefaults.DefBuyVAUnderwriting, tblDefaults.DefBuyFHAUnderwriting, tblDefaults.DefBuyCONVUnderwriting, tblDefaults.DefBuyJumboTaxService, tblDefaults.DefBuyVATaxService, tblDefaults.DefBuyFHATaxService, tblDefaults.DefBuyCONVTaxService, tblDefaults.DefBuyFloodCert, tblDefaults.DefBuyAbstractTitle, tblDefaults.DefBuyMiscFedex, tblDefaults.DefBuyRecording, tblDefaults.DefBuySurvey, tblDefaults.DefBuyAttorney, tblDefaults.DefBasePercentFHA, tblDefaults.DefBasePercentCONV, tblDefaults.DefBasePercentJumbo, tblDefaults.DefBasePercentVA, [TransBuyerPropSalePrice]*[DefPropTaxRate] AS PropTax, [LoanBaseFHA]*[DefBuyOrigFHA]/100 AS OrigFHA, [LoanBaseCONV]*[DefBuyOrigCONV]/100 AS OrigCONV, [LoanBaseVA]*[DefBuyOrigVA]/100 AS OrigVA, [LoanBaseJumbo]*[DefBuyOrigJumbo]/100 AS OrigJumbo, [DefTitleInsDollar]*([TransBuyerPropSalePrice]/[DefTitleInsPerX]) AS TitleInsurance, [OrigFHA]+[DefBuyAppraisal
I have a project that consists of modifying a report in Crystal. The problem is that I know very little about SQL and I need to add some data items to Crystal using SQL. The guide that I was given refers to Enterprise Manager and SQL Query Analyzer but they have since upgraded to SQL Server Management Studio. Can anyone translate the following statement into SQL Server Management Studio so that I can complete this process?
Best regards,
We will now edit the View on the MS SQL server. Open Enterprise Manager and find the cst_adEnroll_MyInitials _vw in the Views section of the database. Right-click, select Copy, then go to the menu bar and Select Tools and SQL Query Analyzer. Once Query Analyzer opens: Change CREATE to ALTER before the view name.
Hi, how can i make optional relationship? for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6. column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null) how can i set the relationship? because one of the columns data is null each time, error always occurs.
I have a select proc that will take a bunch or criteria parameters. Based on how many are not null I would like to decide how many inner joins to do. for example:
select H1.Priceid as HotelPriceId,H2.Priceid as AirPriceId, H1.VendorPackageId from ( select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = 'SBT' and Sitecode = 'system' and PackageType = 'AHCF' )HB on HA.VendorPackageId = HB.VendorPackageId and ( CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = 'LAS' and LengthOfStay = 5 and Ages = 'A2' and ComponentType = @ComponentType_1 and ValidItemType = @ValidItemType_1 and ItemValue = @ItemValue_1 ) )
)H1 INNER JOIN ( select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = 'SBT' and Sitecode = 'system' and PackageType = 'AHCF' )HB on HA.VendorPackageId = HB.VendorPackageId and ( CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = 'LAS' and LengthOfStay = 5 and Ages = 'A2' and ComponentType = @ComponentType_2 and ValidItemType = @ValidItemType_2 and ItemValue = @ItemValue_2 ) ) )H2 on H1.Priceid = H2.priceId Inner Join ( select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = 'SBT' and Sitecode = 'system' and PackageType = 'AHCF' )HB on HA.VendorPackageId = HB.VendorPackageId and ( CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = 'LAS' and LengthOfStay = 5 and Ages = 'A2' and ComponentType = @ComponentType_3 and ValidItemType = @ValidItemType_3 and ItemValue = @ItemValue_3 ) ) )H3 on H2.Priceid = H3.priceId
if values are only passed in from @ComponentType_1,@ValidItemType_1,@ItemValue_1 I dont want to do any inner joins.
If its passed in for @ComponentType_1,@ValidItemType_1,@ItemValue_1 & @ComponentType_2,@ValidItemType_2,@ItemValue_2 I want to do the first Inner Join.
and of course if I get all 3 sets of criteria I want to do both the inner joins. I know I can cut and past this thing 3 times with an if statement but that isn't going to be practical.
Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?
AS SELECT * FROM myTables with joint WHERE DepartureDate = @DepartureDate AND ReturnDate = @ReturnDate AND MaxPrice = @MaxPrice AND Country = @Country
If a parameter is null, I dont want it to be in the WHERE clause. I dont want to use a string parameter and execute a "string" select. I dont want to use a combination of IF to check everything ...
Is there an option in a stored procedure whereby a parameter can be flagged as optional? I have a stored procedure with 2 parameters, Product and Date, and I would like to be able to just pass the Product, or pass Product and Date from an Access project. Is this possible?
Hello, Is it possible to define optional parameters for a stored procedure? What I want is to create a search with about 8 parameters, but most users will only use 3 or 4, so It would be nice If I could only provide the used parameters in my code. And have sql give the unused parameters a default value (possibly %) thx.
I have a package in DTS that I am tryinig to work with. We have a few queries that will export the same exact table to a text file. I am trying to make it so we can pull a previously created text file from a central "CommonFile" folder. In this package I have a Execute Package Task. This second package is only for creating this common txt file. I want this step to only run if the file is non-existant (i.e. someone deleted/moved it by accident) or if it is over a week old (the database we pull it from it updated on sundays). I can run it so the comment txt file get created first thing on monday, but I still want a failsafe in there incase the file is deleted/moved or the package to create it fails.
I have an ActiveX script set up which checks if the file exists and if it does then checks to see how old it is Then from there I would like to jump to a different area of my package:
Code Snippet
Dim pkg Dim stpbegin
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(".txt") = TRUE THEN msgbox("COPY") Set objFile = objFSO.GetFile(".txt")
Dim today Dim thisweek
today = date() thisweek = (Weekday(date())-2)
IF thisweek < 0 THEN thisweek = 6 'Sunday Weekday() = 1 so this will put it at the end of the week.
IF (date() - thisweek) <= objFile.DateLastModified THEN msgbox("COPY") set pkg = DTSGlobalVariables.Parent set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_4") stpbegin.ExecutionStatus = DTSStepExecStat_Waiting ELSE msgbox("CREATE") set pkg = DTSGlobalVariables.Parent set stpbegin = pkg.Steps("DTSStep_DTSExecutePackageTask_1") stpbegin.ExecutionStatus = DTSStepExecStat_Waiting END IF
ELSE msgbox("CREATE") set pkg = DTSGlobalVariables.Parent set stpbegin = pkg.Steps("DTSStep_DTSExecutePackageTask_1") stpbegin.ExecutionStatus = DTSStepExecStat_Waiting END IF
Main = DTSTaskExecResult_Success End Function
The problem I am having is despite the ActiveX script the package will run the execute package task no matter what (i dont see a way to post pics so I will try to draw bellow). I think this is due to me having a success between the ActiveX and the EPT. This is the only way I know of how to step this up. If i do a completion it will also do the EPT and if I do a failure it won't do anything at all after it.
So basically I want this pagage to jump over the DTSExecutePackageTask_1 if the file checks out as okay. Does anyone have a suggestion of how I can set up this package?
I'm using an application that is generating some SQL scripts for SQL Server 2005. I'm trying tweak it so that I can run it on SQL Server 2000.
The line that I'm having trouble with is:
CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional)
The key word "(optional)" is causing trouble. I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected.
What is the alternative syntax to use in SQL Server 2000?
Hi, I'm having a problem trying to do optional items in where clause. I'd appreciate some help, heres the code.
select DFD.Col1 as 'Col1',CMD.Col2 as 'Col2' from Control as DFD,MetaData as CMD,Columns as DC,Tables as DT where DFD.GroupID in ({0}) and
(CMD.MetaDataId = DFD.PrimaryID -- or CMD.MetaDataId = DFD.ForeignID -- These are the problem 3 lines or CMD.MetaDataId=DFD.HumanColumn) --
and DFD.Disabled='N' and CMD.Disabled='N' and DC.ColumnsId=CMD.ColumnId and DC.Disabled='N' and DT.TablesId=DC.TableID and DT.Disabled='N'
-- For the problem rows I need to be able to optionally search if they're = to MetaDataID. Right now this query will return only rows that have metadataid = PrimaryID or Foreign, Human. I need to be able to return all rows with the optional dependancies.
Is it possible to set up a one to one relationship between two tables, where one side of the relationship is optional? For example, I have two tables "Applicant", and "Client":A client will always have one applicant
I need to use a parameter in a stored procedure that is optional and if it is optional I need all records returned, even if there is an entry in the field that the parameter is appllied to. Is this possible? I know I can assign a default value. but the value in the field could be one of many choices. I want to be able to specify a choice and have only those records returned or leave it blank and return all records.
I've been away for a while but now that I'm back one if the first things I noticed is how many of the problems on this forum could have been easily avoided by simply normalizing the database in question.
For those of you without formal SQL training, know this: You have to normalize your database. This is part of the process of database development. You can not just whack together a few tables in whatever way is the easiest for the problem at hand, because eventually your requirements will expand and then you need your data to be flexible.
Seriously, in the last 3 weeks I've seen many many questions where it is obvious that the problem is a flawed DB design, but all the so-called SQL gurus here will just answer the question without addressing the fact that the problem lies much deeper.
Here's a link I found on Google: http://www.cse.unsw.edu.au/~cs3710/PMmaterials/Tutorials/normalise.htm
I have a web page I want to run a stored procedure from. In the web page I have three drop downs Business Area, Business Unit and Reporting PeriodThe drop downs determine whether all projects are returned or all projects for a certain business unit / business area or month.This means I have to tailor my sql statement accordingly. What I want to know is can I append sections of a sql statement ie add or subtract more where clauses depending on the values pulled in from the web page.At the moment I have only accounted for 2 variables and that has caused me to create 4 IF statements depending on the values. 3 variables would cause even more IF statements and multiple combinations which I am trying to avoid.
I need some help... I'm trying to execute a stored procedure and I'm getting this message
Run-Time Error '-2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented
Here is the code: Public Function D2L(sconnect As Variant, dDate As Variant) As Variant Dim rsDate As ADODB.Recordset Dim cmdDate As ADODB.Command Dim prmDate As ADODB.Parameter
Set cmdDate = New ADODB.Command Set ADOConn = New ADODB.Connection
ADOConn.Open sconnect
Set cmdDate.ActiveConnection = ADOConn cmdDate.CommandText = "dbo.UP_CVRT_DATE_TO_LONG" cmdDate.CommandType = adCmdStoredProc
Set prmDate = New ADODB.Parameter prmDate.Type = adDate 'prmDate.Size = 32 prmDate.Direction = adParamInput prmDate.Value = dDate cmdDate.Parameters.Append prmDate
Hello guys, I want to implement a searching with a stored procedure. As an example I have a table like > tableA ; Id,Name,Surname,Gender(bit) 0 -Male , 1 - Female
If i want to choose all records that are male, I could simply have a query like this : SELECT * FROM tableA WHERE Gender = 0 and vice versa for female. If i want to choose all rows in the table i should omit where clause. The problem is , when i use a stored procedure i should send a parameter. And i dont know if there is a possible way to to do this. When i try such procedure : CREATE astoredprc (@gender bit) AS SELECT * FROM tableA WHERE Gender = @Gender GO -- In such prc. i can only get rows with a where clause. What if i want to use the same procedure with all rows and omiting where. I have many fields like this and I m unsure if I should use a text query or not ... Thanks for all of u from now on
I want the procedure to process 1) all data is no dates are presented 2) all data after the start date, if no end date is supplied 3) all data before the end date if no start date is supplied 4) all data between the start and end dates if both are supplied
Now, instead of an elaborate conditional, I added this to the WHERE clause of my SQL statement:
AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date))
It works fine, but I want to know if anyone has a different/better way of doing it, or if there is a big bug waiting to happen here.
I typically don't like to create multipurpose routines in my code, but this is a better approach for my in a non-object-oriented world of SQL.
Hi, I wish to create a user defined funtion in sqlserver2005 with optional parameter list. So at the time of function calling the parameters should be a optional one. How can i do this? please help me .
I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together)
1 2 3 4 / x x x x / x x x x / x where x = not supplied / = supplied a value
(and so the list continues) Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements.
Is it possible to have the field as a unique key and a optional one?It is like.. for example, office code has to be unique (cannot beduplicated with the same code) and it could be null too.
Dear GroupI'd like SQLManager to start on Win98. I've added it to StartUp and itshows in the right-bottom corner of the desktop upon operating systemstart but the database still shows as stopped. Is there's a command Ican use like SQLMANGR.EXE /start or SQLMANGR.EXE /run from the commandline?Thanks very much for your efforts and sharing your expertise!Martin
Hi All,I have a stored proc which looks like this.Create ....(@id int,@ud int,@td int=0)if @td=0select bkah from asdf where id=@id and ud=@udelseselect bkah from asdf where id=@id and ud=@ud and td=@td---------------------------------I am wondering if i could replace if condition with the following lineselect bkah from asdf where id=@id and ud=@udand ( @td<>0 and td>@td )IS sql server 2000 smart enough not to use the td>@td in the query if@td is 0Thanks all
