how to do in crystal report using store procedure

Question

0.00/5 (No votes)

See more:

i have 2 table name city and state:
city table field:
city_id,city_name,state_id

state table field:
state_id,state_name,country_id

i create store procedure:

SQL

ALTER PROCEDURE [dbo].[testreport]
AS
BEGIN
    select * from City_master
    union
    select * from State_master
END

when i add this to crystal report
only city_master column are display in database field

how can i see all 6 of column in database field

i does not know more about crystal report

thanks in advance.....

Posted 8-Dec-13 4:00am

priyanshbhaliya

Add a Solution

Comments

priyanshbhaliya 8-Dec-13 10:18am

can i use to different store procedure????
1 for state
2 for city

JoCodes 8-Dec-13 10:42am

To use Union operator you should have same column names...Use a join instead

[no name] 8-Dec-13 10:54am

Union in conjunction with select * is a bad idea anyway

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

JoCodes · Answer 1 · 2013-12-08T04:50:00

Solution 1

Union Operator wont solve your problem .

You should use Join operator

Posted 8-Dec-13 4:50am

JoCodes

Comments

[no name] 8-Dec-13 10:55am

Union in conjunction with select x.* is a bad idea anyway

priyanshbhaliya 8-Dec-13 11:12am

ok

[no name] 8-Dec-13 11:43am

Excuse me please, that was a little superficial.What I like to say is, with union I prefer to list the fields of both seletcs explicitly.

JoCodes 8-Dec-13 23:52pm

Union needs to satisfy conditions such as number of fields, datatype, fields etc to be same in both the tables.

SHAHRIMA SULTANA · Answer 2 · 2013-12-08T05:37:00

Solution 2

For Crystal report instead of using store procedure you can use View also.
Please try following code:

SQL

CREATE VIEW [dbo].[vw_testreport]
AS
select c.*,s.state_name,s.country_id from City_master c
inner join State_master s
on s.state_id=c.state_id

Posted 8-Dec-13 5:37am

SHAHRIMA SULTANA

Updated 8-Dec-13 8:56am

v5