Hello all,
I have a problem. I have the address like following:
204 east 40th street,brooklyn,ny 11229
I have to break this field into four fields:
address,city,state,zip
address should be: 204 east 40th street
city should be: brooklyn
state should be: ny and
zip should be: 11229
How would I be able to do that?
Any help will be appreciated.
Thanks in advance!!
I have data saved in nvarchar field in SQL Server and I am displaying it using <= dbReader["my_field"] but even the three or four paragraphs comes as one paragragh and I don't know know where are my line breaks going?
View 6 Replies View RelatedI have to output as one filed name, mailing address, and phone of each company that we do business with.
the out put must look as follows:
Company Name
Store number: #####
Street Address 1
Street Address 2
City, State Zip + 4
Phone: (###) ###-####
Each piece of data is a single field and will have to be concatenated into one.
How do I force the line breaks where I need them?
Also each piece of data will be compared to a €œsource€? for validity and I will need to mare the individual data pieces red where €œwrong€?. Like so:
VALID MY DATA
Company Name Company Name
Store number: ##### Store number: #####
Street Address 1 Street Address 1
Street Address 2 Street Address 2
City, State Zip + 4 City, State Zip + 4
Phone: (###) ###-#### Phone: (###) ###-####
Any ideas on how to do this? I have used IIF for conditional formating of field colors in the past, but my expierence is that it changes the WHOLE display field not just piece of data the IIF is wrapped around.
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedI have the following fields in table A:
GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI
===============================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
===============================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
Hello,
I have a field like below which has blanks 'b';
CODE
000123'b'99999'b'RED RING
WHAT i want to do is like below?
FIELD1
000123
FIELD2
99999
FIELD3
RED RING
I have a table returning results like this
Row1 || Row2 || ERPID || ParentID || LevelID || Category || SubCategory || DDate || publish
1 || 1 || 10152159 || 1015 || 2159 || LOCTITE || LOCTITE1 || 29/01/2015 12:10 || 0
1 || 2 || 10152134 || 1015 || 2134 || LOCTITE || LOCTITE2 || 29/01/2015 12:10 || 0
1 || 3 || 10152157 || 1015 || 2157 || LOCTITE || LOCTITE3 || 29/01/2015 12:10 || 0
2 || 1 || 10062003 || 1006 || 2003 || COMPUTER || COMPUTER1 || 29/01/2015 12:10 || 1
2 || 2 || 10062148 || 1006 || 2148 || COMPUTER || COMPUTER2 || 29/01/2015 12:10 || 1
[Code] ....
How I can make the result return like this way?
Row1 || Row2 || ERPID || ParentID || LevelID || Category || SubCategory || DDate || publish
1 || 1 || 10151015 || 1015 || 1015 || LOCTITE || || 29/01/2015 12:10 || 0
1 || 1 || 10152159 || 1015 || 2159 || LOCTITE || LOCTITE1 || 29/01/2015 12:10 || 0
1 || 2 || 10152134 || 1015 || 2134 || LOCTITE || LOCTITE2 || 29/01/2015 12:10 || 0
1 || 3 || 10152157 || 1015 || 2157 || LOCTITE || LOCTITE3 || 29/01/2015 12:10 || 0
2 || 2 || 10061006 || 1006 || 1006 || COMPUTER || || 29/01/2015 12:10 || 1
2 || 1 || 10062003 || 1006 || 2003 || COMPUTER || COMPUTER1 || 29/01/2015 12:10 || 1
[Code] ....
Here is the code I use
SELECT
DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr
ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
[Code] .....
I have the following fields in table A:
GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI
===============================================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
=====================================================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
View 3 Replies View RelatedI have a report with several matrix objects. The data contained in each matrix is simple. One matrix has one column of labels and a column of data. The other has 3 columns of data. Both matrix's only show about 25 rows of data. A variable amount of data is displayed above the matrix's so that sometime the render across a page boundary. Whenever this happens the matrix doesn't render rows down to the bottom of the page. A considerable amount of empty space is left on the page, the completed matrix is displayed on the next page. I'm wondering how to get the matrix to render in this empty space.
This problem only occurs when I view the report on screen, in the Visual Studio "Preview" window, or from the Report Server web site. Tiff, pdf and printed output doesn't contain the extra space.
I've tried adding my matrix to a List and a Rectangle to see of this would fix the problem but it didn't help.
I've check the dimensions and margins or the page and I don't think I have any sizing problems, everything should fit on the page.
--Mark
Hi,
I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.
Like this:
table 1
uidunameuloc
1mehere
1methere
2youhere
2 youthere
table 2
uidulocuparam#uparamval
1here1a
1here2b
1here3c
1there1d
1there2e
1there3f
2here1g
2here2h
2here3i
2there1j
2there2k
2there3l
result in table 3
uidunameuloc uparval1 uparval2 uparval3
1mehere a b c
1methere d e f
2youhere g h i
2 youthere j k l
uparam# field in table 2 always the same sequence
Any ideas????
Thanks...
I am trying to make a field that has info from othe fields
Table = Page0
Fields = LName, FName, SS
I want a new field (Folder) to be all three fields.. for example
LName= Smith
FName= John
SS= 1234
I want to update Folder = Smith_John_1234
Hello to All,
I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber".
Table: DirectHome
Column fields.......
PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE
0000001091 , W , 000026TH , RD
Thank you
RV
I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that?
Here is my query:
select Z, count(*)as num from Table
group by Z
having count(Z) > 1 ----- this returns 213 rows
Select Z, A,B,C,D From Table
Group By Z, A,B,C,D
Having Count(Z)>1 ----This gives me nothing
Hope I am explaining this correctly as to what I want.
Thanks,
Saru Brochu
I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.
View 1 Replies View RelatedIf I have a database table with the following columns:
ID
Other_ID
Description
And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name
How would i do that?
Here is some sample data and what I would like returned
TABLE1
ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2
TABLE2
ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1
The only way around it now is that I store Other_Name in Table1.
Thanks.
Hi,
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
rfpsector_nameproposalscurrent_year
fpname_08_rfpid
NOTE:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname.
I am at a loss and would greatly appreciate any help.
Thanks
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECTÂ Â Â TXPRCL, TXALTR, TXANAM
FROMÂ Â Â dbo.PCWEBF21
WHEREÂ Â Â (TXALTR > 0)
UNION
SELECTÂ Â Â TXPRCL, TXTAXP, TXTNAM
FROMÂ Â Â dbo.PCWEBF21
WHEREÂ Â Â TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.
What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.
I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.
So instead of:
a
122
b
joe
c
st
I would have:
d
122 joe st
Thanks!
Hello,
I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.
Something like - Note: the codes below doesn't work!!
DECLARE @ResultID as int
DECLARE @AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA
FETCH NEXT FROM _rows INTO @AccID
WHILE (@@fetch_status <> -1)
BEGIN
UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @AccID)
WHERE AccID = @AccID
FETCH NEXT FROM _rows INTO @AccID
END
Thanks in advance
Hi All.
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:-
Column Name Data Type
OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money
I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both.
SELECT OrderNo + ' ' + Cost AS Expr1
FROM [Order]
Do i have to convert them both to string/varchar first? If so, how?
Thanks for your replies.
Good afternoon one and all,
I have the following problem that I can use some help with :
I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement
Something like
SELECT ([stc_dd] & '/' & [stc_mm] & '/' & [stc_yy]) AS stkdate
(the above line does not work)
Hope that is clear, thanx in advance for any and all help
Gurmi
I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State .
Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields?
So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State.
Thanks for your help!
Scott
i currently have a table like this..
user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6
the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..
on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4
any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?
thanks
I have a text field that I need to parse out into 4 fields.
30.125x23.625-18.875x25.375
6.1875X19.375-2.4375x15.625
0X0-0x0
26.875X11.375-11.125x22.875
0X0-0x0
0X0-0x0
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
26.625X14.875-11.125x22.875
26.625X14.875-11.125x22.875
I need to 26.625X14.875-11.125x22.875 should be 26.625 then 14.875 then 11.125 then 22.875
I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.
OLD FIELDNEW FIELD
B123F
B123H
B123T
OLD FIELDNEW FIELD
B123F
B123H
B123T
Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results?
I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate.
Any comments on this would be appreciated.
Rye Guy
use Northwind
Go
selectÂ
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_DateÂ
, dbo.Customers.CustomerIDÂ
, dbo.Customers.CompanyNameÂ
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.Â
total_Amount and  Grand_Total cannot be done with my skill level.
Hi ,
I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?
Best Regards,
Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
View 2 Replies View RelatedI want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:
Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC
what am i doing wrong?
Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)
I tried to do that. But I got a error message.
"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Please can some one help me out?
I have the folliwing two fields, want tp concatenate:
=Fields!sequenceno.Value & =Fields!LogType.Value
Thank you very much for the information.