Help on a query

Hi,
Iam trying to figure out the query to achieve the output depicted below

create table master (
iss_dtl_seq_nbr int
)

create table child1(
iss_dtl_seq_nbr int,
line_no int
)

create table child2(
iss_dtl_seq_nbr int,
line_no int
)

insert into master
select 1

insert into child1
select 1, 1
insert into child1
select 1, 2

insert into child2
select 1, 1

insert into child2
select 1, 2

insert into child2
select 1, 3

SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
FROM CHILD1, CHILD2, MASTER
WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
AND CHILD1.LINE_NO = CHILD2.LINE_NO
ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO

Expected Output:

ISS_DTL_SEQ_NBR LINE_NO LINE_NO
----- ---- ----
1 1 1
1 2 2
1 NULL 3

Can anybody help with the query to achieve this?

Thanks.On 9 Sep 2004 09:33:21 -0700, Sudhir wrote:

>Hi,
> Iam trying to figure out the query to achieve the output depicted below
>create table master (
>iss_dtl_seq_nbr int
>)
>create table child1(
>iss_dtl_seq_nbr int,
>line_no int
>)
>create table child2(
>iss_dtl_seq_nbr int,
>line_no int
>)
>insert into master
>select 1
>insert into child1
>select 1, 1
>insert into child1
>select 1, 2
>insert into child2
>select 1, 1
>insert into child2
>select 1, 2
>insert into child2
>select 1, 3
>
>SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
>FROM CHILD1, CHILD2, MASTER
>WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
>AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
>AND CHILD1.LINE_NO = CHILD2.LINE_NO
>ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
>Expected Output:
>ISS_DTL_SEQ_NBR LINE_NO LINE_NO
>----- ---- ----
>1 1 1
>1 2 2
>1 NULL 3
>Can anybody help with the query to achieve this?
>Thanks.

Hi Sudhir,

Thanks for providing the statements to recreate your table structure and
data. The following query will produce the expected output:

select coalesce(master.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr) AS
iss_dtl_seq_nbr,
child1.line_no, child2.line_no
from master
inner join child1
on master.iss_dtl_seq_nbr = child1.iss_dtl_seq_nbr
right outer join child2
on child1.iss_dtl_seq_nbr = child2.iss_dtl_seq_nbr
and child1.line_no = child2.line_no
order by child2.line_no, child1.line_no

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this:

select master.iss_dtl_seq_nbr, child1.line_no, child2.line_no
from child1 full outer join child2 on child1.iss_dtl_seq_nbr =
child2.iss_dtl_seq_nbr and child1.line_no = child2.line_no
join master on master.iss_dtl_seq_nbr =
isnull(child1.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr)

Shervin

s.sudhir@.gmail.com (Sudhir) wrote in message news:<80a5355b.0409090833.4ccd6d2b@.posting.google.com>...
> Hi,
> Iam trying to figure out the query to achieve the output depicted below
> create table master (
> iss_dtl_seq_nbr int
> )
> create table child1(
> iss_dtl_seq_nbr int,
> line_no int
> )
> create table child2(
> iss_dtl_seq_nbr int,
> line_no int
> )
> insert into master
> select 1
> insert into child1
> select 1, 1
> insert into child1
> select 1, 2
> insert into child2
> select 1, 1
> insert into child2
> select 1, 2
> insert into child2
> select 1, 3
>
> SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
> FROM CHILD1, CHILD2, MASTER
> WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
> AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
> AND CHILD1.LINE_NO = CHILD2.LINE_NO
> ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
> Expected Output:
> ISS_DTL_SEQ_NBR LINE_NO LINE_NO
> ----- ---- ----
> 1 1 1
> 1 2 2
> 1 NULL 3
> Can anybody help with the query to achieve this?
> Thanks.

Help me getting started

I am new to ssrs,

Iam facing the following problem with reportviewer parameter

1) I have a dataset added to the project under app_code which has the following querry select * from emp

2) In the rdlc i have two columns empname,deptid,i have added a parameter called deptid with type as int

3) I have a webpage with reportviewer i have mapped the reportviewer to the rdlc file and datasource

4)on click of a button i want to display emp details from a particular deptid alone , so i added these lines on click of the button

ReportViewer1.ShowReportBody =true;

ReportParameter rpt =newReportParameter("deptid", Text1.Text);

this.ReportViewer1.LocalReport.SetParameters(newReportParameter[] { rpt });

But stil it is showing all the emp details

HI,dareprasanna:

After you setting the filter parameter to the ReportView, you should make it refresh to see the result.

ReportViewer1.LocalReport.Refresh();

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI Rex Lin,

Even after setting , ReportViewer1.LocalReport.Refresh();

I get all the details instead of getting those employee who are from the corresponding department,

Should i write a seperate method to fetch the employees of the corresponding dept or the reportviewer will automatically fetch the corresponding employees,

If i have not made my question clear in the previous question i will make myself clear now,

1) I added a dataset with the querry select empname,deptid from emp

2) In the rdlc i have added two details column (empname and deptid)
I have added one parameter by right clicking out of rdlc and added a parameter called 'deptid'

3) In the aspx page i have added reportviewer Choosed the rdlc i have created just above this point
I have not done any changes with ObjectDatasource1 which is created on associating reportviewer with rdlc

4) I have added the following piece of code in page load

ReportViewer1.ShowReportBody = true;
ReportParameter rpt = new ReportParameter("deptid", "39");
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rpt });
ReportViewer1.LocalReport.Refresh();

5) Still i get all the employees instead of getting employee from deptid = 39

|||

HI,dareprasanna

Please double check your sql statement and

I'd rather do it in that way:

SELECT LastName, FirstName, EmailAddress, Phone, ContactID
FROM Person.Contact
WHERE (LastName LIKE @.LastName ) AND (Phone LIKE @.Area )

aspx code:

ReportViewer1.ShowReportBody = true;
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;

strLastName = txtLastName.Text.ToString().Trim();
strArea = txtArea.Text.ToString().Trim();

ReportParameter[] param = new ReportParameter[2];
param[0] = new ReportParameter("LastName", strLastName + "%", false);
param[1] = new ReportParameter("Area", strArea + "%", false);
ReportViewer1.LocalReport.SetParameters(param);

ReportViewer1.LocalReport.Refresh();

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI Rex Lin,

Thank you very much for your kind help,

This discussion has definetly given me some idea about ssrs and report viewer control,

Again Thanks a lot

Regards

prasanna

|||

You are welcome, prasanna.

It's my pleasure!