1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

SQL questions

Discussion in 'Software Development' started by SabreWolf3, Jul 26, 2004.

Thread Status:
Not open for further replies.
  1. SabreWolf3

    SabreWolf3 Thread Starter

    Joined:
    Nov 6, 2000
    Messages:
    227
    I am running a query with very specific criteria, and I need to assign a value to all records returned that meet this criteria. I will then run a second query that has different criteria, and assign a second value to those records returned. Then, putting all of those records into the same database, this will allow pivot tables to be created and differentiate between the two custom values I have assigned.

    My question is this: how do I assign a static (default) value to all records returned from a query? I need to do this in a query, as it will be a nightly automated process. Let's say this is my sample query below:

    Code:
    SELECT Cal_Dt, Order_ID, Customer_ID
    FROM Order_Details, Customer_Details
    WHERE Order_Details.Customer_ID = Customer_Details.Customer_ID AND
    Cal_Dt = #1/1/2004# And Payment_Type = "MC" And Order_Num >= 25
    
    Now, in addition to the 3 fields I have selected in my query, I want a 4th field with a value of "Silver" assigned for every record returned in this query.

    A second question I have about this query is that I want to concatenate the values of Cal_Dt, Order_ID and Customer_ID. The problem is that the Cal_Dt field is a date/time data type and the Order_ID and Customer_ID fields are both decimal data types. The concat function of Redbrick Decision Server only works with character data types, so it won't concatenate the values for me. Is there a way to convert these data types to char/varchar so the concat function will work?

    Thanks
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    You can assign a variable to the recordset itself:

    strSQL = SELECT Cal_Dt, Order_ID, Customer_ID
    FROM Order_Details, Customer_Details
    WHERE Order_Details.Customer_ID = Customer_Details.Customer_ID AND
    Cal_Dt = #1/1/2004# And Payment_Type = "MC" And Order_Num >= 25

    You can inject the "silver" in anywhere when you loop through all of the records.

    To add the date into a concatenated string just format the date to a serial value. strMSC= DateSerial("Cal_Dt")&""& Order_ID &""&Customer_ID
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/254686

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice