SQLTeam.com | Weblogs | Forums

Data type conflict one table dont see another

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.

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'

hi

  1. r u sure what you are thinking is correct ?
  2. when you filter ? r u missing anything .. i mean steps in sequence
  3. is it a software glitch ... one time or sometimes
  4. 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
:slight_smile:
:+1:

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

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

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

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

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

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.

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

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

olap test contain dates


that i am reference to

No not like that. Like this

Create table xyz(id int)

Insert into xyz
Select 1

Etc

Like you did earlier

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

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'