Data type conflict one table dont see another

Deimos_farm · February 5, 2021, 9:18am

Good day come across of problem i have a formula that reference information from another table but when i am trying filtering by this information it show no resolts with it with means it dont see the information in second table all data type is same on both tables. What can be a roblem.

yosiasz · February 5, 2021, 2:08pm

It will be hard to answer that without some sample data. please provide sample DDL and DML

create table somied(id int, name nvarchar(50) )

insert into somied
select 1, 'Deimos'

create table table2(id int, activity varchar(50)) 

insert into table2
select 1, 'Run'

harishgg1 · February 5, 2021, 2:49pm

hi

r u sure what you are thinking is correct ?
when you filter ? r u missing anything .. i mean steps in sequence
is it a software glitch ... one time or sometimes
is the data there in the second table or has it been deleted

how to debug
5) try creating other tables and see

Hope this helps

ScottPletcher · February 5, 2021, 3:45pm

Check to see if the collation is different on the two dbs.

Deimos_farm · February 6, 2021, 1:34am

i hope it will help
CREATE TABLE [OLAP].[deimos](
[Name] [nvarchar(600) NOT NULL,
[Source] [nvarchar(600) NOT NULL,
[AllocateToTc] [nvarchar(600) NULL,
[Percent] [nvarchar(600) NULL,
[Date] nvarchar(600) NULL,
[AllocateFromTc nvarchar(600) NULL
) ON [PRIMARY]
GO
INSERT INTO [OLAP].[deimos]
([Name]
,[Source]
,[AllocateToTc]
,[Percent]
,[Date]
,[AllocateFromTc])
VALUES ('1','User','Houses: N','25','1/11/2020','Default Category: Default Option'),
('2','User','Houses: NL','25','1/11/2020','Default Category: Default Option'),
('3','User','Houses: N-02-GR','25','1/11/2020','Default Category: Default Option'),
('4','User','Houses: N-@Home','20','1/12/2020','Default Category: Default Option'),
('5','User','Houses: N-01-KL','10','1/12/2020','Default Category: Default Option'),
('6','User','Houses: N2-GR','50','1/12/2020','Default Category: Default Option'),
('7','User','Houses: N-BR','20','1/12/2020','Default Category: Default Option')

the second tayble i am trying to reference is just date also in format of nvarchar
CREATE TABLE [OLAP].[Month](
[ID] [int] NOT NULL,
[Name] nvarchar(600) NOT NULL,
[Source] nvarchar(600) NOT NULL,
[Date] nvarchar(600) NULL,
[DayOfYear] [nvarchar(600) NULL,
[Day] nvarchar]600) NULL,
[Month] nvarchar(600) NULL,
[Year] [nvarchar(600) NULL,
[DayOfWeek] [nvarchar(600) NULL,
[DayOfWeekAbr] [nvarchar(600) NULL,
[WeekOfYear] [nvarchar(600) NULL,
[Quarter] [nvarchar(600) NULL,
[MonthName] [nvarchar(600) NULL,
[MonthNameAbr] [nvarchar600) NULL,
[MonthEndDate] [nvarchar(600) NULL,
[YearEndDate] [nvarchar(600) NULL,
[FinancialMonth] [nvarchar(600) NULL,
[FinancialYear] [nvarchar(600) NULL,
[Year_Month] [nvarchar(600) NULL,
CONSTRAINT [PK__Month__ID] PRIMARY KEY CLUSTERED
(

formala that i am trying to male work look like this
select
A1.Month as Date,
A1.Scenario,
A1.Account,
A1.Organisation,
A1.Tracking_Category_2,
A1.Tracking_Category_1,
case
when A1.Month = B2.Date
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else a1.Tracking_Category_1
end as Tracking_Category_d,
A1.TEXT_VAL,
case
when A1.Tracking_Category_1 = AllocateFromTc
and A1.Month = B2.Date
then A1.Amount*[Percent]/100
else A1.Amount
end as Amountd
,A1.Amount

from OLAP.VW_CUBE_Finance_NAMES A1 , OLAP.Test B2
where A1.Month = B2.Date

but dont work becouse it dont see A1.Month = B2.Date

Deimos_farm · February 6, 2021, 1:39am

thaks it did elp idenafay a problem better but still gesing about solution

yosiasz · February 6, 2021, 2:41am

You need to provide some data for [OLAP].[Month] table. You have it and know about it, we don't. Help us help you

Deimos_farm · February 7, 2021, 12:03am

thanks sorry for poor frazing i am new to all of this
here how olap month table look like
CREATE TABLE [OLAP].[Month](
[ID] [int] NOT NULL,
[Name] [nvarchar(600) NOT NULL,
[Source] [nvarchar(600) NOT NULL,
[Date] [nvarchar(600) NULL,
[DayOfYear] [nvarchar(600) NULL,
[Day] [nvarchar(600) NULL,
[Month] [nvarchar(600) NULL,
[Year] [nvarchar(600) NULL,
[DayOfWeek] [nvarchar(600) NULL,
[DayOfWeekAbr] [nvarchar(600) NULL,
[WeekOfYear] [nvarchar(600) NULL,
[Quarter] [nvarchar(600) NULL,
[MonthName] [nvarchar(600) NULL,
[MonthNameAbr] [nvarchar(600) NULL,
[MonthEndDate] [nvarchar(600) NULL,
[YearEndDate] [nvarchar(600) NULL,
[FinancialMonth] [nvarchar(600) NULL,
[FinancialYear] [nvarchar(600) NULL,
[Year_Month] [nvarchar(600) NULL,
CONSTRAINT [PK__Month__ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UQ__Month__Name] UNIQUE NONCLUSTERED
(
[Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [OLAP].[Month] ADD CONSTRAINT [DF__Month__Source] DEFAULT ('User') FOR [Source]
GO

ALTER TABLE [OLAP].[Month] WITH CHECK ADD CONSTRAINT [CK__Month__Source] CHECK (([Source]='User' OR [Source]='System'))
GO

ALTER TABLE [OLAP].[Month] CHECK CONSTRAINT [CK__Month__Source]

and tha how its look in

yosiasz · February 7, 2021, 1:11am

not helpful, but we can sort that out looks like a common calendar table. Now show us the query you are running that is not producing the result you want.

Deimos_farm · February 7, 2021, 2:32am

CREATE VIEW [OLAP].[VW_Deimos_view6]
AS
select
A1.Month as Date,
A1.Scenario,
A1.Account,
A1.Organisation,
A1.Tracking_Category_2,
A1.Tracking_Category_1,
case
when A1.Month = B2.Date
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else a1.Tracking_Category_1
end as Tracking_Category_d,
A1.TEXT_VAL,
case
when A1.Tracking_Category_1 = AllocateFromTc
and A1.Month = B2.Date
then A1.Amount*[Percent]/100
else A1.Amount
end as Amountd
,A1.Amount

from OLAP.VW_CUBE_Finance_NAMES A1 , OLAP.Test B2
where A1.Month = B2.Date

yosiasz · February 7, 2021, 2:35am

Every post is a surprise, now you have a table OLAP.VW_CUBE_Finance_NAMES

Deimos_farm · February 7, 2021, 3:20am

olap test contain dates

that i am reference to

yosiasz · February 7, 2021, 3:22am

No not like that. Like this

Create table xyz(id int)

Insert into xyz
Select 1

Etc

Like you did earlier

Deimos_farm · February 7, 2021, 3:33am

like this

CREATE VIEW [OLAP].[VW_CUBE_Finance_NAMES]
AS
SELECT
A1.NAME as [Month],
A2.NAME as [Scenario],
A3.NAME as [Account],
A4.NAME as [Organisation],
A5.NAME as [Tracking_Category_1],
A6.NAME as [Tracking_Category_2],
C2.TEXT_VAL,
C2.Amount
FROM [OLAP].[CUBE_Finance_WB] c2
Inner JOIN [OLAP].[Month]A1
ON A1.ID = C2.[Month]
Inner JOIN [OLAP].[Scenario]A2
ON A2.ID = C2.[Scenario]
Inner JOIN [OLAP].[Account]A3
ON A3.ID = C2.[Account]
Inner JOIN [OLAP].[Organisation]A4
ON A4.ID = C2.[Organisation]
Inner JOIN [OLAP].[Tracking_Category_1]A5
ON A5.ID = C2.[Tracking_Category_1]
Inner JOIN [OLAP].[Tracking_Category_2]A6
ON A6.ID = C2.[Tracking_Category_2]

GO

yosiasz · February 7, 2021, 3:51am

Yet another surprise. Well, we tried. Hope someone else can help you

Anyways you have the dates in Month as 2021-01-01 format but your query does <> '01/01/2021'. Because both columns are varchar(600) its not going to work unless you do a cast on the where clause (very bad idea) or change the data types to date. Its not a date column, its varchar


drop table [deimos]
drop table Months

CREATE TABLE [deimos](
[Name] nvarchar(600) NOT NULL,
[Source] nvarchar(600) NOT NULL,
[AllocateToTc] nvarchar(600) NULL,
[Percent] nvarchar(600) NULL,
[Date] nvarchar(600) NULL,
[AllocateFromTc] nvarchar(600) NULL
) ON [PRIMARY]
GO
INSERT INTO [deimos]
([Name]
,[Source]
,[AllocateToTc]
,[Percent]
,[Date]
,[AllocateFromTc])
VALUES ('1','User','Houses: N','25','1/11/2020','Default Category: Default Option'),
('2','User','Houses: NL','25','1/11/2020','Default Category: Default Option'),
('3','User','Houses: N-02-GR','25','1/11/2020','Default Category: Default Option'),
('4','User','Houses: N-@Home','20','1/12/2020','Default Category: Default Option'),
('5','User','Houses: N-01-KL','10','1/12/2020','Default Category: Default Option'),
('6','User','Houses: N2-GR','50','1/12/2020','Default Category: Default Option'),
('7','User','Houses: N-BR','20','1/12/2020','Default Category: Default Option')


CREATE TABLE [Months](
[Date] nvarchar(600) NULL)


insert into Months
select '2020-11-01'

select * 
  From deimos a
  join months m on a.Date = m.Date
where a.[Date] = '2020-11-01'