oracle query in SSRS
-
Wednesday, February 27, 2013 2:25 PM
How to query in where condition that the field expects single quote to retrieve
Eg:
Select * from ep1 where id_10=:reg_no
:regno value is retrievedby placing single quotes
All Replies
-
Wednesday, February 27, 2013 3:58 PM
Hi,
You really don't need to change anything for your query above.
What you need to do however is relate :reg_no to your parameter.
Say you have a text parameter named pRegNo that will accept the input from the user.
Right click on your dataset, goto properties, goto the parameters tab, add a parameter,
in the parameter name, enter :reg_no
in the parameter value, pull down and select [@pRegNo].
This will run your query fine.
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t z- Proposed As Answer by Mike YinMicrosoft Contingent Staff, Moderator Thursday, February 28, 2013 10:07 AM
- Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Sunday, March 03, 2013 3:30 PM
- Unmarked As Answer by developertest Thursday, March 07, 2013 5:36 AM
-
Tuesday, March 05, 2013 7:26 AM
Hi Krootz,
My query is below and i have 4 parameters. Can you please guide me how to filter with 4 parameters, now i put (OR) between parameters.tHE REG_NO parameter the filed data type is char, so still i canot retrieve.
SELECT DISTINCT TO_CHAR(ep13_7.id_1007,'YYYY/MM/DD') as ID_1007,
Ep13_7.ID_1013,
EP13_7.ID_1016,
EP13_7.ID_1017,
TO_CHAR(EP13_7.ID_1019,'YYYY/MM/DD') AS ID_1019,
TRIM(EP13_7.ID_1021) AS ID_1021,
TRIM(EP13_7.ID_1023) AS ID_1023,
TRIM(EP13_7.ID_1024) AS ID_1024,
EP13_7.ID_1025,EP13_7.ID_1026,EP13_7.ID_1027,EP13_7.ID_1028,EP13_7.ID_1029,TRIM(EP13_7.ID_1030)AS ID_1030,TRIM(EP13_7.ID_1031) AS ID_1031,TRIM(EP13_7.ID_1032)AS ID_1032,TRIM(EP13_7.ID_1033)AS ID_1033,EP13_7.ID_1034,EP13_7.ID_1035 EP13_7.ID_1036, EP13_7.ID_1037,
EP13_7.ID_1038, EP13_7.ID_1039, EP13_7.ID_1040, EP13_7.ID_1041, trim(EP13_7.ID_1044) AS ID_1044, trim(EP13_7.ID_1045) AS ID_1045,
trim(EP13_7.ID_1046) AS ID_1046, trim(EP13_7.ID_1047) AS ID_1047, EP13_7.ID_500, EP13_7.ID_501, EP13_7.ID_502, EP13_7.ID_503,
EP13_7.ID_504, EP13_7.ID_505, EP13_7.ID_506, EP13_7.ID_507, EP13_7.ID_1042, EP13_7.ID_1048, EP13_7.ID_1049, EP13_7.ID_1050,
TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS TD,TO_CHAR(SYSDATE, 'HH:MM') as SYSTME, trim(F.VAL_STR) AS PERSON_RELGN, trim(RP.VAL_STR) AS REG_PLACE
FROM EP13_7 INNER JOIN
(SELECT EP13_7_1.ID_1025, KOVFRM.LKU.VAL_STR, EP13_7_1.ID_1026 AS FCID
FROM KOVFRM.CML INNER JOIN
KOVFRM.FLD ON KOVFRM.CML.CMLID = SUBSTR(KOVFRM.FLD.DISPTYP, 3, 1) INNER JOIN
KOVFRM.LKU ON KOVFRM.CML.SECTION_UPARAM = KOVFRM.LKU."SECTION" INNER JOIN
EP13_7 EP13_7_1 ON KOVFRM.LKU."ENTRY" = EP13_7_1.ID_1025
WHERE (KOVFRM.FLD.FLDID = '1025')) F ON EP13_7.ID_1026 = F.FCID INNER JOIN
(SELECT EP13_7_1.ID_1013, LKU_1.VAL_STR, EP13_7_1.ID_1026 AS RPCID
FROM KOVFRM.CML CML_1 INNER JOIN
KOVFRM.FLD FLD_1 ON CML_1.CMLID = SUBSTR(FLD_1.DISPTYP, 3, 2) INNER JOIN
KOVFRM.LKU LKU_1 ON CML_1.SECTION_UPARAM = LKU_1."SECTION" INNER JOIN
EP13_7 EP13_7_1 ON LKU_1."ENTRY" = EP13_7_1.ID_1013
WHERE (FLD_1.FLDID = '1013')) RP ON EP13_7.ID_1026 = RP.RPCID
WHERE ((ID_1019 is not null or ID_1021 is not null) and (ID_1018 is null and ID_1020 is null)) AND ID_12 <> -1 AND(ID_1026 =:CID OR ID_1019=:DDATE OR ID_1016=:REG_NO OR TRIM(ID_1021):=NAME)
Thanks
-
Tuesday, March 05, 2013 12:08 PM
Hi Krootz,
My query is below and i have 4 parameters. Can you please guide me how to filter with 4 parameters, now i put (OR) between parameters.tHE REG_NO parameter the filed data type is char, so still i canot retrieve.
SELECT DISTINCT TO_CHAR(ep13_7.id_1007,'YYYY/MM/DD') as ID_1007,
Ep13_7.ID_1013,
EP13_7.ID_1016,
EP13_7.ID_1017,
TO_CHAR(EP13_7.ID_1019,'YYYY/MM/DD') AS ID_1019,
TRIM(EP13_7.ID_1021) AS ID_1021,
TRIM(EP13_7.ID_1023) AS ID_1023,
TRIM(EP13_7.ID_1024) AS ID_1024,
EP13_7.ID_1025,EP13_7.ID_1026,EP13_7.ID_1027,EP13_7.ID_1028,EP13_7.ID_1029,TRIM(EP13_7.ID_1030)AS ID_1030,TRIM(EP13_7.ID_1031) AS ID_1031,TRIM(EP13_7.ID_1032)AS ID_1032,TRIM(EP13_7.ID_1033)AS ID_1033,EP13_7.ID_1034,EP13_7.ID_1035 EP13_7.ID_1036, EP13_7.ID_1037,
EP13_7.ID_1038, EP13_7.ID_1039, EP13_7.ID_1040, EP13_7.ID_1041, trim(EP13_7.ID_1044) AS ID_1044, trim(EP13_7.ID_1045) AS ID_1045,
trim(EP13_7.ID_1046) AS ID_1046, trim(EP13_7.ID_1047) AS ID_1047, EP13_7.ID_500, EP13_7.ID_501, EP13_7.ID_502, EP13_7.ID_503,
EP13_7.ID_504, EP13_7.ID_505, EP13_7.ID_506, EP13_7.ID_507, EP13_7.ID_1042, EP13_7.ID_1048, EP13_7.ID_1049, EP13_7.ID_1050,
TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS TD,TO_CHAR(SYSDATE, 'HH:MM') as SYSTME, trim(F.VAL_STR) AS PERSON_RELGN, trim(RP.VAL_STR) AS REG_PLACE
FROM EP13_7 INNER JOIN
(SELECT EP13_7_1.ID_1025, KOVFRM.LKU.VAL_STR, EP13_7_1.ID_1026 AS FCID
FROM KOVFRM.CML INNER JOIN
KOVFRM.FLD ON KOVFRM.CML.CMLID = SUBSTR(KOVFRM.FLD.DISPTYP, 3, 1) INNER JOIN
KOVFRM.LKU ON KOVFRM.CML.SECTION_UPARAM = KOVFRM.LKU."SECTION" INNER JOIN
EP13_7 EP13_7_1 ON KOVFRM.LKU."ENTRY" = EP13_7_1.ID_1025
WHERE (KOVFRM.FLD.FLDID = '1025')) F ON EP13_7.ID_1026 = F.FCID INNER JOIN
(SELECT EP13_7_1.ID_1013, LKU_1.VAL_STR, EP13_7_1.ID_1026 AS RPCID
FROM KOVFRM.CML CML_1 INNER JOIN
KOVFRM.FLD FLD_1 ON CML_1.CMLID = SUBSTR(FLD_1.DISPTYP, 3, 2) INNER JOIN
KOVFRM.LKU LKU_1 ON CML_1.SECTION_UPARAM = LKU_1."SECTION" INNER JOIN
EP13_7 EP13_7_1 ON LKU_1."ENTRY" = EP13_7_1.ID_1013
WHERE (FLD_1.FLDID = '1013')) RP ON EP13_7.ID_1026 = RP.RPCID
WHERE ((ID_1019 is not null or ID_1021 is not null) and (ID_1018 is null and ID_1020 is null)) AND ID_12 <> -1 AND(ID_1026 =:CID OR ID_1019=:DDATE OR ID_1016=:REG_NO OR TRIM(ID_1021):=NAME)
Thanks
-
Tuesday, March 05, 2013 12:33 PM
Hi,
(ID_1026 =:CID OR ID_1019=:DDATE OR ID_1016=:REG_NO OR TRIM(ID_1021):=NAME) --- if the user enters blank on DDATE and your ID_1019 is date type, this will not work. Forget about the parameters for now, if you were to substitute real values to this where clause, what would they be? The OR makes it difficult since in ORACLE, you will have to say IS NULL if the fields are blank.
You will have to convert the entire select into an expression. To make this more simpler, first create a view in ORACLE:
CREATE OR REPLACE VIEW EP13_7_V AS SELECT DISTINCT TO_CHAR(ep13_7.id_1007,'YYYY/MM/DD') as ID_1007, Ep13_7.ID_1013, EP13_7.ID_1016, EP13_7.ID_1017, TO_CHAR(EP13_7.ID_1019,'YYYY/MM/DD') AS ID_1019, TRIM(EP13_7.ID_1021) AS ID_1021, TRIM(EP13_7.ID_1023) AS ID_1023, TRIM(EP13_7.ID_1024) AS ID_1024, EP13_7.ID_1025,EP13_7.ID_1026,EP13_7.ID_1027,EP13_7.ID_1028,EP13_7.ID_1029,TRIM(EP13_7.ID_1030)AS ID_1030,TRIM(EP13_7.ID_1031) AS ID_1031,TRIM(EP13_7.ID_1032)AS ID_1032,TRIM(EP13_7.ID_1033)AS ID_1033,EP13_7.ID_1034,EP13_7.ID_1035 EP13_7.ID_1036, EP13_7.ID_1037, EP13_7.ID_1038, EP13_7.ID_1039, EP13_7.ID_1040, EP13_7.ID_1041, trim(EP13_7.ID_1044) AS ID_1044, trim(EP13_7.ID_1045) AS ID_1045, trim(EP13_7.ID_1046) AS ID_1046, trim(EP13_7.ID_1047) AS ID_1047, EP13_7.ID_500, EP13_7.ID_501, EP13_7.ID_502, EP13_7.ID_503, EP13_7.ID_504, EP13_7.ID_505, EP13_7.ID_506, EP13_7.ID_507, EP13_7.ID_1042, EP13_7.ID_1048, EP13_7.ID_1049, EP13_7.ID_1050, TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS TD,TO_CHAR(SYSDATE, 'HH:MM') as SYSTME, trim(F.VAL_STR) AS PERSON_RELGN, trim(RP.VAL_STR) AS REG_PLACE FROM EP13_7 INNER JOIN (SELECT EP13_7_1.ID_1025, KOVFRM.LKU.VAL_STR, EP13_7_1.ID_1026 AS FCID FROM KOVFRM.CML INNER JOIN KOVFRM.FLD ON KOVFRM.CML.CMLID = SUBSTR(KOVFRM.FLD.DISPTYP, 3, 1) INNER JOIN KOVFRM.LKU ON KOVFRM.CML.SECTION_UPARAM = KOVFRM.LKU."SECTION" INNER JOIN EP13_7 EP13_7_1 ON KOVFRM.LKU."ENTRY" = EP13_7_1.ID_1025 WHERE (KOVFRM.FLD.FLDID = '1025')) F ON EP13_7.ID_1026 = F.FCID INNER JOIN (SELECT EP13_7_1.ID_1013, LKU_1.VAL_STR, EP13_7_1.ID_1026 AS RPCID FROM KOVFRM.CML CML_1 INNER JOIN KOVFRM.FLD FLD_1 ON CML_1.CMLID = SUBSTR(FLD_1.DISPTYP, 3, 2) INNER JOIN KOVFRM.LKU LKU_1 ON CML_1.SECTION_UPARAM = LKU_1."SECTION" INNER JOIN EP13_7 EP13_7_1 ON LKU_1."ENTRY" = EP13_7_1.ID_1013 WHERE (FLD_1.FLDID = '1013')) RP ON EP13_7.ID_1026 = RP.RPCID WHERE ((ID_1019 is not null or ID_1021 is not null) and (ID_1018 is null and ID_1020 is null)) AND ID_12 <> -12. Change your dataset query. So I can help with this, give me the following:
The name of your 4 parameters in SSRS for :CID, :DDATE, :REG_NO, :NAME
The data types of ID_1026, ID_1019, ID_1016 and ID_1021
And I will give you the SQL expression that you need to make this work in Oracle.
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t z -
Tuesday, March 05, 2013 2:27 PM
Hi Krootz,
Thanks for the reply,
Yes ID_1019 is date type and i make Default value to NUll in report parameter properties for all parameters.
Name of the 4 paramters is in arabic
:CID for civilid,:DDATE for date,:REG_NO for Registration Number, :NAME for Name
Datatypes
ID_1026 Numeric
ID_1019 datetime
ID_1016 char
ID_1021 char
Please suggest me the SQl expression and it will be grateful..
Thanks
-
Tuesday, March 05, 2013 2:31 PM
Hi Krootz,
The datatype in report parameters for all set to string.
Thanks
- Marked As Answer by developertest Thursday, March 07, 2013 5:35 AM
- Unmarked As Answer by developertest Thursday, March 07, 2013 5:36 AM
-
Tuesday, March 05, 2013 2:32 PM
Hi Krootz,
Thanks for the reply,
Yes ID_1019 is date type and i make Default value to NUll in report parameter properties for all parameters.
Name of the 4 paramters is in arabic
:CID for civilid,:DDATE for date,:REG_NO for Registration Number, :NAME for Name
Datatypes
ID_1026 Numeric
ID_1019 datetime
ID_1016 char
ID_1021 char
Please suggest me the SQl expression and it will be grateful..
Thanks
Hi Krootz,
The datatype in report parameters for all set to string.
Thanks
-
Tuesday, March 05, 2013 2:40 PM
Hi,
Make sure you have created the view as per my previous post.
Then change the sql query to be like this:
="SELECT * from EP13_7_V where ID_1026 " & IIF(Parameters!civilid.Value=Nothing,"IS NULL",Parameters!civilid.value) & " OR ID_1019 " & IIF(Parameters!date.Value=Nothing,"IS NULL","=TO_DATE('" & Parameters!date.Value & "','MM/DD/YY')") & " OR ID_1016 " & IIF(Parameters!regno.Value=Nothing,"IS NULL","='" & Parameters!regno.value & "'") & " OR ID_1021 " & IIF(Parameters!name.Value=Nothing,"IS NULL","='" & Parameters!name.value & "'")
Copy and paste this including the =, then change the parameter names to the arabic names and the date format MM/DD/YY according to the date format entered in your database. Take note, you are no longer using the :REG_NO, :DDATE, etc but you are now using the Parameters!ArabicParameterName.Value
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t z- Marked As Answer by developertest Wednesday, March 06, 2013 11:56 AM
- Unmarked As Answer by developertest Thursday, March 07, 2013 1:22 PM
-
Wednesday, March 06, 2013 5:37 AM
Hi Krootz,
Sorry Im litle bit confused, first i ll create view in oracle then i should the above query with = in report page.Dont mistake me i never used view before.
Thanks a lot
-
Wednesday, March 06, 2013 11:59 AM
Hi KRootz,
The field civil id is in char datatype and i can retreive by entering the civilid value in the report , the registration number is also char that query is not retrieving in the report. what would be th reason.
Thanks
.png)
