Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: jfcox
  • Order by date
  1. jfcox

    Subquery returned more than 1 row error

    I have a table with three columns; column a is an identity column, column b is just an id number, and column c is a date. I have several blanks in the date column and want to replace them with nulls. I tried the following query which fails on a "subquery returned more than 1 row" error and I...
  2. jfcox

    Date format doesn't work as expected

    Thanks, but I need date1 to be a real date otherwise it doesn't sort correctly. I also only want it as mm/dd/yyyy. How do I convert what you wrote to a datetime?
  3. jfcox

    Date format doesn't work as expected

    I have a column that I need to get into mm/dd/yyyy format I can get it that way as a varchar but converting to a date gives me a problem. I thought it was my data but look at this example, select Convert(varchar,getdate(), 101) as date1, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 101))...
  4. jfcox

    CSV Parameter list with SQL 'in' as part of the where clause

    Interestingly, that doesn't work for me completely; If I go {table.type} in {?type}[1] that works, if I go {table.type} in {?type}[2] that creates an error. The value of that was passed in as Type was: interactive,home So, although there are two (or more) items in my list, it is not...
  5. jfcox

    CSV Parameter list with SQL 'in' as part of the where clause

    This ought to be simple but I'm missing something. I have a multiple, distinct parameter called "Type" that I am passing a comma separated list, for example; interactive,selfpaced,home I'd like to use something like this in a record selection formula: {maintable.type} in {?Type} If I...
  6. jfcox

    SQL table to single row Spreadsheet or flatfile

    Wow, thanks a bunch, I would have never come up with this on my own. Jim
  7. jfcox

    SQL table to single row Spreadsheet or flatfile

    That is correct
  8. jfcox

    SQL table to single row Spreadsheet or flatfile

    Obviously, I picked a bad example. Let me try with somthing that might inherently imply the outcome. Suppose I had a table of last_Name, Drivers Lic St., and Drivers License number. If I had data like this: Smith NJ 12345 Smith NY 98755 Jones TN 55554 Jones KY 6879 I'm looking for a...
  9. jfcox

    SQL table to single row Spreadsheet or flatfile

    Yes, they are essentially name-value pairs.
  10. jfcox

    SQL table to single row Spreadsheet or flatfile

    No, that doesn't matter. Thanks
  11. jfcox

    SQL table to single row Spreadsheet or flatfile

    Using the previous example, the result I need should look something like CustNo state1 partno1 state2 partno 2 1 NJ 60 NY 50 2 TN 30 KY 45 The state might be repeated but with a different PartNo. Technically, there can be unlimited numbers of...
  12. jfcox

    SQL table to single row Spreadsheet or flatfile

    I have a typical table that looks something like CustNo State PartNo 1 NJ 60 1 NY 50 2 TN 30 2 KY 45 The CustNo column is not unique but is the key. I need to create a single row on a spreadsheet or flat file that looks like this CustNo...
  13. jfcox

    Reporting Services vs Crystal, which is better?

    Hi, I'm running Crystal Enterprise at the momemnt but hate it. How does Reporting Services compare in terms of report design and deployment?
  14. jfcox

    Convert separated varchar date to datetime

    Thanks for all your suggestions as I tried them all. It still doesn't work and I get an error 'Error converting data to datetime' I think maybe Esquared's suggestion that I'm missing leading zeros might be valid. To be a little clearer than my first post, I have a date broken into three...
  15. jfcox

    Convert separated varchar date to datetime

    I have a table that has the date separated into 3 columns, month, day and year. All three columns are varchar in the database. I need to recombine those parts into a full date and convert the result into a datetime. I have tried casting and coverting combinations to no avail. One of the...
  16. jfcox

    Trouble passing multi value parameter

    I'm running CR 8.5, Win2K, active x viewer. I am trying to pass multi values to a parameter I've created. If I configure the parameter for a single value, it works fine. If I change the same parameter to multi value, the user prompt box opens every time and expects manual entry. I am simply...
  17. jfcox

    Confused by Crystal 10 licensing and use

    Hi, I currently have Crystal Ent 8.5 that I use to distribute reports from our ASP application. The users only have the com object viewer to display the report in their browser. I use the 5 concurrent licenses that came with CR Ent. and have no trouble. I'm thinking about upgrading to CR 10...
  18. jfcox

    TO_CHAR creates extra leading blank

    Mufasa, Thanks for the help. Based on what you sent, I fixed it. What I was doing was assuming that the extra blank was coming from the source table. I had this Select To_Char(ltrim(column), '099999') instead of the correct Select ltrim(To_Char(column, '099999')) I hate it when I do...
  19. jfcox

    TO_CHAR creates extra leading blank

    Great! Here's the sql and the result: Select TO_CHAR(AHFS, '099999') NEW_AHFS from FDB_GCNSEQNO_AHFS 240408 240408 240408 240408 240408 240408 240408 And here is the result of the dump: Typ=1 Len=7: 32,50,52,48,52,48,56 Typ=1 Len=7: 32,50,52,48,52,48,56 Typ=1 Len=7...
  20. jfcox

    TO_CHAR creates extra leading blank

    Hi, I have to convert a number column that has leading zero(s) to a varchar2. If I run the TO_Char(column, '099999') or other similar formats I get an extra leading blank. For example, if the value in the number column is 012345 when I run the to_char I get ' 012345'. I have tried TRIM and...

Part and Inventory Search

Back
Top