display null records

Question

User1897897189 posted

dear all,

i have follwoing table

REGION  date_apprvd    sal
a           20130101    1000
a      20130201    2000
a      20130301    3000
b      20140101    4000
b      20140201    5000
b      20140301    6000
c      20130101    7000
c      20130201    8000
c      20130301    9000
d      20130101    1100
d      20130201    2100
d      20130301    3100
e      20140101    4100
e      20140201    5100
e      20140301    6100


pls help me to create a report

input is 20130101

region      sal      count
a           1000          1
b           0               0
c           7000          1
d           1100         1
e            0              0

thanks

nick
   

Answer 1

User-1716253493 posted
Maybe you can use isnull(fieldname, 'text')

Answer 2

User1897897189 posted

could you please give me an example

Answer 3

User143067745 posted

Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

(Select  Region,date_apprvd, count(*) as 'COUNT' from TABLE_NAME  where date_apprvd='VALUE' Group By  date_apprvd) tempFirst

Left Join

(Select  date_apprvd, isnull(sal,0) as 'SAL'  from  TABLE_NAME where date_apprvd='VALUE') tempSecond

on  tempFirst.date_apprvd=tempSecond.date_apprvd) tbl;

Try this query after replacing BOLD content by appropriate values.

Answer 4

User1897897189 posted

it says

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from
(Select  Region,date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By  date_apprvd)
tempFirst Left Join
(Select  date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101')tempSecond
on  tempFirst.date_apprvd=tempSecond.date_apprvd) tbl

Answer 5

User143067745 posted

Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

(

(Select  Region,date_apprvd, count(*) as 'COUNT' from TABLE_NAME  where date_apprvd='VALUE' Group By  date_apprvd) tempFirst

Left Join

(Select  date_apprvd, isnull(sal,0) as 'SAL'  from  TABLE_NAME where date_apprvd='VALUE') tempSecond

on  tempFirst.date_apprvd=tempSecond.date_apprvd

) tbl;

Sry I made a silly mistake.

Answer 6

User1897897189 posted

infact i tried this before posting

it says

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'tbl'.

Answer 7

User143067745 posted

Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

(

(Select  Region,date_apprvd, count(*) as 'COUNT' from TABLE_NAME  where date_apprvd='VALUE' Group By  date_apprvd) tempFirst

Left Join

(Select  date_apprvd, isnull(sal,0) as 'SAL'  from  TABLE_NAME where date_apprvd='VALUE') tempSecond

on  tempFirst.date_apprvd=tempSecond.date_apprvd

) ;

Answer 8

User1897897189 posted

Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

(

(Select  Region,date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By Region, date_apprvd) tempFirst

Left Join

(Select  date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101') tempSecond

on  tempFirst.date_apprvd=tempSecond.date_apprvd

) ;

THE RESULT IS

a      1000    1
a      7000    1
a      1100    1
c      1000    1
c      7000    1
c      1100    1
d      1000    1
d      7000    1
d      1100    1

WHICH IS WRONG

nick

Answer 9

User143067745 posted

Select  tempSecond.Region,tempSecond.SAL,tempFirst.COUNT  from

(

(Select  date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By date_apprvd) tempFirst

Left Join

(Select  Region,date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101') tempSecond

on  tempFirst.date_apprvd=tempSecond.date_apprvd

) ;

Answer 10

User1897897189 posted

my expected op is

region      sal      count
a           1000          1
b           0               0
c           7000          1
d           1100         1
e            0              0

Answer 11

User143067745 posted

I realy very sry. One last time:

Select  tempSecond.Region,tempSecond.SAL,tempFirst.COUNT  from

(
(Select  Region,date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101') tempSecond

Left Join
(Select  date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By date_apprvd) tempFirst


on  tempSecond.date_apprvd=tempFirst.date_apprvd

) ;

Answer 12

User1897897189 posted

i really appreciate your help, no need for any sorry...

still its not giving the expected op. following is the op it generated

a      1000    3
c      7000    3
d      1100    3

thanks

nick

Answer 13

User-578610739 posted

Hi Nicklibee,

Please explain about your output. Out of 3 or 4 similar data, you want a one recod , the output logic is not fulfill to understand. i.e.

a           20130101    1000
a      20130201    2000
a      20130301    3000

for this 3  row , your output will  a           1000          1

for b your outpu will be     b           0               0 

It is not understand the logic. Please elaborate more.

Answer 14

User1897897189 posted

Dear all,

I have 3 tables and all the 3 tables are joined and created a command objet in crystal reports

Following are the tables and the command code.

My expected op is mentioned at the last.

Pls help me to create a report.


table a1

empno ename

1    a
2    b
3    c

table a2

empno dob

1    1
2    2
3    3

table a3

dob djoin

1    20130101
1    20130201
1    20130301
2    20140101
2    20140201
2    20140301
3    20150101
3    20150201
3    20150301

SELECT     dbo.A1.empno, dbo.A1.ename, dbo.A2.dob, dbo.A3.djoin
FROM         dbo.A3 RIGHT OUTER JOIN
                      dbo.A2 ON dbo.A3.dob = dbo.A2.dob RIGHT OUTER JOIN
                      dbo.A1 ON dbo.A2.empno = dbo.A1.empno

empno ename dob djoin

1    a    1    20130101
1    a    1    20130201
1    a    1    20130301
2    b    2    20140101
2    b    2    20140201
2    b    2    20140301
3    c    3    20150101
3    c    3    20150201
3    c    3    20150301



ip parameter is djoin
say 20130101 to 20130101
i want the following op

empno        empname        count
1        a        1
2        b        0
3        c        0

Thanks
Nick

Answer 15

User-578610739 posted

Hi Nick,

If you post in sqlserver ,then you get the result faster. Any way -Its very easy. I create a sample sqlquery, just run is sqlserver

declare @a1 table (empno int, ename varchar(50))
declare @a2 table (empno int, dob int)
declare @a3 table (dob int, djoin varchar(50))

insert into  @a1 values(1,'a')
insert into  @a1 values(2,'b')
insert into  @a1 values(3,'c')

insert into  @a2 values(1,1)
insert into  @a2 values(2,2)
insert into  @a2 values(3,3)

insert into  @a3 values(1,'20130101')
insert into  @a3 values(1,'20130101')
insert into  @a3 values(1,'20130101')
insert into  @a3 values(2,'20140101')
insert into  @a3 values(2,'20140101')
insert into  @a3 values(2,'20140301')
insert into  @a3 values(3,'20150101')
insert into  @a3 values(3,'20150101')
insert into  @a3 values(3,'20150101')


SELECT     A1.empno, A1.ename, A2.dob, A3.djoin
FROM   @A3 a3
RIGHT OUTER JOIN @A2 a2 ON A3.dob = A2.dob 
RIGHT OUTER JOIN @A1 a1 ON A2.empno = A1.empno

DECLARE @PassDt varchar(50)= '20130101'

SELECT A1.empno, A1.ename, COUNT(djoin)
FROM   @A1 a1 
LEFT OUTER JOIN @A2 a2 ON A2.empno = A1.empno
LEFT OUTER JOIN @A3 a3 ON A3.dob = A2.dob  AND ( a3.djoin = @passdt  or @PassDt is null)
group by A1.empno, A1.ename