New to SQL

I have a table with a field that is 14 characters long. I have created two more columns with in the same table. I wish to parse each record taking the 14 character field and storing the first 10 characters in one of the newly created fields and take the next 4 characters and store them in the other newly created fields.

Can someone tell me how to do that?Look in BOL (Books Online) at the Left and Right functions.|||I can do the left and right functions. I just don't understand the looping through the records and updating the other fields.|||Run an update query:

UPDATE TableName
SET SecondField = Left(FirstField,10),
ThirdField = Right(FirstField,4)|||Thanks. I was thinking this was going to be more complicated.|||Of course the real question is should you have the other fields in the table, since their values can always be calculated from the first. Generally speaking the answer would be no.

New Time Dimension Role after cube wizard

If I have a data source view consisting of two tables - a fact table and a time dimension table. The fact table has three date columns - transaction begin date, transaction end date, and updated date. When I initially designed the data source view my requirements only called for the trans begin and end dates so I made two joins to the time table from the sales table. I then ran the cube wizard which correctly detected that I intended to use the time table twice for two different Time hierarchies (Roles) on a single time dimension.

I have recently been asked to add the updated date but I cannot figure out how to add a new hierarchy (Role) on the time dimension. Any Ideas?

You should be able to go to the Dimension Usage tab in the cube designer and click on the Add Cube Dimension button on the toolbar (the third button from the left). When the list of dimensions comes up, simply select your Time dimension and add it again. You'll then see the dimension added within the list of dimensions down the left side of the tab, likely with a name like Time (Time 1) or something similar. Now, simply highlight the newly added role-playing dimension, hit F2, and rename it whatever you want. Then, of course, set the appropriate relationship between this new dimension and the measure group(s) within the cube...

HTH,

Dave Fackler

sql

new stored procedure problem...

using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.

CREATE PROCEDURE isp_ap_load_checks
@.p_comp char(2),
@.p_vend char(6),
@.p_user char(12),
@.p_date1 char(8),
@.p_date2 char(8)
as

if (@.p_user <= '')
begin
set @.p_user = 'system'
end

if (@.p_date1 <= '00000000')
begin
set @.p_date1 = '00000000'
end

if (@.p_date2 <= @.p_date1)
begin
set @.p_date2 = '99999999'
end

delete from XAPCHECKS
where xapck_comp = @.p_comp and xapck_vend = @.p_vend and xapck_user = @.p_user

delete from XAPCHKDETAIL
where xapcd_comp = @.p_comp and xapcd_vend = @.p_vend and xapcd_user = @.p_user

insert into XAPCHECKS
select apph_comp, apph_vend, @.p_user,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0')), apph_payck, chm_type, chm_stat, apt_bank,
apph_paymnts, apph_stat

from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id

insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id

from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id

GOPost the DDL for XAPCHKDETAIL.|||here is the sql script for the table...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XAPCHKDETAIL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XAPCHKDETAIL]
GO

CREATE TABLE [dbo].[XAPCHKDETAIL] (
[xapcd_comp] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_vend] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_user] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_check] [int] NOT NULL ,
[xapcd_chk_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapck_check_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_bank] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[xapcd_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IXAPCHKDETAIL0] ON [dbo].[XAPCHKDETAIL]([xapcd_comp], [xapcd_vend], [xapcd_user], [xapcd_check], [xapcd_chk_type], [xapck_check_status], [xapcd_bank]) ON [PRIMARY]
GO|||Visit http://support.microsoft.com/kb/878501 , an article from Microsoft to resolve the problem.

Cheers,
--Riaz

using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.|||Change this:
insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...

...to this:
insert into XAPCHKDETAIL
([xapcd_comp],
[xapcd_vend],
[xapcd_user],
[xapcd_check],
[xapcd_chk_type],
[xapck_check_status],
[xapcd_bank],
[xapcd_type],
[xapcd_id])
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...

ALWAYS enumerate your column names.|||Is there a way that you know of to only load up a distinct record into the XAPCHECKS table. The APPHISTF can have many ID's associated with a single check.
apphistf:
ID 1 check 1
ID 2 check 1
ID 3 check 1
ID 4 check 2
ID 5 check 3
ID 6 check 3
...
what i am trying to accomplish is to get a recap for a vend by check date, check number that doesnt have to tie back to the ID, that is where the XAPCHKDETAIL comes in.|||Either SELECT DISTINCT, or use an aggregate query with MAX or MIN to select one of the IDs to insert.