RE: what to put in place of null
On second thought, to_char and to_number as well, I just prefer '' for chars. So, never mind. (And I just told Lisa that I was NOT an idiot, not today at least. That was then, this is now.) > -Original Message- > From: Yosi > Sent: Tuesday, June 05, 2001 7:18 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: what to put in place of null > > > Don't think so. Use: > > ... > union > select '', '', dd2, dd3 > / > > The empty string is equivalent to null for inserts and updates. > > hth, > > Yosi > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, June 05, 2001 6:14 PM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: what to put in place of null > > > > > > Try applying data conversion function to_char/to_date or nvl > > function if dd > > and dd1 are numbers to the nulls in the second select. > > Example: > > > > cursor c1 is > > select dd,dd1,dd2,dd3 from dd > > union > > select to_char(null),to_char(null),dd2,dd3 > > from dd1; > > > > The point here is you need to have as many columns in your > > second select > > statement as your first select has and the datatypes have > to match. > > > > -Original Message- > > Sent: Tuesday, June 05, 2001 5:31 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi, > > > > I have a cursor declared as > > cursor c1 is > > select dd,dd1,dd2,dd3 from dd > > union > > select null,null,dd2,dd3 from dd1; > > > > i am getting error: > > * > > ERROR at line 1: > > ORA-01790: expression must have same datatype as > > corresponding expression > > ORA-06512: at "DY.REST", line 4 > > ORA-06512: at "DY.REST", line 10 > > ORA-06512: at line 1 > > > > what to replace null with.. > > dd1 has only 2 columns dd2 and dd3. > > > > Thanks > > Harvinder > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Harvinder Singh > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / > Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / > Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: what to put in place of null
I believe the empty string is not guaranteed to be the same as a null in the future -- perhaps new ANSI standards? It's much safer to cast the null using to_char, to_date, etc. as suggested earlier. Marc Perkowitz Senior Consultant TWJ Consulting, LLP 847-256-8866 x15 www.twjconsulting.com - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, June 05, 2001 6:18 PM > Don't think so. Use: > > ... > union > select '', '', dd2, dd3 > / > > The empty string is equivalent to null for inserts and updates. > > hth, > > Yosi > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, June 05, 2001 6:14 PM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: what to put in place of null > > > > > > Try applying data conversion function to_char/to_date or nvl > > function if dd > > and dd1 are numbers to the nulls in the second select. > > Example: > > > > cursor c1 is > > select dd,dd1,dd2,dd3 from dd > > union > > select to_char(null),to_char(null),dd2,dd3 > > from dd1; > > > > The point here is you need to have as many columns in your > > second select > > statement as your first select has and the datatypes have to match. > > > > -Original Message- > > Sent: Tuesday, June 05, 2001 5:31 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi, > > > > I have a cursor declared as > > cursor c1 is > > select dd,dd1,dd2,dd3 from dd > > union > > select null,null,dd2,dd3 from dd1; > > > > i am getting error: > > * > > ERROR at line 1: > > ORA-01790: expression must have same datatype as > > corresponding expression > > ORA-06512: at "DY.REST", line 4 > > ORA-06512: at "DY.REST", line 10 > > ORA-06512: at line 1 > > > > what to replace null with.. > > dd1 has only 2 columns dd2 and dd3. > > > > Thanks > > Harvinder > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Harvinder Singh > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marc Perkowitz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what to put in place of null
Don't think so. Use: ... union select '', '', dd2, dd3 / The empty string is equivalent to null for inserts and updates. hth, Yosi > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 05, 2001 6:14 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: what to put in place of null > > > Try applying data conversion function to_char/to_date or nvl > function if dd > and dd1 are numbers to the nulls in the second select. > Example: > > cursor c1 is > select dd,dd1,dd2,dd3 from dd > union > select to_char(null),to_char(null),dd2,dd3 > from dd1; > > The point here is you need to have as many columns in your > second select > statement as your first select has and the datatypes have to match. > > -Original Message- > Sent: Tuesday, June 05, 2001 5:31 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > I have a cursor declared as > cursor c1 is > select dd,dd1,dd2,dd3 from dd > union > select null,null,dd2,dd3 from dd1; > > i am getting error: > * > ERROR at line 1: > ORA-01790: expression must have same datatype as > corresponding expression > ORA-06512: at "DY.REST", line 4 > ORA-06512: at "DY.REST", line 10 > ORA-06512: at line 1 > > what to replace null with.. > dd1 has only 2 columns dd2 and dd3. > > Thanks > Harvinder > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Harvinder Singh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: what to put in place of null
Harvinder Singh wrote: > > Hi, > > I have a cursor declared as > cursor c1 is > select dd,dd1,dd2,dd3 from dd > union > select null,null,dd2,dd3 from dd1; > > i am getting error: > * > ERROR at line 1: > ORA-01790: expression must have same datatype as corresponding expression > ORA-06512: at "DY.REST", line 4 > ORA-06512: at "DY.REST", line 10 > ORA-06512: at line 1 > > what to replace null with.. > dd1 has only 2 columns dd2 and dd3. > > Thanks > Harvinder > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com to_whatevertype_of_corresponding_not_null_column(NULL) NULL can have a type with oracle ... -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult ([EMAIL PROTECTED]) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what to put in place of null
I don't usually put a null like that in a union. I usually use a '' for a character and a 0 for a number and turn all dates to character. This way there is no matching problem. The thing is, you might have to use a value that you can then work with. For example, if you data is all positive numbers, you might want to use a -1 to match up to numbers. This way you can test the selected data and act on it accordingly. Plus, if your data is character and can not contain an '*' you might want to use it opposite of character fields. Its all data dependent. -Original Message- Sent: Tuesday, June 05, 2001 4:31 PM To: Multiple recipients of list ORACLE-L Hi, I have a cursor declared as cursor c1 is select dd,dd1,dd2,dd3 from dd union select null,null,dd2,dd3 from dd1; i am getting error: * ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression ORA-06512: at "DY.REST", line 4 ORA-06512: at "DY.REST", line 10 ORA-06512: at line 1 what to replace null with.. dd1 has only 2 columns dd2 and dd3. Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what to put in place of null
Try applying data conversion function to_char/to_date or nvl function if dd and dd1 are numbers to the nulls in the second select. Example: cursor c1 is select dd,dd1,dd2,dd3 from dd union select to_char(null),to_char(null),dd2,dd3 from dd1; The point here is you need to have as many columns in your second select statement as your first select has and the datatypes have to match. -Original Message- Sent: Tuesday, June 05, 2001 5:31 PM To: Multiple recipients of list ORACLE-L Hi, I have a cursor declared as cursor c1 is select dd,dd1,dd2,dd3 from dd union select null,null,dd2,dd3 from dd1; i am getting error: * ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression ORA-06512: at "DY.REST", line 4 ORA-06512: at "DY.REST", line 10 ORA-06512: at line 1 what to replace null with.. dd1 has only 2 columns dd2 and dd3. Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what to put in place of null
Try I have a cursor declared as cursor c1 is select dd,dd1,dd2,dd3 from dd union select ' ',' ',dd2,dd3 from dd1; or I have a cursor declared as cursor c1 is select dd,dd1,dd2,dd3 from dd union select 0,0,dd2,dd3 from dd1; - Ethan -Original Message- Sent: Tuesday, June 05, 2001 2:31 PM To: Multiple recipients of list ORACLE-L Hi, I have a cursor declared as I have a cursor declared as cursor c1 is select dd,dd1,dd2,dd3 from dd union select null,null,dd2,dd3 from dd1; i am getting error: * ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression ORA-06512: at "DY.REST", line 4 ORA-06512: at "DY.REST", line 10 ORA-06512: at line 1 what to replace null with.. dd1 has only 2 columns dd2 and dd3. Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what to put in place of null
Harv, Well, it depends on the type of dd and dd1, doesn't it? how about: (for the case where these are VARCHAR2) cursor c1 is select dd,dd1,dd2,dd3 from dd union select 'ddstub','dd1stub',dd2,dd3 from dd1; and likewise for other datatypes, mutatis mutandis. Yours in Data, etc. || -Original Message- || From: Harvinder Singh [mailto:[EMAIL PROTECTED]] || Sent: Tuesday, June 05, 2001 5:31 PM || To: Multiple recipients of list ORACLE-L || Subject: what to put in place of null || || || Hi, || || I have a cursor declared as || cursor c1 is || select dd,dd1,dd2,dd3 from dd || union || select null,null,dd2,dd3 from dd1; || || i am getting error: || * || ERROR at line 1: || ORA-01790: expression must have same datatype as || corresponding expression || ORA-06512: at "DY.REST", line 4 || ORA-06512: at "DY.REST", line 10 || ORA-06512: at line 1 || || what to replace null with.. || dd1 has only 2 columns dd2 and dd3. || || Thanks || Harvinder || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || -- || Author: Harvinder Singh || INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 || San Diego, California-- Public Internet access / || Mailing Lists || || To REMOVE yourself from this mailing list, send an E-Mail message || to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in || the message BODY, include a line containing: UNSUB ORACLE-L || (or the name of mailing list you want to be removed from). You may || also send the HELP command for other information (like subscribing). || -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
