November 13, 2008 at 1:56 pm
how would i write this, i have two records for every customer
first time ID=1, second time ID=4
so if the customer1 = 48313
customer2 =49155
the column should read as
CustomerID
48313-1
48313-4
49155-1
49155-4
and so on.....
November 13, 2008 at 2:02 pm
You're going to hate this, but...
Please provide us with the DDL for the table (the CREATE TABLE statement), sample data for the table (as INSERT INTO statements), what you have already tried, and the expected results based on the sample data.
From your post, don't really know what it is you are really trying to accomplish.
November 13, 2008 at 2:29 pm
CREATE TABLE [dbo].[CustomerAddresses](
[CustomerID] [dbo].[C_ID] (nvarchar(30))NOT NULL,
[AddressTypeID] [dbo].[C_ID] (nvarchar(30))NOT NULL,
[CustomerAddressID] [dbo].[C_ID] (nvarchar(30))NOT NULL
insert into dbo.CustomerAddresses
(CustomerID, AddressTypeID, CustomerAddressID)
values('48313', '1', '48313-1')
('48313', '4', '48313-4')
('49515', '1', '49515-1')
('49515', '4', '49515-4')
output should be:
CustomerID AddTypeID CustomerAddressID
48313 1 48313-1
48313 4 48313-4
49515 1 49515-1
49515 4 49515-4
im trying to write the condition, i dont know how to write
I have tried select A.Account_number +'-1'
and i get
CustomerID AddTypeID
48315-1 -4
does this work for you
November 13, 2008 at 2:41 pm
I'm a little confused. Couldn't you just select CustomerAddressID?
Or try this:
Select CustomerID + '-' + AddressTypeID
November 13, 2008 at 2:58 pm
paul.starr (11/13/2008)
CREATE TABLE [dbo].[CustomerAddresses]([CustomerID] [dbo].[C_ID] (nvarchar(30))NOT NULL,
[AddressTypeID] [dbo].[C_ID] (nvarchar(30))NOT NULL,
[CustomerAddressID] [dbo].[C_ID] (nvarchar(30))NOT NULL
insert into dbo.CustomerAddresses
(CustomerID, AddressTypeID, CustomerAddressID)
values('48313', '1', '48313-1')
('48313', '4', '48313-4')
('49515', '1', '49515-1')
('49515', '4', '49515-4')
output should be:
CustomerID AddTypeID CustomerAddressID
48313 1 48313-1
48313 4 48313-4
49515 1 49515-1
49515 4 49515-4
im trying to write the condition, i dont know how to write
I have tried select A.Account_number +'-1'
and i get
CustomerID AddTypeID
48315-1 -4
does this work for you
First, no, the CREATE TABLE script won't work. Read BOL for the syntax for CREATE TABLE and I think you'll figure out what is wrong.
Second, you have given me one table (CustomerAddresses), with sample data that ALREADY has what you want accomplished done (look at your third column).
Third, your code appears to reference ANOTHER table that you haven't provided.
Fourth, your CustomerAddress table has a multicolumn unique (primary key from the looks of it) with these two columns CustomerID and AddTypeID so a simple query against this table will generate what you are looking for, even though you have included it in your sample data.
select CustomerID + '-' + AddTypeID from dbo.CustomerAddresses
Have I missed anything that may shed a little more light on what you are actually trying to accomplish?
November 13, 2008 at 4:24 pm
i am missing the view
November 13, 2008 at 5:01 pm
this query gives me the output im looking for
select CustomerID + '-' + AddTypeID from dbo.CustomerAddresses
48313-1
48313-4
48315-1
48315-4
i have a question, i have a view, the column CustomerID (from table) = Account_number (from view)
each query below gives me the record that I need, but i need them to output the same as the table output above, there isnt an example you can give me where I can write two records for every Customer.
First Time through ID = 1, second time through ID = 4"
select A.Account_number +'-1'
from dbo.AVW_11i_WA_ADDRESS A
select A.Account_number +'-4'
from dbo.AVW_11i_WA_ADDRESS A
Am I explaining this correctly? i will continue to try and get the create statement correct also.
November 13, 2008 at 10:07 pm
Problem: You won't give us what we ask for so we can't provide you with the answer you are looking for.
You only give us half answers to our questions, what do you expect us to do, pull the answer out of our arse?
If you have read the article in my signature block below, you should be able to provide us with everything we need. This means CREATE TABLE, CREATE VIEW, sample data, current code (the entire query or stored procedure, not just tiny snippets), and expected results based on the sample data.
As I have told you, we aren't King Midas, we can't turn crap into gold. You have to be up front and honest with everything if you expect the same in return.
The more and BETTER information you provide (which means you have to work), the better answers you will get in return. You also should spend time reading Books Online (BOL) as well.
If there is a view involved, we need it. Because a view is based off base tables or other views, we need those also. We also need data for all the base tables involved as well. We don't all the data, just a sample of the data (as long as all tables involved have related data).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply