Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing parameter from Table View from Oracle returns empty result Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 8:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:28 AM
Points: 8, Visits: 71
Hi All,

I have spent few hours looking into the issue and still cannot figure this one out.

I have created parameter @OrgNumber which I am getting the available values from View(Oracle - connected via OLEDB) and passing it to query to generate report but it returns empty result. Below is i am using ..

SELECT CHANNEL_ID, CHANNEL_CODE, ORGNO, TEAM_CODE, CHANNEL_NAME, CHANNEL_GRADE, CHANNEL_TYPE, CHANNEL_TYPE_NAME, PARENT_ID, STATUS,
LEADER_ID, LEADER_THAI_FULL_NAME, ACNT_ORG_YN, CHANNEL_GRADE_NAME, ZIP, HOUSE_NO, VILLAGE, MOO, SOI, STREET, SUBDISTRICT, DISTRICT, CITY,
TELEPHONE, INSERTED_BY, INSERT_TIME, UPDATED_BY, UPDATE_TIME
FROM DEV_DATAHUB.DH_V_ISTRUCTURE
WHERE (ORGNO = ('@OrgNumber'))

My question is when I insert the parameter it has single quotes wrapped around parameter name. From Oracle the data type is VARCHAR and I am setting value type as tect in ssrs. ANy suggestions? please help.

Thanks,
J

Post #1495741
Posted Wednesday, September 18, 2013 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 10,268, Visits: 13,245
You need to get rid of the single-quotes and replace the @ with a :. Check out this blog post



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1496107
Posted Thursday, September 19, 2013 5:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:37 PM
Points: 1,110, Visits: 2,378
Jack is correct, for Oracle you need a colon : for parameters.
Post #1496310
Posted Wednesday, January 15, 2014 2:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:14 PM
Points: 18, Visits: 148
Hi

This link looks useful, but unfortunatly it suggests using '+' as a concatination, which Oracle doesn't support, and it has the parameter without single quotes which oracle expects to be an object name not a literal string so basically it doesn't work
Post #1531327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse