| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9
Comments:
<0> your query is very ugly for me to go in and actually try to debug what you are doing
<1> heh. its not ugly - its just bigger than you expected
<0> no its ugly
<0> its not formatted properly
<1> there is a left join, its on line 003
<1> SicLuDe: I beg to differ
<0> i understand that theres a left join
<0> the query that i pasted has no left join
<0> you said you tried out my query and it did not return any 0s
<0> i do not see you trying out the query i pasted
<1> it is formatted correctly, for my resolution, and without width restrictoins
<0> you can differ all you want but the fact is that you should have multiple lines with proper formatting
<1> no - I tried the way you wrote your query and it did not return all regions
<1> as I said, there are not customers in 100% of the regions, but for the sake of the report, there MUST be 125 (+1 for customer group name) so that they match the predefined textboxes in the report
<0> ok thats what i want to see
<0> you tryign out the query i wrote
<0> with your tables
<0> and by the way: http://sql-servers.com/nopaste/?show=274
<0> that would be a much more readable format
<0> so dont tell me that your query with no indents is a readable format
<1> fair enough
<0> and i still dont get how you get 125 regions out of 2 characters? unless they are alphanumeric?
<2> I'm willing to bet his source is as as readable as his query. :/
<0> :)
<1> SicLuDe: you are not british, are you?
<0> nope
<1> uk postcodes are not like aipz
<1> zips sorry
<0> well thats why i asked if its alphanumeric
<1> they are in the form A(A?)00 0AA
<1> sorry
<1> they are in the form A(A?)0(0?) 0AA
<1> so- my post code is LE11 7AR
<0> ok
<0> so now... the correlated query that i wrote...
<1> and its the first two (sometmies 1) letters which degifine the region
<0> can i see how you wrote it so that it doenst give count* = 0?
<1> define sorry
<1> um... in 273 I think
<0> huh
<0> ?
<1> well, I just reformatted it
<1> and the site crashed
<1> !woot! </sarcasm>
<1> I'm not doing it again
<0> i just wnat to see the query dude
<0> i dont see why this is taking so long
<1> I told you - 273
<1> http://sql-servers.com/nopaste/?show=273
<0> what the hell is 273?
<0> dude throwing a number out there means nothing to me
<1> sorry :/
<0> yet you are still doing a left join in there
<0> i keep on asking to see the version you wrote with teh corellated query
<1> yeeeees. because without it the query does not do the basic requirement
<0> well im wondering how you can claim that the query i gave you does not work if you have not tried it
<0> im really trying to help you but you are not making it easy
<0> the basic requirement you claimed to me was that you want a count of customers (including 0) for each region
<1> ok - SicLuDe I apprechiate your hlp, but at this point its causing me more harm than good. the query in 273 does what it nees to do, but prints an extra page. I wrote some vba into the form to ignore the page if all the fields were null in the time its taken us to get this far. without the left join in the table, and doing it the way that you showed me caused the query to not print out the regions where isnull(count) or count =0. w
<0> which i claim the query i gave you does and you have not shown me how you implemented it
<1> ::sigh::
<1> I will write the query again
<3> argh.... how do i create a foreign key in sql server 2005, i get an error saying the tables need at least one related column!?!?
<1> can you paste it again, its gone outside my logs
<3> isn't the key i am about to make the relation!??
<1> or is it the one in 274?
<1> thast http://sql-servers.com/nopaste/?show=274 sorry
<1> no wait. obviously not that one
<1> hmm. this whole problem has just become mute, as I just found out that due to ms being stupid as hell, they broke the implementation of crosstab queries in vba, thus negating the possibility of doing what I wanted to anyhow
<1> oh yeah. they broke it in 2000, and its still not resolved.
<1> "good work" billy
<4> crosstab queries?
<1> Halo_Four: pivot tables (in non ms speak)
<4> I know what they are. They don't exist in SQL beyond some grouping tricks.
<1> indeed.
<0> Halo_Four: they exist in 2005
<4> Syntax candy for the grouping tricks.
<1> but when you say "dont exist" what you actually mean is that you can achieve them, just that very few people know why you would need to, and even fewer know how
<1> actually, you said it better
<4> What I actually mean is that it's something that SQL and RDBMSes were not designed to do.
<1> um. I disagree. but I really dont want to get into a debate right now.
<5> about the null-value. (mssql) When I insert a "" it becomes a null-value. How can I insert an empty "" ?
<4> Schemas must be statically deterministic. Pivots violate that, which is why not pivot hack can handle unaccounted for columns.
<4> Pivoting is something which really should be handled at the client level.
<4> s/not/no
<6> anyone here use the belkin omniview pro2 KVMs? do they need a ps2 keyboard/mouse to function? or will usb work solo
<1> rjarett: you can buy a power supply
<6> what does that have to do with a KVM?
<4> SELECT Something, MAX(CASE Title WHEN 'Value1' THEN Data ELSE NULL END) AS Value1, MAX(CASE Title WHEN 'Value2' THEN Data ELSE NULL END) AS Value2 FROM Table1 GROUP BY Something
<1> Halo_Four: I can see where you are coming from, but the idea of statistically "pivoting" a table predates computers
<4> So do bizarre DB types, but that's not relevant to the discussion.
<1> rjarett: you need to power the kvm, and the usb. usually this power is taken from the ps2. if you have no ps2 connectors you still need to supply power to the unit using a power supply (available seperately)
<6> im not tal;king kvm power. im talkign.. when you use the old omniview, and you dont have one of the ps2 plugged in, it thinks the port is dead and wont lock on
<1> IT HAS NO POWER!
<6> yes it does
<6> thats what the big fat plug is for
<1> in which case, if you are talking using multiple clients, the old omniview detected clients using the monitor plug, not the ps2
<6> you have no clue what you are talkign abotu do you?
<1> so - you could "log into" a client screen, so long as a vga plug was pluged in
<4> SELECT Something, Value1, Value2 FROM Table1 PIVOT (MAX(Data) FOR Title IN (Value1, Value2)) AS PivotedTable
<6> no
<4> That is SQL2005s answer to pivoting
<6> even with the vga connected, you would not be able to lock onto the channel
<6> unless the keyboard was too
<4> I have one of those two port Belkin KVMs, USB, the thing was a bitch.
<4> The kind with the cables built into it
<6> i am asking.. do the new pro2 models need that? or willt hey always show what is coming in on vga even without the keyboard/mouse inst connected
<1> rjarett: I just did it on mine, and it worked fine without ps2
<1> so :p
<1> however mine !usb
<6> MattyT is that a 8 port omniview pro?
<1> yeah, old school. pre usb
<4> If the ****ing thing doesn't get a USB connection on a port it ***umes that nothing is there and switches over, which ****s during POST or working on something that doesn't have USB support.
<4> I tried to find a firmware upgrade to disable that but I didn't see one.
<6> unplug the PC keyboard fromt he units back
<1> rjarett: are you talking to me, becauseI jsut did that. and like I just said..... it worked fine.
<6> well i think you are lying or have no clue
<1> Halo_Four: is that different to the way that it was done pre sql2k5?
<6> either way thats nto what i asked
<4> SQL2000 didn't have a PIVOT operator, the first example I posted would be for SQL2000
<1> rjarett: whatever. also - I may have lied a little, its a 4 port omniview pro
<4> Using MAX, CASE and GROUP BY
<1> dunno if that makes a difference
<4> In the end they perform the same thing.
<6> the units will not lock on their channel without the kb, and they wont display
<6> which is a problem with suns/clones with the apx mb
<1> also I didnt unplug from the unit as its not easy to do, I did it from the machine side, as thats easier to get to. maybe it does it if there is a plug in the unit.
<1> in which case, just get a few old mice and cut the ps2 off them
<6> no
<6> its not just if "it exists" in the unit
<1> ok - now you have actually lost me.
<1> also
<1> Halo_Four: have you ever seen this error: Cannot use the crosstab of a non-fixed column as a subquery
<1> its generated by vba, but if I copy the sql code into access it works fine.
<4> non-fixed?
<1> apparently its if you try to sort the columns in a crosstab, but I'm not doing that
<1> yeah. I dont get it
<1> its really not "non-fixed"
<1> all the web research I'm doing says its a known error in the ms dbms, and is cured by "saving" the query before executing.
<4> hm, Access, dfferent animal
<1> wondering if the best way is to "save" a query into the db - execute it then delete it
<1> unfortunatly its all they have here
<7> matt`: your executing the query through vba using odbc ... or oledb ...
<7> access doesn't use either of them internally ...
<7> so you can do things in access sql that you can't do in odbc/oledb sql
<1> I see.
<1> so basically "NNNNK - you lose!"
<7> and things like cross tabs should be saved as 'queries' in access ...
<1> MoZ: yeah, apart from the query is dependant on a couple of values in the vba project
<1> actually just one
Return to
#sql
or
Go to some related
logs:
VANKA.BE
usage pcl2pdf
#delphi
#computers
helena kristensen
check_nt sql
#slackware
#flash
pectogram
#hardware
|
|