I need t do some date converts on some varchar fields which
might have trailing spaces.
I tried this below but still
select df_ppd, df_xray_date from patient_
where convert(datetime,rtrim(df_ppd),101) > '06/20/2000'
received a conversion error message
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Any ideas?
I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category".
Sample:
Human Resources
All documents related to new hire process.
New Hire Request forms, New Hire forms, Termination Forms
Total Files: 25
Programs & Utilities
Windows utilities and other tools to keep you safe on the net.
Antivirus, Spyware Removal Tools, Other programs
Total Files: 37
My SQL2005 DB has 2 tables.
1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription)
2) Files. (CategoryID, ParentCAtegoryID, FileTitle)
My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents.
My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only.
Sample:
Human Resources. CategoryID=4, ParentCategoryID=NULL
New Hire Request forms. CategoryID=13, Parent CategoryID=4
New Hire Forms. CategoryID=17, Parent CategoryID=4
Termination Forms. CategoryID=22, Parent CategoryID=4
Programs & Utilities. CategoryID=6, ParentCategoryID=NULL
Antivirus. CategoryID=8, Parent CategoryID=6
SpyWare Removal Tools. CategoryID=26, Parent CategoryID=6
Other Programs. CategoryID=31, Parent CategoryID=6
When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories.
Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else.
I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.
Dear All
I am trying to populate an OledbDatareader for binding to a ASP datagrid.
For this I use select statement to display combined fields in a datagrid cell.
Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on.
But the problem is if any of the three field is null the combined field 'Address' returns as Null.
Please help me to overcome this problem.
Regards
kalanad ( beginner)
I have a SQL table which has field data like below
field a field b
123 KB 246 kb
How can I strip out just the numbers(there will always be a space between the number and KB?
Thanks
Can you please tell me, is it possible to Trim the columns in a select statement:
select eventid,referenceno,EventDesciption,modulename,moduleid,created_by,CONVERT(varchar(10),eventdate,101) as eventdate from MYTable
Thank you very much.
hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.
I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction.
This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent.
This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used.
Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation.
Orders:
Contains the core order information pertaining to all transactions
CREATE TABLE [Orders] (
[OrderID] [int] NOT NULL ,
[MerchantID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentMethodID] [int] NOT NULL ,
[IsTestOrder] [bit] NOT NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
)
Transactions:
Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction.
CREATE TABLE [OrderTransactions] (
[OrderID] [int] NOT NULL ,
[TransactionID] [int] NOT NULL ,
[TransactionTypeID] [int] NOT NULL ,
[CustomerIPAddress] [bigint] NOT NULL ,
[Description] [nvarchar] (250) NOT NULL ,
CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED
(
[OrderID],
[SequenceID]
) ON [PRIMARY] ,
CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
)
)
create view vwchannel
as
select distinct s2.soptype,
s2.sopnumbe , --internet orders/information center orders
s2.custnmbr , --amazon.com orders
s3,itemnmbr, sum(s3.quantity) from salestab s2
left outer join (select distinct soptype,sopnumbe,itemnmbr,quantity from salesdisttab) s3
on s2.soptype = s3.soptype and s2.sopnumbe = s3.sopnumbe
where (s2.CUSTNMBR LIKE 'amazon%')
AND ((s2.SOPNUMBE LIKE 'net%') OR (s2.SOPNUMBE LIKE 'inv%'))
AND s2.soptype = 3
group by s3.itemnmbr,s2.sopnumbe,s2.custnmbr,s2.soptype
go
i m getting 70 rows in output which is correct but..
i have to combine sopnumbe and custnmbr into one column name as channel
how can i do that?
i tried like:
case when (s2.SOPNUMBE LIKE 'net%' OR s2.SOPNUMBE LIKE 'inv%') then s2.sopnumbe
when s2.CUSTNMBR LIKE 'amazon%' then s2.custnmbr
end as channel
but i m getting 0rows affected in ouput.. instead of 70rows...
can anyone help me?
thanks.
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
View 4 Replies View Relatedive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?
Hi,
Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".
I do get the answer but its not the correct figure. My SQL statement is as follow:
SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName
Please help.
How to convert an INT value from the registry to a VARCHAR but not sure how to convert from INT to VARCHAR within a CASE statement. Below is my code which current fails on "Conversion failed when converting the varchar value 'Automatic' to data type int".
DECLARE Var1 INT
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEMCurrentControlSetServicesSQLServerAgent',
@value_name = N'Start',
@value = @Var1 OUTPUT
[Code] ....
Hello!
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduNextContacts.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduContactsAuditChanges.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
Thanks in advance,
Harry
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
View 1 Replies View RelatedI'm running a query to show the transfer fees spent by a club with data from two tables :
Code:
SELECT SUM([Transfer Fee]) From [Summer_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Union All
SELECT SUM([Transfer Fee]) From [Winter_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Shows the sum from each table :
Code:
545.01
110.98
What do I need to add to the query to show the total from both tables?
In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc.
We have a report in which there are 2 parameters (Drop Downs).
Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine.
Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.)
Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?
I need to convert a date like 08/1/2009 to 0809
I also need to show currency as 100.00 and not 100.0000
How can I do these in a select statement?
SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment
I appreciate any help!
Hi,I currently have a ms access update query that runsperfectly well and quicly in access however I now need to add this queryto convert this qeryu to oracles equivelant sql syntax and add it to the endof an oracle sql script.Unfortunately Im not having much success although i seem to be able toconvert it to a working oracle sql. it takes hours to run the statement inoracle where as in access it runs in secondsany help is appreciated.Ms Access sql :UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tpriceWHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));Oracle SQL :update pro.tst_rv3x_rpt_wrk xset(x.mkd_date, x.mkd_price) =(Select a.new_active_date, a.new_tpricefrom pro.sty_tprices a, pro.style_colours bwhere a.sty_id=b.sty_idand b.bf_mat_char_val = x.bf_mat_char_valand b.season = x.seasonand b.sty_num = x.sty_numand b.sty_qual = x.sty_qualand a.new_active_date is not null)
View 1 Replies View RelatedHi,
Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:
take all order totals from same client and add together order totals to produce a grand client spend total.
Anyone help!?
thanks
Dear GroupThe scaenario is as follows:FirstName and LastName are separate columns in the contact table and Iwant to be able to search e.g. for the FirstName and part of theLastName at the same time e.g. 'John A' should return 'John Adams'.Would be grateful if you can give me some hint as I don't seem to getit work.SELECT FirstName, Lastname FROM i2b_contact WHERE (SELECT Firstname +Lastname AS CName) LIKE 'John A%'Thanks very much for your help and efforts!Martin
View 2 Replies View RelatedI am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one.
The field that does not work is a varchar(500) field (named Description).
I am not sure what the problem is.
The error that I am getting is:
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. The char value has incorrect syntax.
Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes.
Any help is appreciated.
------------------------------------------------------------------------------------------------------
declare @Match varchar(75)
declare @NumRows int
declare @StartPos int
declare @OrderBy varchar(25)
declare @OrderDir varchar(4)
set @Match = 'dog% AND bark%'
set @NumRows = 25
set @StartPos = 100
set @OrderBy = 'Description'
set @OrderDir = 'ASC'
WITH catitems AS
(
SELECT ci.ID, ci.SupplierCode, ci.Description, ci.AUDIO_LINK, ci.SoundLength, ci.HighCost, ci.Channels,
ROW_NUMBER() OVER (ORDER BY
CASE
WHEN @OrderBy='ID' AND @OrderDir='ASC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='ASC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='ASC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='ASC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='ASC'
THEN CAST(ci.Channels AS smallint)
END ASC,
CASE
WHEN @OrderBy='ID' AND @OrderDir='DESC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='DESC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='DESC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='DESC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='DESC'
THEN CAST(ci.Channels AS smallint)
END DESC) As RowNo
FROM TableName As ci
WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1
)
SELECT catitems.ID,
catitems.SupplierCode,
catitems.Description,
catitems.AUDIO_LINK,
catitems.HighCost,
catitems.Channels
FROM catitems
WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1
------------------------------------------------------------------------------------------------------
Thanks,
Aric
Hi,
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
Can anyone help me please?
Thx
I have a table with two columns refid and name and it has the following values
1 tom
1 jim
2 bob
1 bob
I need a resultset that would have the following values
1 tom, jim, bob
2 bob
I have tried couple of things one being:
DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist
But I am looking for a resultset with a unique refid and all the names comma separated for that refid.
I have configured active passive cluster in production environment. And we also have a dr which we have configured with asynchronous mirroring with no witness. Currently active node(node
a) is in sync with dr. When failover happens and the second node(node
b) becomes active, the mirror is broken and goes to disconnected mode.
But when we failback again to node a mirror is connected again and is in sync again. In our setup we have active passive cluster and a standalone server as dr.
I am working on to convert my static Store procedure to Dynamic.
I have created a Store procedure with Merge statement which is inserting new record and updating existing record.
This SP I will use in SSIS Insted of Data Flow Task I will run in Execute SQL Task.
Now my biggest problem is I dont know how to convert static code toi dynamic
Below is my Store procedure code.
As you can see my Source Query
I have a filemaster table as shown below which consist of Input filename,Source table ,Destination table and BBX expression.
Input_FilenameSourceTableName DestinationTableName BBxKeyDerExpr
CCTFB ImportBBxCctfb ArchiveBBxCctfb SUBSTRING(Col001,1,6)
CEMXR ImportBBxCemxr ArchiveBBxCemxr SUBSTRING(Col001,1,10)
In my source query I want to change the value of Source table ,Destination table and BBX expression dynamically on the basis of input file.
Purpose of making dynamic is that I have created separate sp for all the input, my clients want to have sungle dynamic sp which will execute on the basis of input file.this input file name I wil get fromm variable which i have created in SSIS Package.
Lets consider @File_name is the variable in package which store the file name
if file name is CCTFB then my query should take the Source table ,Destination table and BBX expression value from file master table.
Like that I have 100 of source query and evry query have diffrent number of columns. How can I change the column number in uodate and insert statement dynamically on run time.
CAST(SUBSTRING(Col001,1,6) + SUBSTRING(Col002,1,10) AS varchar(100)) :-It creates a key for comparing, this value i can take it from filemaster
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003,Col004) AS varchar(max))) -here numberv of column need to be changed .
(SUBSTRING(SOURCE.Col001,1,6) + SUBSTRING(SOURCE.Col002,1,10)) this condition also i can take it from file master.
[Code] ....
I am able to get inserted and updated rowcount, but not able to get the matching records count.
This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
I am investigating the feasibility of a configuration with 3 databases on SQL2005
DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions
DB_B is a subscriber database which subscribes to pub_txn
DB_B is also a publisher which serves up merge publication pub_merge
DB_C is a subscriber database which pulls pub_merge
===============================
Updates on DB_A are successfully replicated to DB_B
Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B
===============================
Updates on DB_B are successfully replicated to both DB_A and DB_C
===============================
Updates on DB_C initially failed with the error
Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.
I then changed the login repllinkproxy to be a db_owner in DB_C
I now get the error
Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.
=================================
I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?
2) Why is update on DB_A not reaching DB_C
3) Why can't I update DB_C?
Any suggestions gratefully received
aero1
Looking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
How to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS
[Code] .....
Hi All :CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), SubjectVarchar(20), Type Char(1))INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')SELECT * FROM TABLEAI am on SQL 2000.I need an output where i have to have a resultset grouped on Type, butthe results in one row.In the resultset I needTypeP PersonType P Country, Type Q Person, Type Q Country, TypeW Person Type W Country---------------------------------------------------------------------------------------------------------------------Einstein:ArchimedesGermany:GreeceKant:Aristotle Germany:GreeceKafka:HomerGermany:Greece************************************************** *************I have written a puesdo-cursor code to do the same, but if there is away to do as a set operation, that would be greatPlease select as a whole and past in query analyser as the resultsetis all overlaid when i paste in this box.Thank youRS
View 2 Replies View Relatedis there a function trimming string such as Trim in vb?
View 2 Replies View RelatedIN ACCESS
UPDATE 14_Together_final_ICNs INNER JOIN 14_Together_UniqueSKUs ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU SET [14_Together_final_ICNs].sku_desc = [14_Together_UniqueSKUs].NewDesc
WHERE (((Trim(Nz([NewDesc])))<>""));
THIS IS WHAT I TRY IN SQL SEVER BUT GET A DIFFERNET RESULT
UPDATE [14_Together_final_ICNs]
SET sku_desc = [14_Together_UniqueSKUs].NewDesc
FROM [14_Together_final_ICNs] INNER JOIN
[14_Together_UniqueSKUs] ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU
WHERE (RTRIM(LTRIM([14_Together_final_ICNs].sku_desc))<> N'')
Guys/Girls
Have a small problem I have received a load of data in a flat file format and each column has dividend up with a comma. I have stuck the whole row in to its own column.
What I want to do is take the first 12 characters of the row and put them in there own column.
I think I need to use the Len function or the trim function, so can some body point me in the right direction on how to do this.
Regards Lee
Okay basic question from the village idiot. How do I return only the date and not the time from a datetime column and also can I return only a set number of digits in a float column?
Thanks in advance peter
The Yak Village Idiot