BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Small SQL question          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Marco Caspers Guest Posted: Mon Dec 08, 2003 2:30 pm    Post subject: Small SQL question Hi Folks, I have a small question to ask on how to get something done in SQL statement(s). Currently i have a table containing a few fields among which the following two: KeyFLD (a string field of 8 characters), EndDate (A string field of 11 characters). What i need is basically a select statement that returns all records of the table but instead of the value of EndDate i want it to return a boolean result wether or not the contents of the field is NULL. I've tried: - SELECT KeyFLD, ISNULL(EndDate) FROM MyTable - SELECT KeyFLD, (EndDate IS NULL) FROM MyTable I've also tried: SELECT KeyFLD, B.BoolVal FROM MyTable A LEFT OUTER JOIN BoolTrans B ON B.BoolVal=(A.ENDDate IS NULL) Where BoolTrans contains 2 records with Boolean field BoolVal (True and False).. Neither of these work, the first is not supported (Capability not supported error) and the latter two complain about an invalid keyword (IS).. Currently i'm using a few simple standalone paradox tables, but might need to convert them later to MS SQL server 2000.. I need something that works in both situations though.. When i convert the thing i make sure that i add in an extra boolean field that changes with the EndDate being filled or not, but for now i'm stuck with what i've got.. Can anyone suggest what i could use to solve this issue? Regards, Marco.. Back to top Mehrdad Momeni Guest Posted: Mon Dec 08, 2003 4:27 pm    Post subject: Re: Small SQL question Oh brother- Some thing like this - common between paradox and sql server - you gotta be kiddin. Sorry I did not want to make disapointments, big brothers may have some solutions though. Cheers - and good luck Back to top Kevin Frevert Guest Posted: Mon Dec 08, 2003 4:48 pm    Post subject: Re: Small SQL question Marco, With MSSQL you could use a Case statement like.. Select KeyField, EndDate, CAST((CASE WHEN IsNull(EndDate,0) = 0 THEN 1 ELSE 0 END) as bit) [Is_EndDate_Null] From MyTable Where {so on and so forth} I'm not familar with the particulars of Paradox, so any specific Pardox questions may be better asked in the ..delphi.database.desktop group Good luck, krf "Marco Caspers" <*nospam*@haxor.vaxor.com> wrote Quote: Hi Folks, Currently i'm using a few simple standalone paradox tables, but might need to convert them later to MS SQL server 2000.. Back to top Marco Caspers Guest Posted: Mon Dec 08, 2003 5:42 pm    Post subject: Re: Small SQL question "Kevin Frevert" <Work (AT) drinkingcoffee (DOT) com> schreef in bericht news:3fd4ab41$1 (AT) newsgroups (DOT) borland.com... Quote: Marco, With MSSQL you could use a Case statement like.. Select KeyField, EndDate, CAST((CASE WHEN IsNull(EndDate,0) = 0 THEN 1 ELSE 0 END) as bit) [Is_EndDate_Null] From MyTable Where {so on and so forth} I'm not familar with the particulars of Paradox, so any specific Pardox questions may be better asked in the ..delphi.database.desktop group Kevin, Thank you for your response. This will do for the SQL server part, it won't do for Paradox. As per your hint i will try in the desktop group.. Regards, Marco. Back to top Mike Walsh Guest Posted: Mon Dec 08, 2003 6:00 pm    Post subject: Re: Small SQL question "Kevin Frevert" <Work (AT) drinkingcoffee (DOT) com> wrote Quote: Marco, With MSSQL you could use a Case statement like.. Select KeyField, EndDate, CAST((CASE WHEN IsNull(EndDate,0) = 0 THEN 1 ELSE 0 END) as bit) [Is_EndDate_Null] From MyTable Where {so on and so forth} I'm not familar with the particulars of Paradox, so any specific Pardox questions may be better asked in the ..delphi.database.desktop group Good luck, krf "Marco Caspers" <*nospam*@haxor.vaxor.com> wrote in message news:3fd49909$1 (AT) newsgroups (DOT) borland.com... Hi Folks, Currently i'm using a few simple standalone paradox tables, but might need to convert them later to MS SQL server 2000.. Marco, Like Kevin, I'm not familiar with how you can do this in Paradox, but maybe a union will work there. Personally, I like the case statement better, but a union would work with SQL Server also... select KeyFld, cast (1 as bit) as Is_EndDate_Null from WhereEver where (EndDate is null) union select KeyFld, cast (0 as bit) as Is_EndDate_Null from WhereEver where (EndDate is not null) Mike Walsh Back to top Betsy Guest Posted: Tue Dec 09, 2003 12:06 am    Post subject: Re: Small SQL question Marco... I would like to suggest that you give up your quest now! Although sql syntax is suppose to fall under some standard or another it's been my experience that this has little to no effect on the truth of the matter. Unless your select statement is straight-forward select * from ???.### you are going to run into compatibility/syntax errors. NULL/ISNULL functionality varies from database to database... these are 'functions' provided by the database somehow and the syntax varies enough that it will give you headaches. Besides you are accessing the paradox table via the BDE components and ODBC and will most likely want to take those layers of complexity out when you convert to MSSQL... You know what you have... code for what you have and worry about what you 'will have' later... best of luck to you! b "Marco Caspers" <*nospam*@haxor.vaxor.com> wrote Quote: Hi Folks, I have a small question to ask on how to get something done in SQL statement(s). Currently i have a table containing a few fields among which the following two: KeyFLD (a string field of 8 characters), EndDate (A string field of 11 characters). What i need is basically a select statement that returns all records of the table but instead of the value of EndDate i want it to return a boolean result wether or not the contents of the field is NULL. I've tried: - SELECT KeyFLD, ISNULL(EndDate) FROM MyTable - SELECT KeyFLD, (EndDate IS NULL) FROM MyTable I've also tried: SELECT KeyFLD, B.BoolVal FROM MyTable A LEFT OUTER JOIN BoolTrans B ON B.BoolVal=(A.ENDDate IS NULL) Where BoolTrans contains 2 records with Boolean field BoolVal (True and False).. Neither of these work, the first is not supported (Capability not supported error) and the latter two complain about an invalid keyword (IS).. Currently i'm using a few simple standalone paradox tables, but might need to convert them later to MS SQL server 2000.. I need something that works in both situations though.. When i convert the thing i make sure that i add in an extra boolean field that changes with the EndDate being filled or not, but for now i'm stuck with what i've got.. Can anyone suggest what i could use to solve this issue? Regards, Marco.. Back to top Marco Caspers Guest Posted: Tue Dec 09, 2003 7:46 am    Post subject: Re: Small SQL question Mike Walsh wrote: [snip] Quote: Like Kevin, I'm not familiar with how you can do this in Paradox, but maybe a union will work there. Personally, I like the case statement better, but a union would work with SQL Server also... select KeyFld, cast (1 as bit) as Is_EndDate_Null from WhereEver where (EndDate is null) union select KeyFld, cast (0 as bit) as Is_EndDate_Null from WhereEver where (EndDate is not null) Hi Mike, Thanks for this heads up on SQL. Union works perfectly (and fast) for this! I only had to replace bit by boolean for Paradox to make it work. Regards, Marco. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.