oracle newbie
ok, i am having trouble with a simple query. in oracle worksheet, this produces 11 records (what it should be) SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir FROM tbl_shp WHERE asn_ir IS null but the same statement in CF produces 0 records... this is the first time i have played with oracle, so am i just having a bad brain day or what? ~~ Stephenie ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137730 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: oracle newbie
Those have to be the most obfuscated column names I've ever seen :) - Jim Stephenie Hamilton wrote: >ok, i am having trouble with a simple query. >in oracle worksheet, this produces 11 records (what it should be) > >SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir >FROM tbl_shp >WHERE asn_ir IS null > >but the same statement in CF produces 0 records... >this is the first time i have played with oracle, so am i just having >a bad brain day or what? >~~ >Stephenie > > > ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137731 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
RE: oracle newbie
You may need to prefix the table name with the correct schema: SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir FROM MySchema.tbl_shp WHERE asn_ir IS null -Dain -Original Message- From: Stephenie Hamilton [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2003 3:19 PM To: CF-Talk Cc: Subject:oracle newbie ok, i am having trouble with a simple query. in oracle worksheet, this produces 11 records (what it should be) SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir FROM tbl_shp WHERE asn_ir IS null but the same statement in CF produces 0 records... this is the first time i have played with oracle, so am i just having a bad brain day or what? ~~ Stephenie ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137732 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
RE: oracle newbie
Stephenie I've never had your exact problem, but I have experience difficulty with NULL values in Oracle from ColdFusion using the default JDBC/ODBC drivers. I have several times created queries that would throw JDBC/ODBC errors since I was selecting fields that contained NULL values particularly date fields with NULL values. I resolved this problem this week by installing the Oracle JDBC Thin Client, and setting up a datasource to use that instead. Resolved my misbehaving query at least. HTH -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA > -Original Message- > From: Stephenie Hamilton [mailto:[EMAIL PROTECTED] > Sent: Friday, September 19, 2003 12:19 PM > To: CF-Talk > Subject: oracle newbie > > > ok, i am having trouble with a simple query. > in oracle worksheet, this produces 11 records (what it should be) > > SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir > FROM tbl_shp > WHERE asn_ir IS null > > but the same statement in CF produces 0 records... > this is the first time i have played with oracle, so am i just having > a bad brain day or what? > ~~ > Stephenie > > > ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137734 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
RE: oracle newbie
which should throw an error if this is required, should it not? Unless there are two tables, one under the schema and one without ala a synonym. Stephanie, out of curiosity can you use RDS to browse this table? Can you run the SQL in CFStudio's query tool? Doug >-Original Message- >From: Dain Anderson [mailto:[EMAIL PROTECTED] >Sent: Friday, September 19, 2003 3:24 PM >To: CF-Talk >Subject: RE: oracle newbie > > >You may need to prefix the table name with the correct schema: > >SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir >FROM MySchema.tbl_shp >WHERE asn_ir IS null > >-Dain > >-Original Message- >From: Stephenie Hamilton [mailto:[EMAIL PROTECTED] >Sent: Fri 9/19/2003 3:19 PM >To:CF-Talk >Cc: >Subject: oracle newbie >ok, i am having trouble with a simple query. >in oracle worksheet, this produces 11 records (what it should be) > >SELECT shp_id, spr_nr, loc_cd, pkg_id, csn_na, xcp_cd, act_cd, asn_ir >FROM tbl_shp >WHERE asn_ir IS null > >but the same statement in CF produces 0 records... >this is the first time i have played with oracle, so am i just having >a bad brain day or what? >~~ >Stephenie > > > > ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137735 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
Re: oracle newbie
Can you install a third-party tool like Aqua Data or TOAD? That might be a little friendlier. - Jim Stephenie Hamilton wrote: >I am not sure what the schema name is, they didn't tell me that...any hints on how i >could find it using oracle worksheet? > >I cannot use RDS (corporate rules...arg) so the only way I can even look at the db is >with oracle worksheet, which is doesn't show me much compared to SQL Ent. Mgr (which >is what i worked with the last 3 years.) > >~~ >Steph > > > > > >>which should throw an error if this is required, should it not? >>Unless there are two tables, one under the schema and one without ala >>a synonym. >> >>Stephanie, out of curiosity can you use RDS to browse this table? Can >>you run the SQL in CFStudio's query tool? >> >>Doug >> >> >> > ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137738 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: oracle newbie
you have debugging on? Is the SQL correct there? Cached query? Is the cfm page cached inadvertantly by CF? The user for the DSN is the same as in this 'oracle worksheet' and teh same DB actually? I know, some cheesy easy questions, but you never know. I've been known to bang my head for an hour about some SQL not working right only to find I had cachedwithin set in the cfquery tag. DOH! I've not seen any issues with null, SQL, and the JDBC driver that ships with CFMX. Its Friday 4:00 EST...time to give up and hit happy hour maybe? ^_^ Doug >-Original Message- >From: Stephenie Hamilton [mailto:[EMAIL PROTECTED] >Sent: Friday, September 19, 2003 3:43 PM >To: CF-Talk >Subject: Re:oracle newbie > > >I am not sure what the schema name is, they didn't tell me >that...any hints on how i could find it using oracle worksheet? > >I cannot use RDS (corporate rules...arg) so the only way I can >even look at the db is with oracle worksheet, which is doesn't >show me much compared to SQL Ent. Mgr (which is what i worked >with the last 3 years.) > >~~ >Steph > > > >>which should throw an error if this is required, should it not? >>Unless there are two tables, one under the schema and one without ala >>a synonym. >> >>Stephanie, out of curiosity can you use RDS to browse this >table? Can >>you run the SQL in CFStudio's query tool? >> >>Doug >> > ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137743 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
Re: oracle newbie
>>i had no control over the db at all. These obviously were set by some database "administrator" ;-) Never would a programer use such messy column names ;-) ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137753 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
