Hi, all experts here,
I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?
Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
I have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions.
Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this.
with CustomGroup( id)
as
(
Select id from Groups g
Inner join GroupStatus s on s.Id = g.Id
Where s.Status = €˜Active€™
)
IF @DisplyStatus=€?UserDetail€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.* from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserSimple€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.FirstName,u.LastName,u.DOB from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserWithAddress€?
begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
View 9 Replies View RelatedHi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
View 6 Replies View RelatedGreeting SQLers,
I'm attempting to define a Default value for a new user-defined-datatype in SQL Server 6.5, but can't as the dropdown list only allow (none) value!
Anyone can help? TIA.
How to define schemaname & how to qualify schemaname in SQL
statement.
Hello, everyone:
I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD
Thanks a lot.
ZYT
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets()
On Error Resume Next
'Dim pt As PivotTable
Dim Sh As Worksheet
Dim refresh As Boolean
Dim tempMode As Boolean
Dim startTime As Date
[code]...
Is there a way to define a key that puts the text 'SELECT * FROM 'into the Query Analyzer window?I must type this about 50 times a day but cannot see a simple way ofdefining a key to write it for me..(tools/customize) seems to execute everything you put in there ratherthan leave it on the screen for me to add table names etc to.thanks for your time...
View 2 Replies View Relatedhallo
i am on the very beginning with sql.s,
how can i define a default value for the table field like its possible in access,
for example: the if the user not gonna enter a val so there is a default val predefined in the table field,
for example i would like to predefine the date now on the smalldate field....
thanks
Hi
I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId
The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>"
SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId">
<SelectParameters>
<asp:ControlParameter ControlID="Drop1" Name="SourceId" />
<asp:ControlParameter ControlID="Drop1" Name="CatId" />
</SelectParameters>
</asp:SqlDataSource>
Can anyone help me to define the parameters?
thanks
I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
View 1 Replies View RelatedWhy is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
thank you
Hi, I am trying to connect to my local SQL Server through a webform ( on VS.NET ). My web.config shows something like this -
sqlConnectionString="data source=IP address;Trusted_Connection=yes"and the tutorial I am following says something this - SqlConnection myConnection = new SqlConnection( "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI");If I just replace the NetSDK either with my IP address or the servername (my machine name), I get a SQLException that server doesn't exist or access denied. I have specified "windows authentication". How do I correctly define my connection string ? Please guide me.Thanks,sbs.
I noticed in SQL ODBC API reference that SQLGetTypeInfo would return true for AUTO_INCREMENT if a smallint field is defined as autoincrement. Is there a simple way to set a field autoincrement thru the SQL Server`s front end?
T.G.
One more silly question,
What is an identity column? how can I define it?
table abc
=========
no (primary key) name
================ ====
1 jung
2 love
4 tom
In this example, I want to define 'no' as an identify column so that I can update it as I wish.
Thanks in advance,
-----------------------------------------------------------------------------
Subject:
From:
Date: sorry one more question (reply)
Kurt ()
6/23/01 2:48:32 AM
Jung,
Take a look at your table. It looks like you don't have an identity column
defined for "no" (your column name). Set Indentity_Insert doesn't work for tables that don't have one defined. PS A primary key is not the same as an Idendtity column.
------------
jung at 6/22/01 9:49:34 PM
table : abc
no (primary key) name
================ ====
1 jung
2 love
4 tom
In this record, I wanted to update no 4 to 3 which is primary key.
So, I used the query as below.
=============================
Set Identity_insert abc on
UPDATE abc
SET no = 3
WHERE no = 4
Set Identity_insert abc off
go
=============================
But I got an error;
Server: Msg 8106, Level 16, State 1, Line 1
Table 'abc' does not have the identity property. Cannot perform SET operation.
Why this happens? I really want to update the primary key.
I'm working on a SQL report and am returning a list of values for a column/field, "STATUS", to be used as one of the search parameter.
SELECT DISTINCT STATUS FROM [table]
ORDER BY STATUS
Values returned are [blank], approved, onhold, etc.How can I get the [blank] value to be set as "All" within the report parameter for STATUS?
Fazlul Haq writes "i m new to sql server2000.my question is
" I CREATE A TABLE IN WHICH THREE COLUMS EXIST i.e SNO(NUMERIC),NAME(CHAR),FNAME(CHAR).I WANT TO ENTER VALUE IN SNO FIELD STARTING FROM ZERO(0)e.g 011,021,022 etc and when enter value without starting from zero an error messag is appered on the screen.I WANT THAT THIS CAN BE DONE BY CREATING A "FUNCTION" OR "RULE" OR "STORED PROCEDURE". PLZ HELP ME ""
Hi
I have a question about user define function in sql.
How can I use Exec in UDF?
What I mean is that I made a string in a UDF, And I need to
execute the string in function. but I think it's illegal.
so tell me how can I use it?
P.S : forget about useing stored procedure instead of function.
thanx.
I am designing a new table with a few columns that may or may not havea value on each row that is inserted.What issues determine whether to allow a NULL value to be inserted forthat column or define a default value to be used?I want to think through the repercussions of this decision before I getinto production.
View 3 Replies View RelatedI have a primary key (column name is emp_id) in employee table. Also,I would like to make a combination of other two columns is unique.(combination of officecode field and claimno field must be unique).how can I implement this uniquess in ms sql 2000? thank you.
View 2 Replies View Relatedhi,
I'm making a cube using SSAS and the data in the cube is updated daily. however, each day I take the data with the date of migration because I want to keep the data as history snapshots. the problem is when browsing the data I want to aggregate only the data of a desired date not all the data together. can I define a custom aggregator rather than those found in the properties for the measures?
Thanks a lot.
Mina
I have a DTS package (SQL2000) which uses ActiveX scripts to check for records with certain criteria in a table. If the certain criteria is met, I want to initiate a child package that preforms a data-extraction. Otherwise move onto the next step which check for a different set of criteria. Currently if the criteria is met, I flag the task as success to dictate moving to one set of steps, otherwise failure & moving onto the next check.
The problem I am realizing, is that if I 'Fail' on of the check steps, subsequent steps furthur down the route that I am directing it to go, do not run. These are other ActiveX tasks that send email, and do furthur analysis.
Why is this the case? Can't you perform a logical check, and based upon the check have a decision to run certain steps? I attempted to use the result 'Main = DTSStepScriptResult_DontExecuteTask', but I must not be using it correctly, because it lists the task as returning failure.
Any help would be great.
Kevin Albrecht
Is there a SQL statement that I can use which allows me to set value lables in a result set? For example, if a table has a gender variable with values of 'M' & 'F', can I write a sql statement which creates a result set that changes these to 'Male' & 'Female' so that everything I do with the result set shows the new labels (but does not change the underlying M/F values in the orginal table?
I do not want to create a new table, but rather display the M/F values as Male/Female when I access the result set in an application (such as Report Services).
Thanks for any advice you can give.
Ed Sloat
I have a client who tells me in Access you could assign the printer directly to the report using PrtDevNames and PrtDevMode. Apparently it's true.
Can this be done in Reporting Services?
Hi!
I plan to prepare a lot of DMX statements, but can I control
when the datamining model actually was calculated?
Lets say i make following
1. create mining structure... - I expect that this execution will take "no" time
2. create mining model... - I expect the same
3. insert into <miningmodel>... select ... - I expect that that actually will take some time- right?
I really only want to prepare definition of mining structures/models and telling the model what data (insert into) it should use.
Is it possible to control the actual processing, eg. execution of insert statement (= execution of time consuming select-part)?
I have a batchjob that only need to define things but calculation of mining model is not needed until someone really
want's to see it (and it is ok that this person has to wait on processing).
BOL writes
>>
Processing Options
The following table describes the processing methods that are available in Analysis Services, and identifies the objects for which each method is supported.
Process Default
Detects the process state of an object, and performs processing necessary to deliver unprocessed or partially processed objects to a fully processed state. This processing option is supported for cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions.
...
<<
But how to control this behaviour?
Best regards
I want to run some sql code from Query Analyzer. The sql code uses parameters.How do I define the parameter before setting it to a value? I don't want to hardcode the input parameteres.
Ex:
create @Prim -- This is the line I need help on. Instead of "create", what is -- the proper way of setting this value ?
set @Prime=13
SELECT Prime.Quaternion, Gaussian.RegionId, Laplace.Primer,
Hello! I hope that I have posted in the right spot.
We are looking for advise. I know that there is no right or wrong answer. "It depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical drives are being used. These 3 physical drives are 1 container with Raid 5. This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is SAN.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples of what they are doing or provide suggestions?
The SQL below is the start of a massive Stored Procedure for Comparing two Datasets, which will be produced onto a report.
I was wondering if I could call an SQLserver Procedure that would tell me the names of all the Columns that are produced by this SP, so I can print them out and more easily code the report?
SELECT
stk.StockNumber,
stk.DefaultImageName,
tVTP.Make as PolMake,
stkV.VehicleMake,
tVTP.Model as PolModel,
stkV.VehicleModel,
tVTP.ModelNo as PolModelNo,
stkV.VehicleModelNo,
tVTP.EngineNumber as PolEngineNumber,
Stk.EngineNumber,
tVTP.comHeadLightNumber as PolHeadLightNumber,
Stk.comHeadLightNumber,
tVTP.comTailLightNumber as PolTailLightNumber,
Stk.comTailLightNumber ,
tVTP.comBumperLightNumber as PolBumperLightNumber,
Stk.comBumperLightNumber,
tVTP.comCornerLightNumber as PolCornerLightNumber,
Stk.comCornerLightNumber,
tVTP.Chassis as PolChassis,
--Drive Train
tVD.DriveTrainDescription as POlDriveTrainDescription,
StktVD.DriveTrainDescription,
--Body Type
tVBT.BodyTypeDescription as PolBodyDescription ,
StkVBT.BodyTypeDescription
FROM tblStock Stk
--JOINS FOR THE Policy Definition
INNER JOIN tblVehicles V
ON V.VehicleID = Stk.VehicleID
INNER JOIN tblVehicleType_Policy tVtP
ON tVTP.VehicleMaster = V.VehicleMaster
AND tVTP.Make = V.VehicleMake
AND tVTP.Model = V.VehicleModel
AND tVTP.ModelNo = V.VehicleModelNo
INNER JOIN tblVehicleDriveTrain tVD ON
tVD.vehicleDrivetrainID = tVTP.DrivetrainID
INNER JOIN tblvehicleBodyType tVBT ON
tVBT.VehicleBodyTypeID = tVTP.BodyTypeID
--JOINS FOR the Stock Definition
INNER JOIN tblVehicles STkV ON
StkV.VehicleID = Stk.VehicleID
INNER JOIN tblvehicleBodyType StkVBT ON
StkVBT.VehicleBodyTypeID = Stk.BodyTypeID
INNER JOIN tblVehicleDriveTrain StktVD ON
StktVD.vehicleDrivetrainID = stk.DrivetrainID
--INNER JOIN tbl
WHERE Stk.StockNumber LIKE 'V%'
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
FROM dbo.QRY_FacNew_Term
WHERE (Exclude <> 'Y')
ORDER BY Category, Client_Name
I have this query that calculates the next possible shipping day, based on 3 conditions:
- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green
- There might be extra days (@xdays) required by the process - marked blue
- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate
from WORKCALENDARDATE
where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and
[code]...
So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.
DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456'
for xml path('')),1,1,''))
Hi everyone,
Primary platform is sql25k running with sp2.
I'd like to define for a concrete field which only could have three values:
"a","b","c"
TIA
Hello Friends,
I am new to SSRS and i want to define a Variable to one of the expression of my ssrs report. Can anyone help me to solve this issue.
I am using a matrix in the report and in that matrix in each box i am using some expression to get the value. So i need to define a variable .. Is it possible to define a variable ?