Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

SQL Expression Help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • elkton_john
    replied
    Re: SQL Expression Help

    Originally posted by Rich Hartnett View Post
    Sorry for the delay in getting back to you John, I've just been swamped at work.

    This solution works great!!! Thank you so much.

    As I said, I am still very new at SQL, but this looks like it works similar to a CASE statement in DBF tables. Is that correct?
    Rich,

    " ... I've just been swamped at work." No problem - I guess crime is a booming industry!

    You're correct, the 'Switch' statement is the T-SQL equivalent to the select-case statement in xbasic. As with learning anything a little bit complicated, SQL can seem daunting but now that I work with it regularly I find that it can do things a lot easier than some other DB's. Having said that (I'll give Alpha a little plug here), you come to appreciate Alpha's built in functions, helpers and other means to an end. I have used Alpha for almost 30 years now, starting with the MS-Dos version and while not perfect, I still find it one of the best pieces of software that I use.

    John

    Leave a comment:


  • Rich Hartnett
    replied
    Re: SQL Expression Help

    Sorry for the delay in getting back to you John, I've just been swamped at work.

    This solution works great!!! Thank you so much.

    As I said, I am still very new at SQL, but this looks like it works similar to a CASE statement in DBF tables. Is that correct?

    Leave a comment:


  • elkton_john
    replied
    Re: SQL Expression Help

    Rich,

    Here you go. Let me know if you have any questions.

    John

    --- Correction to my pdf instructions ---
    You don't need to repeat the codes for the same department. Just use the first code (as assigned in the parameter) for each department.
    Attached Files
    Last edited by elkton_john; December 01, 2017, 01:53 PM. Reason: Change instructions

    Leave a comment:


  • Rich Hartnett
    replied
    Re: SQL Expression Help

    Okay thanks John.

    Leave a comment:


  • elkton_john
    replied
    Re: SQL Expression Help

    Rich,

    You can use an expression to set the title but I'll have to give it some thought as to how to best do it.

    John

    Leave a comment:


  • Rich Hartnett
    replied
    Re: SQL Expression Help

    Hey John I have a related question if you don't mind ............

    Is there an easy way I could take the agency name and have it populate at the top of the report? (see below) (It has static text at the top right now, but I would like it to change with the agency that is selected.)

    Screen Shot 11-30-17 at 10.09 AM 001.JPG

    Screen Shot 11-30-17 at 10.16 AM.JPG

    Rich
    Attached Files

    Leave a comment:


  • Davidk
    replied
    Re: SQL Expression Help

    John... that was a really good, complete, illustrative, appropriate answer. Thanks.

    Leave a comment:


  • elkton_john
    replied
    Re: SQL Expression Help

    Rich,

    Glad I could help.

    John

    Leave a comment:


  • Rich Hartnett
    replied
    Re: SQL Expression Help

    I'm sorry John, you are right, it does indeed work!!!!!!!!!!!

    I think perhaps I must have had a period at the end of the expression instead of a comma.

    ScreenShot022.jpg

    Anyway, I just rebuilt the expression and this time it works like a champ.

    I can't tell you how much time I have spent working on this one single expression. If I had had hair left on my head I would have pulled it all out by now, but it was gone long ago!

    Anyway, thanks so much John, GGhandi and Pat for your assistance.

    Rich

    Leave a comment:


  • elkton_john
    replied
    Re: SQL Expression Help

    Rich,

    The expression looks right to me. Is the parameter data type set to 'text' (see page 2 of my pdf)? If it is, can you try deleting the two date parameters and then create the expression for the beat parameter for testing purposes? Also, can you right click and then edit the BeatName parameter and then post a screen shot of the labels and values?

    John

    Leave a comment:


  • GGandhi
    replied
    Re: SQL Expression Help

    success is such a sweet word!
    https://www.screencast.com/t/oEOFokbC7

    I created a temp dataset converting the agency numbers to human readable pd name, adding multiple beat names to a single pd, then based the report and filtering it on the agency name. change anything on this to suit you.
    to have a dropdown list for the filter you need to create another dataset in the report, I am sure you can do that.
    here is the temp dataset build up for the report
    Code:
    SELECT
    	dt.id
    	,dt.CFDATE
    	,dt.INCIDENT_TYPE
    	,dt.BEAT_NAME
    	,dt.AGENCY
    FROM
    (SELECT
      t1.id
      ,t1.CFDATE
      ,t1.INCIDENT_TYPE
      ,t1.BEAT_NAME
    ,CASE
    WHEN BEAT_NAME = 842 THEN 'A PD'
    WHEN BEAT_NAME = 210 THEN 'BERWYN HEIGHTS PD'
    WHEN BEAT_NAME = 211 THEN 'BERWYN HEIGHTS PD'
    WHEN BEAT_NAME = 104 THEN 'B PD'
    WHEN BEAT_NAME = 1441 THEN 'C PD'
    WHEN BEAT_NAME = 227 THEN 'D PD'
    WHEN BEAT_NAME = 1603 THEN 'E PD'
    WHEN BEAT_NAME = 869 THEN 'F PD'
    WHEN BEAT_NAME = 131 THEN 'G PD'
    WHEN BEAT_NAME = 429 THEN 'H PD'
    WHEN BEAT_NAME = 939 THEN 'I PD'
    WHEN BEAT_NAME = 220 THEN 'J PD'
    WHEN BEAT_NAME = 313 THEN 'K PD'
    WHEN BEAT_NAME = 810 THEN 'L PD'
    END AS Agency
    FROM
      t1) dt
    WHERE dt.AGENCY = (@AGENCY)

    Leave a comment:


  • Rich Hartnett
    replied
    Re: SQL Expression Help

    John I tried that but I get an error message:

    Screen Shot 11-29-17 at 05.05 PM.JPG

    Screen Shot 11-29-17 at 05.05 PM 001.JPG

    Leave a comment:


  • elkton_john
    replied
    Re: SQL Expression Help

    Rich,

    I think that I solved your problem. Attached is a pdf that shows the screen shots in SQL report builder. if you have any questions, let me know.

    John
    Attached Files

    Leave a comment:


  • Pat Bremkamp
    replied
    Re: SQL Expression Help

    In MySQL or MariaDB you can write
    where BeatName = "0210" OR BeatName = "0211" (no periods around the or)
    or more generically, write
    where BeatName IN ('0210','0211')

    Leave a comment:


  • Rich Hartnett
    replied
    Re: SQL Expression Help

    Thanks Ghandi. Unfortunately that doesn't work, either with or without the quotation marks, but that is exactly what I am trying to do -- figure out the correct way to write the expression so it will look for either 0210 or 0211. Your method does not throw any errors, it just doesn't return any records.

    Screen Shot 11-29-17 at 10.01 AM.JPG

    Screen Shot 11-29-17 at 10.03 AM.JPG

    Leave a comment:

Working...
X