February 18, 2025 at 8:33 am
Code for mssql 2008
DECLARE @CompanyID TINYINT
DECLARE @MemNo decimal
DECLARE @MemberFrom Integer
DECLARE @MemberTo Integer
DECLARE @AreaFrom Integer
DECLARE @AreaTo Integer
DECLARE @status integer
select
@MemNo = 5,
--@MemberFrom = 1,
--@MemberTo = 3395,
--@AreaFrom = 1,
--@AreaTo = 44,
@status = 1
create table #Memberlist(
NoAhlilama int null,
NoAhli int null
)
create table #Memberlist2(
NoAhlilama int null,
NoAhli int null,
Nama varchar(255)
)
create table #Memberlist3(
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255)
)
create table #Memberlist4(
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255)
)
create table #Memberlist5(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255),
FamAgre int null
)
create table #TempPutkan1(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255) null,
RcvSelAreaID varchar(50),
FamAgre int null)
create table #TempPutkan2(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255) null,
RcvSelAreaID2 varchar(50),
IDPutkan2 int null,
FamAgre int null
)
create table #TempPutkan3(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255) null,
RcvRM2 varchar (50) null,
IDPutkan2 int null,
FamAgre int null
)
create table #TempPutkan4(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255) null,
RcvSelUnitPutkan2 varchar (50) null,
IDPutkan2 int null,
FamAgre int null
)
create table #TempPutkan5(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255) null,
TerimaRM15K varchar(50),
TarikhTerima15K datetime,
RujukanRM15K varchar(255) null,
LawRMFee decimal(13,4),
LawID int null,
TarikhLawRMFee datetime,
RujukanLawRMFee varchar(50),
FamAgre int null
)
create table #TempPutkan6(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
Nama varchar(255) null,
RcvRM100K varchar (50) null,
IDPutkan3 int null,
FamAgre int null
)
create table #TempPutkan(
NoAhlilamalev4 int null,
NoAhlilamalev3 int null,
NoAhlilamalev2 int null,
NoAhlilamalev1 int null,
NoAhli int null,
RcvSelAreaID1 varchar(50),
RcvSelAreaID2 varchar(50),
RcvRM varchar(50),
RcvSelUnit2 varchar(50),
Rcv15K varchar(50),
RcvRM100K varchar(50),
Nama varchar(255) null,
FamAgre int null)
insert into #Memberlist
SELECT distinct A.NoAhlilama as lmemid, A.NoAhli
FROM TABLE_ANGGOTA A
WHERE A.NoAhli = @MemNo
ORDER BY A.NoAhlilama
select * from #Memberlist
insert into #Memberlist2
select B.NoAhlilama, B.NoAhli, B.Nama
from table_anggota B, #Memberlist
where B.NoAhlilama = #Memberlist.NoAhlilama
and B.NoAhli = #Memberlist.NoAhli
order by B.NoAhli
select * from #Memberlist2
insert into #Memberlist3
SELECT
(select NoAhliLama
from table_anggota
where (NoAhli = #Memberlist2.NoAhliLama)) as NoAhliLama,
isnull(#Memberlist2.NoAhlilama,0), C.NoAhli, C.Nama
from table_anggota C, #Memberlist2
where C.NoAhlilama = #Memberlist2.NoAhlilama
and C.NoAhli = #Memberlist2.NoAhli
group BY #Memberlist2.NoAhliLama, C.NoAhli, C.Nama
order by C.NoAhli
select * from #Memberlist3
insert into #Memberlist4
SELECT
(select isnull(E.NoAhliLama,0)
from table_anggota E
where (E.NoAhli = #Memberlist3.NoAhlilamalev2)) as NoAhliLama,
isnull(#Memberlist3.NoAhlilamalev2,0), isnull(#Memberlist3.NoAhlilamalev1,0), D.NoAhli, D.Nama
from table_anggota D, #Memberlist3
where D.NoAhlilama = #Memberlist3.NoAhlilamalev1
and D.NoAhli = #Memberlist3.NoAhli
group BY #Memberlist3.NoAhlilamalev2, #Memberlist3.NoAhlilamalev1, D.NoAhli, D.Nama
order by D.NoAhli
select * from #Memberlist4
insert into #Memberlist5
SELECT
(select isnull(NoAhliLama,0)
from table_anggota
where (NoAhli = #Memberlist4.NoAhlilamalev3)) as NoAhliLama,
isnull(#Memberlist4.NoAhlilamalev3,0), isnull(#Memberlist4.NoAhlilamalev2,0), isnull(#Memberlist4.NoAhlilamalev1,0), E.NoAhli, E.Nama, E.PerjanjianKeluargaP3
from table_anggota E, #Memberlist4
where E.NoAhlilama = #Memberlist4.NoAhlilamalev1
and E.NoAhli = #Memberlist4.NoAhli
group by #Memberlist4.NoAhlilamalev3, #Memberlist4.NoAhlilamalev2, #Memberlist4.NoAhlilamalev1, E.NoAhli, E.Nama, E.PerjanjianKeluargaP3
order by E.NoAhli
select * from #Memberlist5
/*
drop table #Memberlist
drop table #Memberlist2
drop table #Memberlist3
drop table #Memberlist4
drop table #Memberlist5
drop table #TempPutkan1
drop table #TempPutkan2
drop table #TempPutkan3
drop table #TempPutkan4
drop table #TempPutkan5
drop table #TempPutkan6
drop table #TempPutkan
*/
February 18, 2025 at 5:01 pm
Do you really need to create 12 temporary tables to demonstrate the problem?
Also, you should use the {;} Code button to format your code. It makes it much easier to read.
Most people are loath to open documents from random posters.
We cannot run your code, because you are populating your temporary tables from a table that WE DO NOT HAVE ACCESS TO.
My guess is that you are expecting the ISNULL() in your subquery to replace a null value with 0, but that the subquery is returning ZERO rows, so there is no field value to return, thus you are getting a NULL.
INSERT INTO #Memberlist4
SELECT
( -- Your subquery.
SELECT ISNULL(E.NoAhliLama, 0)
FROM table_anggota E
WHERE (E.NoAhli = #Memberlist3.NoAhlilamalev2)
) AS NoAhliLama
, ISNULL(( -- Put subquery in ISNULL() instead
SELECT E.NoAhliLama
FROM table_anggota E
WHERE (E.NoAhli = #Memberlist3.NoAhlilamalev2)
), 0) AS NoAhliLama
, ISNULL(#Memberlist3.NoAhlilamalev2, 0)
, ISNULL(#Memberlist3.NoAhlilamalev1, 0)
, D.NoAhli
, D.Nama
FROM table_anggota D
, #Memberlist3
WHERE D.NoAhlilama = #Memberlist3.NoAhlilamalev1
AND D.NoAhli = #Memberlist3.NoAhli
GROUP BY #Memberlist3.NoAhlilamalev2
, #Memberlist3.NoAhlilamalev1
, D.NoAhli
, D.Nama
ORDER BY D.NoAhli;
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 19, 2025 at 6:13 am
Mr. Drew Allen is correct, the subquery is not returning any row(s) to apply ISNULL() function, but why ?
let me dive even more deeper in your code,
SELECT distinct A.NoAhlilama as lmemid, A.NoAhli
FROM TABLE_ANGGOTA A
WHERE A.NoAhli = @MemNo
this is the place where your query fails initially, you should check the @MemNo Variable's value (5) and appropriate data in the main table (check for table_angotta's NoAhli column has any value of "5"), once you find this, then all your further insert queries will be answered.
Hope this helps you...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy