SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

I'm having some trouble with sqlite. After booting my machine, a query takes 10+ minutes to execute. After that, the same query executes in a couple seconds, and uses some indexes I created. There's constant hard drive activity during the 10+ minutes so I assume sqlite is doing something to read data from disk into memory. It seems unreasonably slow though for the fairly simple query I'm doing and the size of this database (500MB.) I tried doing a VACUUM, didn't help at all.

I found this thread on the sqlite mailing list, seems to be the exact problem I'm having. They suggest renaming the files to something other than .db as Windows does some weird caching with files of that extension, but we use .db3 in our app, and the author of the thread didn't have any luck with that solution. He also specifies that he doesn't have the issue on Linux.

Any suggestions on where to go from here?

# ? Aug 3, 2010 08:03

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 19:26

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

camels posted:

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Does that make sense? You don't have to set up a stored procedure, necessarily, just read SCOPE_IDENTITY in the same module where you INSERT into the table.

Actually I came across that exact same article... I don't see the statement very clearly.

I tried multiple variations of "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]" and SQL returned garbage or nothing...

What is the statement I can write that will return the value?

# ? Aug 3, 2010 08:10

Bad Titty Puker: Nov 3, 2007; Soiled Meat

cannibustacap posted:

Actually I came across that exact same article... I don't see the statement very clearly.

I tried multiple variations of "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]" and SQL returned garbage or nothing...

What is the statement I can write that will return the value?

Post CREATE TABLE statement, the code you ran and what SQL Server returned

# ? Aug 3, 2010 12:51

Sub Par: Jul 18, 2001; Dinosaur Gum

fletcher posted:

I'm having some trouble with sqlite. After booting my machine, a query takes 10+ minutes to execute. After that, the same query executes in a couple seconds, and uses some indexes I created. There's constant hard drive activity during the 10+ minutes so I assume sqlite is doing something to read data from disk into memory. It seems unreasonably slow though for the fairly simple query I'm doing and the size of this database (500MB.) I tried doing a VACUUM, didn't help at all.

I found this thread on the sqlite mailing list, seems to be the exact problem I'm having. They suggest renaming the files to something other than .db as Windows does some weird caching with files of that extension, but we use .db3 in our app, and the author of the thread didn't have any luck with that solution. He also specifies that he doesn't have the issue on Linux.

Any suggestions on where to go from here?

The only thing I can find is from the same mailing list, different thread. This guy says you can mitigate it using his method.

# ? Aug 3, 2010 14:32

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

cannibustacap posted:

Actually I came across that exact same article... I don't see the statement very clearly.

I tried multiple variations of "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]" and SQL returned garbage or nothing...

What is the statement I can write that will return the value?

Well to be honest, I just used the SQL Server GUI to create the tables. I am not much of an SQL expert, I just use it to get the database job done.

I created a SQL Sever 2005 table, made a few columns with varchar(n) and int and then the "identity" column that auto-increments, called 'nid'

Nothing more than that.

When I add data to that table, I'd like to simply extract out that new entry to the 'nid' column...

# ? Aug 3, 2010 16:42

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Sub Par posted:

The only thing I can find is from the same mailing list, different thread. This guy says you can mitigate it using his method.

Thank you for the link! The SELECT COUNT(last_column) trick does appear to make a huge improvement, that operation only takes 40 seconds, and then executing the query that normally takes 10 minutes only takes 9 seconds.

Watching the I/O Read Bytes in task manager, the SELECT COUNT causes the read bytes to climb in 10MB chunks, without the SELECT COUNT first the Read Bytes goes in 200KB chunks and gradually increases into 2MB chunks.

This SQLite optimization page specifically mentions Windows and poor memory allocation. I guess with the SELECT COUNT query it knows it has to go through the whole table, so it does the memory allocation more efficiently?

# ? Aug 3, 2010 20:15

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

fletcher posted:

Thank you for the link! The SELECT COUNT(last_column) trick does appear to make a huge improvement, that operation only takes 40 seconds, and then executing the query that normally takes 10 minutes only takes 9 seconds.

Watching the I/O Read Bytes in task manager, the SELECT COUNT causes the read bytes to climb in 10MB chunks, without the SELECT COUNT first the Read Bytes goes in 200KB chunks and gradually increases into 2MB chunks.

This SQLite optimization page specifically mentions Windows and poor memory allocation. I guess with the SELECT COUNT query it knows it has to go through the whole table, so it does the memory allocation more efficiently?

Wouldn't it make more sense to count the Primary key column's?

# ? Aug 3, 2010 20:17

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Sprawl posted:

Wouldn't it make more sense to count the Primary key column's?

Not if I'm trying to touch the whole table as was mentioned in the sqlite thread that was linked.

# ? Aug 3, 2010 20:36

Civil: Apr 21, 2003; Do you see this? This means "Have a nice day".

I'm selecting values out of a very simple table. I want to return a single record per ContractNo, which is why I inserted the 'group by' statement.

code:

select ContractNo, BidOpenDate
from bidsummary 
WHERE BidOpenDate BETWEEN (NOW() - INTERVAL 42 DAY) AND NOW() and BidOpenDate >= '2010-07-01'
group by ContractNo 
ORDER BY BidOpenDate DESC, ContractNo ASC

I only want to show the latest instance of a record, but if I have 2 different records for ContractNo's with different BidOpenDate's, I'll get the first one entered into the database, rather than the most recent one, which is what I want.

How would I force the most recent one to be the one returned?

edit: vvv Perfect, thank you! vvv

Civil fucked around with this message at 23:35 on Aug 3, 2010

# ? Aug 3, 2010 23:08

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Civil posted:

I'm selecting values out of a very simple table. I want to return a single record per ContractNo, which is why I inserted the 'group by' statement.
code:
select ContractNo, BidOpenDate
from bidsummary 
WHERE BidOpenDate BETWEEN (NOW() - INTERVAL 42 DAY) AND NOW() and BidOpenDate >= '2010-07-01'
group by ContractNo 
ORDER BY BidOpenDate DESC, ContractNo ASC
 
I only want to show the latest instance of a record, but if I have 2 different records for ContractNo's with different BidOpenDate's, I'll get the first one entered into the database, rather than the most recent one, which is what I want.

How would I force the most recent one to be the one returned?

code:

select ContractNo, MAX(BidOpenDate) as BidOpenDate
from bidsummary 
WHERE BidOpenDate BETWEEN (NOW() - INTERVAL 42 DAY) AND NOW() and BidOpenDate >= '2010-07-01'
group by ContractNo 
ORDER BY BidOpenDate DESC, ContractNo ASC

Should get you the newest date.

# ? Aug 3, 2010 23:16

Bad Titty Puker: Nov 3, 2007; Soiled Meat

cannibustacap posted:

Well to be honest, I just used the SQL Server GUI to create the tables. I am not much of an SQL expert, I just use it to get the database job done.

I created a SQL Sever 2005 table, made a few columns with varchar(n) and int and then the "identity" column that auto-increments, called 'nid'

Nothing more than that.

When I add data to that table, I'd like to simply extract out that new entry to the 'nid' column...

Please post the code you are running to insert the data and get SCOPE_IDENTITY...

# ? Aug 4, 2010 00:04

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

camels posted:

Please post the code you are running to insert the data and get SCOPE_IDENTITY...

Okay, well I am actually using the Matlab Database Toolbox to insert the data, but that toolbox basically does this:

code:

INSERT INTO table1 (col1, col2) VALUES (value1, value2);
SELECT nid FROM table1 WHRE nid = @@IDENTITY;

So that is the code, pretty much.

Not sure how to implement SCOPE_IDENTITY

EDIT: What I am trying to do is get the "nid" from table1 after it got "value1" and "value2" inserted into cols1 and 2.

So I am looking for the "nid" that exists in that same row as "value1" and "value2"

cannibustacap fucked around with this message at 00:24 on Aug 4, 2010

# ? Aug 4, 2010 00:16

Bad Titty Puker: Nov 3, 2007; Soiled Meat

cannibustacap posted:

Okay, well I am actually using the Matlab Database Toolbox to insert the data, but that toolbox basically does this:
code:
INSERT INTO table1 (col1, col2) VALUES (value1, value2);
SELECT nid FROM table1 WHRE nid = @@IDENTITY;
So that is the code, pretty much.

Not sure how to implement SCOPE_IDENTITY

EDIT: What I am trying to do is get the "nid" from table1 after it got "value1" and "value2" inserted into cols1 and 2.

So I am looking for the "nid" that exists in that same row as "value1" and "value2"

You didn't say you were using the Matlab Database Toolbox. You didn't explain what you don't understand about the two lines of sample code in the MSDN article.

You said you tried using SCOPE_IDENTITY and failed, but you didn't post any code or error messages.

Perhaps someone more patient than I can help you.

# ? Aug 4, 2010 00:41

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

camels posted:

You didn't say you were using the Matlab Database Toolbox. You didn't explain what you don't understand about the two lines of sample code in the MSDN article.

You said you tried using SCOPE_IDENTITY and failed, but you didn't post any code or error messages.

Perhaps someone more patient than I can help you.

I didn't see how that matlab stuff mattered? I was just inserting rows into a database and I just wanted a statement to extract the last 'nid' within my scope.

Thanks for giving up on me!

# ? Aug 4, 2010 01:59

Bad Titty Puker: Nov 3, 2007; Soiled Meat

cannibustacap posted:

I didn't see how that matlab stuff mattered? I was just inserting rows into a database and I just wanted a statement to extract the last 'nid' within my scope.

Thanks for giving up on me!

edit: Okay, I give up. I will answer the question you asked three times:

quote:

I just wanted a statement to extract the last 'nid' within my scope.

I posted the answer in my first reply. The answer to your question is in the MSDN article that you yourself looked at. It's not a trick answer. The article literally shows you how to do what you want. That's all the help I can give you.

Bad Titty Puker fucked around with this message at 02:47 on Aug 4, 2010

# ? Aug 4, 2010 02:42

Kekekela: Oct 28, 2004

cannibustacap posted:

Okay, well I am actually using the Matlab Database Toolbox to insert the data, but that toolbox basically does this:
code:
INSERT INTO table1 (col1, col2) VALUES (value1, value2);
SELECT nid FROM table1 WHRE nid = @@IDENTITY;
So that is the code, pretty much.

Not sure how to implement SCOPE_IDENTITY

EDIT: What I am trying to do is get the "nid" from table1 after it got "value1" and "value2" inserted into cols1 and 2.

So I am looking for the "nid" that exists in that same row as "value1" and "value2"

Well, selecting nid where nid = @@Identity (or SCOPE_IDENTITY()) is just the same as selecting the identity directly (select @@Identity). To "implement SCOPE_IDENTITY" You should just be able to
SELECT SCOPE_IDENTITY()
unless I'm completely misunderstanding you.

# ? Aug 4, 2010 02:47

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

I think the combination of my limited understanding of SQL and asking a confusing question is throwing everyone off.

I will stick to the @@IDENTITY because it actually returns a value when I use it and it is consistently the right one!

SCOPE_IDENTITY didn't return anything, which makes me think I am doing it wrong. But oh well.

# ? Aug 5, 2010 09:34

OneEightHundred: Feb 28, 2008; Soon, we will be unstoppable!

I think there was a thread about getting the database under source control, but it seems to have fallen off and I don't have archives, and the company I'm working for is currently trying to make a push to start tracking releases in source control instead of using some bug tracker not really designed for it.

Anyone have any advice for how to integrate the DB into source control, what has worked well and what hasn't, etc.?

# ? Aug 5, 2010 15:56

Avarice7: Sep 7, 2004

Hey guys, alright I've been tasked with combining a few queries to create a single query for use in some automation software. I've finally reached a tipping point and can't figure out why. Been stuck on this too long, and my team doesn't know... so gonna ask for some help.

Getting a stupid ORA-00900:invalid SQL statement error:

code:

SELECT COUNT(*)
FROM   asap.circuit c,
       asap.serv_item si,
       asap.serv_req_si srsi,
       asap.serv_req_si_value srsv,
       asap.end_user_location_usage eulu
WHERE  c.circuit_design_id = si.circuit_design_id
       AND si.serv_item_id = srsi.serv_item_id
       AND srsi.document_number = eulu.document_number
       AND srsi.serv_item_id = eulu.serv_item_id
       AND srsi.document_number = '4744969'
       AND Upper(si.item_alias) LIKE '%T-1%'
       AND eulu.local_loop_ind = 'Y'
       AND srsv.value_label = 'Marketing Product Name'
       AND srsv.serv_item_id = si.serv_item_id

The lines that I added that broke it were:
Line 5 in FROM) asap.serv_req_si_value srsv,
and
Lines 14,15 in WHERE) AND srsv.value_label = 'Marketing Product Name'
AND srsv.serv_item_id = si.serv_item_id

I'm not sure whats going on in those lines to break the statement, unless adding those lines broke something elsewhere and I'm not seeing that either.

Thanks!

# ? Aug 5, 2010 17:27

Sub Par: Jul 18, 2001; Dinosaur Gum

Replacing your table names with dual and your field names with dummy returns a valid SQL statement on my installation (10g). I don't see anything wrong with it.

It was working just fine without those lines? If you remove them, does it still work?

# ? Aug 5, 2010 18:18

Avarice7: Sep 7, 2004

Yeah, it's the most bizarre thing. I remove those three lines and it works just fine and currently returns 3 records. Ultimately I'll replace Count(*) w/ some specific fields, but for now just using count(*) for testing purposes.

# ? Aug 5, 2010 18:30

enri: Dec 16, 2003; Hope you're having an amazing day

Ok, it's 11:31pm and I can't quite wrap my head around how to phrase this question so I'm going to just explain what I'm trying to do and hope one of you lovely chaps can work it out.

I've got 5 tables, the first table, we'll call 'StatusReports' is a general 'this is where everything goes regarding the status reports' table.

The other 4 tables are general information relating to that first table, i.e. users, account, etc. but one of them is for the status' themselves and it looks something like this:

code:

ID     STATUSDESC
1      n/a
2      not received
3      received
4      in progress
5      on hold
6      in queue

The only problem is, I have 3 columns in the statusreport table using the ID reference from this table and I can't work out how to join the fuckers together to get the info I want.

the part of statusreport I'm struggling with looks like this:

code:

status_brief     status_creative    status_data
1                2                  1
4                1                  2
3                2                  2

At the mo I'm quite content doing something like this:

code:

select *, users.name AS userName, priority.name AS priorityName
from statusreport, users, priority
  where statusreport.name = users.ID AND
  where statusreport.priority = priority.ID
  order by priority desc

I'm just doing some simple joins to say:

priority in statusreport is 1 eh? Ok, Id '1' in the priority table says 'low', so use that value 'low' when you're slapping it in the page ok?

but as I said, the 'status' table is referenced 3 times in the statusreport table, so how the hell do I get it to do something like:

status_brief = 1 so call it 'n/a', status_creative = 2 so call it 'not received' and status_data = 4 so call it 'in progress'

All the common sense approaches are hurting my head.

For the record, I'm testing the hell out of it in SQL server but it's primarily supposed to be coming from a coldfusion page.

Don't know if I've quite explained myself fully or if the thing I'm trying to do is even workable. I don't want to create 3 identical reference tables just to save some chump sitting there going 'what the hell does 3 mean in the brief status column?', help

edit: in a nutshell, I think I'm trying to do multiple joins between the same two tables and SQL server doesn't like my formatting of the query or something... no idea where to look

edit: found a guide to doing it using aliases but it's all in scary poo poo that as far as I'm aware, coldfusion doesn't much like, i.e. join t1 on t2.id blah blah blah.. coldfusion seemed to prefer where t1.name = t2.id or some such poo poo. Buggered if I know the ins and outs of it, all I know is that coldfusion doesn't like me at the mo

enri fucked around with this message at 00:06 on Aug 7, 2010

# ? Aug 6, 2010 23:44

RoadCrewWorker: Nov 19, 2007; camels aren't so great

code:

select 
(stuff from statusreport st....),
d1.statusdesc as brief_status,
d2.statusdesc as creative_status,
d3.statusdesc as data_status
from
statusreport st
join statusnames d1 on (st.status_brief=d1.id)
join statusnames d2 on (st.status_creative=d2.id)
join statusnames d3 on (st.status_data=d3.id)

Wouldn't three simple joins like this (mysql syntax but should be easily translated) give you a statusreport table along with 3 named columns having the appropriate status descriptions? My apologies if i missunderstood your request or if this proposal doesn't work because of some SQL server quirks, i hope it's at least a start.

Edit: drat, didn't catch your edit. Well, simply translate the join conditions into a bunch of where clauses?

RoadCrewWorker fucked around with this message at 00:15 on Aug 7, 2010

# ? Aug 7, 2010 00:13

enri: Dec 16, 2003; Hope you're having an amazing day

RoadCrewWorker posted:

Edit: drat, didn't catch your edit. Well, simply translate the join conditions into a bunch of where clauses?

I think I got it, I was just muffing up the aliases

the multiple where clauses was the first thing I tried but bloody aliases. Cheers all the same though, pretty much confirms what I've just hacked together

edit: sorry yea, meant to say.. SQL server quirks I think... I can't use 'join' for some reason, it craps its pants. I've also just stuck the query together and it works a treat

hurrah!

enri fucked around with this message at 00:24 on Aug 7, 2010

# ? Aug 7, 2010 00:20

zelifcam: Feb 21, 2004; www.tgs-mart.com

I'm trying to build a case statement and I'm stuck:

code:

Table = location

----------------------
| locID |   keyword  |
----------------------
|   1   | 2334347887 |
|   2   |   so1      |
|   3   |   rt2      |
|   4   | 2213328899 |
|   5   |   rt3      |
----------------------

The keyword can either be a phone number or a 'tag', which come from two different sources. So what I want is something along the lines of:

code:

SELECT locID
  CASE locID
    WHEN keyword = :tag THEN locID
    WHEN keyword = :number THEN locID
    ELSE 'Not found'
  END
FROM location

Basically, try to find the 'tag', if that exists, select the locID, else try to find the 'number', if that exists, select the locID, else return false (or like 'Not found').

The script receives a message that contains either a phone number (so just one location for that phone number), or a tag (so a phone number with multiple locations) if that helps to clear it up.

# ? Aug 7, 2010 15:49

Goat Bastard: Oct 20, 2004

zelifcam posted:

I'm trying to build a case statement and I'm stuck:

If locId is a number column then I don't think you'll be able to do this - I'm pretty sure that if you try to make a result set with two or more data types in the same column then you'll get an error like "Expected NUMBER but was VARCHAR2" (or whatever it is for your flavor of database), even if the query is guaranteed to only find one or the other depending on the bind variables. You might be better off settling for

SELECT locId
FROM location
WHERE keyword = :tag
OR keyword = :number

and then handle the empty result set case in whatever code calls it?

Disclaimer: this is just from memory - I didn't actually try it. It also might only be applicable for Oracle.

# ? Aug 8, 2010 08:47

mindphlux: Jan 8, 2004; by R. Guyovich

latest in my list of dumb access questions

I have a data entry form that lets me browse a table quickly. on this form is a combo box, which populates its dropdown by ID code (UID), customer code, firstname and lastname. The UID is horribly long, so I have it hidden and just use my customer code to quickly look up records. The problem is, the customer code isn't unique - sometimes there will be 3 or 4 entries that say "077x" for instance.

By sorting my query by customer code then date, I have it so that if I just type in a customer code, the combo box will 'onupdate' to the "first" record which matches the SSN, using the code - searchforrecord where ="[CUSTOMERID] = " & "'" & [Screen].[ActiveControl] & "'"

The problem is, whenever I select any record from the dropdown, it always goes to the 'first' record it finds, as opposed to the particular one I select. So, to accomplish this, I imagine I want the searchforrecord to be going for the hidden UID, rather than the non unique CUSTOMERID. But, I still want to search and select with the customer ID, rather than the meaningless UID.

I've googled so much, watched so many tutorials, and banged my head against a wall all weekend, so I guess this is my last resort. again, a really dumb question and I can't believe I'm having to ask it. basically I just don't know any VB, and so I don't understand what the [screen].[activecontrol] is going for, or how to change it to select a particular other field of my query, rather than just what's in the combo box.

# ? Aug 9, 2010 04:54

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

mindphlux posted:

latest in my list of dumb access questions

I have a data entry form that lets me browse a table quickly. on this form is a combo box, which populates its dropdown by ID code (UID), customer code, firstname and lastname. The UID is horribly long, so I have it hidden and just use my customer code to quickly look up records. The problem is, the customer code isn't unique - sometimes there will be 3 or 4 entries that say "077x" for instance.

By sorting my query by customer code then date, I have it so that if I just type in a customer code, the combo box will 'onupdate' to the "first" record which matches the SSN, using the code - searchforrecord where ="[CUSTOMERID] = " & "'" & [Screen].[ActiveControl] & "'"

The problem is, whenever I select any record from the dropdown, it always goes to the 'first' record it finds, as opposed to the particular one I select. So, to accomplish this, I imagine I want the searchforrecord to be going for the hidden UID, rather than the non unique CUSTOMERID. But, I still want to search and select with the customer ID, rather than the meaningless UID.

I've googled so much, watched so many tutorials, and banged my head against a wall all weekend, so I guess this is my last resort. again, a really dumb question and I can't believe I'm having to ask it. basically I just don't know any VB, and so I don't understand what the [screen].[activecontrol] is going for, or how to change it to select a particular other field of my query, rather than just what's in the combo box.

Is there a reason you aren't using a Custom class that you can access both the name and the uid from easily from the dropbox?

code:

public class item
{
     public string name;
     public string uid;

     public item(string Name, string Uid)
     {
          name = Name;
          uid = Uid;
     }

     public override string ToString()
     {
          return name;
     }
}

you add these items to the drop down it will auto parse out the name and when you want to get the uid out of it.

To add and item

code:

item MyItem = New item(name,uid);
Dropdown.Items.Add(MyItem);

To Get the Item

code:

item MyItem = (item)Dropdown.SelectedItem;
MyItem.uid

I think this it how it all works i dont have a complier to test it right now.

edit: oh hell i though this was the .net thread by the nature of the question i dont really understand how its a sql one.

Sprawl fucked around with this message at 05:18 on Aug 9, 2010

# ? Aug 9, 2010 05:14

mindphlux: Jan 8, 2004; by R. Guyovich

Sprawl posted:

edit: oh hell i though this was the .net thread by the nature of the question i dont really understand how its a sql one.

sorry, it wasn't, entirely.

# ? Aug 9, 2010 15:45

mindphlux: Jan 8, 2004; by R. Guyovich

Goddamnit I still can't get this to work.

I have a combo box on my form (combo62) that is correctly populating with my GUID.

If I go into "immediate" and type the following, this happens

code:

?Forms![Employees - Form Entry].Combo62
{000D9294-747A-4700-BD04-CB1C8F0C840D}

So, I know that element is correct.

I have the 'onupdate' for the combo box to do a macro "searchforrecord", and the WHERE criteria is this.

code:

="[Employees]![ID] = " & "'" & [Forms]![Employees - Form Entry]![Combo62] & "'"

There's no type mismatch, but also nothing happens, it just sits there. If I use the access control wizard to make my combo box, it wants to do a very confusing onupdate search for record WHERE

code:

="[ID] = " & Str(Nz([Screen].[ActiveControl],0))

which gives me a type mismatch? presumably because my GUID isn't a string? But I've tried removing the Str() function, the Nz()function, the extra quotes, blah, blah, blah into infinity and it either just sits there when I use the combo box, or gives me a type mismatch error

I've seriously spent 4 hours trying every combination of everything I can think of. The only way I can get this stupid combo box to even remotely do what I want is to exclude the ID altogether, and do a match on SSN or Customer id like this -

code:

="[SSN] = " & "'" & [Screen].[ActiveControl] & "'"

but that defeats the point because there are multiple customerids/SSN entries so it just goes to the first record it finds which is not what I want.

I'm seriously about to blow my brains out. I will definitely buy you beer (paypal 5) if you can tell me what the gently caress I'm doing wrong, or how to accomplish this via macro - preferably without using a bunch of VB code that I don't understand.

mindphlux fucked around with this message at 00:52 on Aug 10, 2010

# ? Aug 10, 2010 00:48

mindphlux: Jan 8, 2004; by R. Guyovich

whelp, looks like im buying myself a beer.

GUIDs have weird brackets which somehow aren't actually part of the value of the something blah blah and really they aren't strings, they're actually arrays of hex or something and in the end you have to convert bullshit GUIDs to strings and compare a variable that's also been converted into a string to find the

god my head

I guess I ended up learning a little vb anyways.

savior article - http://www.vb123.com/kb/index.html?199812_mc_replication.htm

# ? Aug 10, 2010 02:49

JasonV: Dec 8, 2003

New to SQL, so this might be an easy one, but I can't seem to get my head around it.

Given a table of authors and books, where multiple authors might write one book:

code:

book_id     author
------------------
1           john
2           john
3           john
4           steven
5           bob
2           steven

I need to get a list of all book ids that John wrote by himself (1 and 3). I've been hacking at this for awhile, but it's at the point where I'm just randomly trying stupid stuff in the WHERE clause. I'm pretty sure I'm missing something simple.

# ? Aug 10, 2010 17:46

Kekekela: Oct 28, 2004

JasonV posted:

New to SQL, so this might be an easy one, but I can't seem to get my head around it.

Given a table of authors and books, where multiple authors might write one book:
code:
book_id     author
------------------
1           john
2           john
3           john
4           steven
5           bob
2           steven
I need to get a list of all book ids that John wrote by himself (1 and 3). I've been hacking at this for awhile, but it's at the point where I'm just randomly trying stupid stuff in the WHERE clause. I'm pretty sure I'm missing something simple.

There's probably a slicker way but something like:

code:

select max(author) from books having count(book_id) = 1 and max(author)='john'

should work

# ? Aug 10, 2010 17:53

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Kekekela posted:

There's probably a slicker way but something like:
code:
select max(author) from books having count(book_id) = 1 and max(author)='john'
should work

Ah that won't work?

You would need to do something like

code:

select book_id,Count(author) as acount, author group by book_id Having acount = 1 and author ='john'

Sprawl fucked around with this message at 18:09 on Aug 10, 2010

# ? Aug 10, 2010 18:07

JasonV: Dec 8, 2003

Sprawl posted:

Ah that won't work?

You would need to do something like
code:
select book_id,Count(author) as acount, author group by book_id Having acount = 1 and author ='john'

That did it! (once I added the FROM

)

It was the HAVING I didn't know about. I was trying to do some strange COUNT(SELECT ...) = 1 in the WHERE.. which just didn't work.

Thanks!

JasonV fucked around with this message at 18:17 on Aug 10, 2010

# ? Aug 10, 2010 18:15

Kekekela: Oct 28, 2004

Sprawl posted:

Ah that won't work?

I was free-handing and left out the 'group by'. Fixed/tested version...

code:

select book_id
from books
group by book_id Having Count(author)=1 and max(author) ='john'

.
.
.
.

quote:

You would need to do something like

code:

select book_id,Count(author) as acount, author group by book_id Having acount = 1 and author ='john'

This fails for several reasons...
a - no FROM clause
b - can't refer to the aliased field (account) in the having clause
c - can't reference 'author' in the having clause since that's not part of the groupby or an aggregate

Kekekela fucked around with this message at 05:49 on Aug 12, 2010

# ? Aug 10, 2010 18:22

quackquackquack: Nov 10, 2002

I have an MS SQL 2005 database, and a FileMaker 10 client accessing it via ODBC (SQL Native Client)

The data I am trying to import from MS SQL is (varchar(max), null). I have no problem importing (varchar(64), null). It is not an option to change the SQL database.

When I attempt the FileMaker import via ODBC, I get the error: "ODBC Error: [Microsoft][SQL Native Client]String data, right truncation"

I have tried setting the FileMaker field I am importing into to text, container, and number. There is no validation. I've looked through the SQL data in question, and there is nothing strange: IPv4 IP addresses, IPv6 IP addresses, and NULL.

I have no problems querying the MS SQL database itself for the info.

Open to all ideas. I only tinker rarely with databases (this one is our inventory that reads from Configuration Manager), so please let me know if I can be clearer about the problem.

# ? Aug 11, 2010 19:08

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Noel posted:

I have an MS SQL 2005 database, and a FileMaker 10 client accessing it via ODBC (SQL Native Client)

The data I am trying to import from MS SQL is (varchar(max), null). I have no problem importing (varchar(64), null). It is not an option to change the SQL database.

When I attempt the FileMaker import via ODBC, I get the error: "ODBC Error: [Microsoft][SQL Native Client]String data, right truncation"

I have tried setting the FileMaker field I am importing into to text, container, and number. There is no validation. I've looked through the SQL data in question, and there is nothing strange: IPv4 IP addresses, IPv6 IP addresses, and NULL.

I have no problems querying the MS SQL database itself for the info.

Open to all ideas. I only tinker rarely with databases (this one is our inventory that reads from Configuration Manager), so please let me know if I can be clearer about the problem.

iirc a MAX varchar in MSSQL is very long ( 8000 characters iirc )

http://dev.mysql.com/tech-resources/articles/filemaker_mysql_whitepaper/filemaker_to_mysql_whitepaper01.htm
Looking at this example for My SQL and Filemaker because filemaker uses the "text" field and they can be very long you do something on the OBDC advanced area.

# ? Aug 11, 2010 19:26

quackquackquack: Nov 10, 2002

Good find. Unfortunately since I am going in the opposite direction (SQL data into FileMaker) I do not see a comparable setting in my SQL Native Client.

# ? Aug 11, 2010 20:30

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 19:26

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Noel posted:

Good find. Unfortunately since I am going in the opposite direction (SQL data into FileMaker) I do not see a comparable setting in my SQL Native Client.

Your importing into Filemaker using ODBC to read your MSSQL ?

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125505&wa=wsignin1.0

sounds like possible wrong data type in your columns or the data is too long for your column unless you figure out where its erroring its hard to say either way.

# ? Aug 11, 2010 20:45

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »