Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Answered: Academic question

    I have a field that was text YES\NO\Null. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to Yes\No inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return Yes\No\Null or should I just keep the field as a 3 byte text?

    Code:
    SELECT Jobs_Table.JobNum, 
    [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, 
    The_Big_One1.HT_Mail_STId, 
    IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC, 
    Count(The_Big_One1.HT_SerNum) AS CountOfHT_SerNum, Avg(The_Big_One1.TotalDays) AS AvgOfNoDays
    FROM Jobs_Table INNER JOIN The_Big_One1 ON Jobs_Table.JobNum = The_Big_One1.HT_JobNum
    GROUP BY Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService], The_Big_One1.HT_Mail_STId, IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")), The_Big_One1.STC_SCAN;
    or
    Code:
    SELECT Jobs_Table.JobNum, 
    [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, 
    The_Big_One1.HT_Mail_STId, 
    THE_Big_One1.STC_SCAN, 
    Count(The_Big_One1.HT_SerNum) AS CountOfHT_SerNum, Avg(The_Big_One1.TotalDays) AS AvgOfNoDays
    FROM Jobs_Table INNER JOIN The_Big_One1 ON Jobs_Table.JobNum = The_Big_One1.HT_JobNum
    GROUP BY Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService], 
    The_Big_One1.HT_Mail_STId, The_Big_One1.STC_SCAN;

  2. Best Answer
    Posted by blindman

    ""When I imported it into SQL I converted it to Boolean"
    There is no Boolean datatype in SQL Server, so it is unclear how to best resolve your problem.
    Are you using MS Access as an interface?"


  3. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "When I imported it into SQL I converted it to Boolean"
    There is no Boolean datatype in SQL Server, so it is unclear how to best resolve your problem.
    Are you using MS Access as an interface?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #3
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    Thanks you for your reply. After due consideration I decided to go with the 3byte text field as this information is strictly for display. The time and effort it would take to change the code in all my reports would not be worth the storage space saved by converting it to a bit type field. I do not believe it would add speed to the application, maybe it could do the opposite.

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you are determined to stick to a text field then please make sure you implement a CHECK constraint to limit the values available in this field. Nothing worse when someone types "YNo" in to the field
    George
    Home | Blog

  6. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    select [This is FALSE] = cast('false' as bit), [...and this is TRUE] = cast('true' as bit), [while this is NULL] = cast(null as bit)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I have a field [sic] that was text YES\NO\NULL. <<

    Text? No such data type in SQL! Field? A field in SQL is part of a temporal data type {year, month, day, hour, minute, second}; have you read a book or had an intro course to SQL? You got basic terms wrong

    >> When I imported it into SQL I converted it to Boolean [sic] and had to go back and NULL out the ones that were supposed to be NULL. <<

    Another fundamental error. We use predicates and not BOOLEAN in RDBMS. Yes, some vendors have them, but they just screw up things. https://www.simple-talk.com/sql/t-sq...-of-a-problem/

    >> I am now trying to use the fields [sic] in my old reports and find that I have to convert the values to Yes\No inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return Yes\No\NULL or should I just keep the field [sic] AS a 3 byte text? <<

    This whole thing probably needs to be re-written.

    That “Jobs_Table” name is a design error known AS a “tibble”; it goes back to FORTRAN I and II, early Operating Systems and 1960's BASIC. The simple early compilers has to have meta data in the names! We do not do that anymore.

    ISNULL() has been replaced by COALESCE(), We do not use the IFF dialect FROM spreadsheets; SQL has a CASE expression. In fact, this looks like a bad spreadsheet where you have data element names meant for display!

    You also failed to follow basic netiquette and post DDL.
    Why do you think that “The_Big_One1” is a meaningful name for a set of entities?? Sorry, “big one” is vaguely pornographic and we need a set of them. Your “fullservice” is a value in a nominal scale, probably “something_service_type” as guess. Then you have “basic”!! Basic what? Another service type? More denormalization? We have no DDL!

    Want to try again? Can you throw this mess and use SQL?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •