"Sue Hoegemeier" wrote:

> Could you please provide sample data? If you enter null for
> techarea then you still have a row when grouped by the
> agency id. The count would not be null or 0. I tried to
> guess at what your sample data may be but it doesn't make
> much sense. In the previous post there was a link to
> netiquette for how to get questions on queries answered. The
> section about sample data provides a link to a script to
> generate sample data.
>
> -Sue
>
> On Thu, 24 Mar 2005 06:01:03 -0800, CLM
> <CLM@discussions.microsoft.com> wrote:
>
> >Sue,
> >
> >I wasn't aware that DDL meant the script statement, here it is:
> >if exists (select * from dbo.sysobjects where id =
> >object_id(N'[dbo].[ApplicationLoad]') and OBJECTPROPERTY(id, N'IsUserTable')
> >= 1)
> >drop table [dbo].[ApplicationLoad]
> >GO
> >
> >CREATE TABLE [dbo].[ApplicationLoad] (
> > [appinv_item_id] [float] NULL ,
> > [appinv_agency_id] [int] NULL ,
> > [appinv_techarea] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> >NULL ,
> > [appinv_vendor] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [appinv_product] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [appinv_model] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [appinv_version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >) ON [PRIMARY]
> >GO
> >
> >As for Data, I am only concerned with the agency_id and techarea:
> >appinv_agency_id = 999
> >appinv_techarea:
> > 1. Null (# of Entries in Data - 3)
> > 2. Web Development (# of Entries in Data = 1)
> > 3. Web Management/Portal Access (# of Entries in Data = 2)
> > 4. Web Management/Web Analytics (# of Entries in Data = 1)
> > 5. Web Server Software/Web Services (# of Entries in Data = 4)
> >
> >Basically All I am trying to do is per agency id, give me a grouping of
> >techarea and then tell me how many entries are there. If it is the count
> >comes up as 0/Null then count the number of times the ID# is there for the
> >null techarea and give me that result. That is why I am using the CASE (Iif)
> >statement.
> >
> >CLM
> >
> >"Sue Hoegemeier" wrote:
> >
> >> The easiest way is probably from Enterprise Manager, right
> >> click on the table, select all tasks, select Generate SQL
> >> script and then click on preview. That will show the create
> >> table statement (DDL) in the script preview window. You can
> >> copy and paste it here.
> >> You generally want to do that and provide insert statements
> >> for sample data and give sample results or explain what it
> >> is you are trying to accomplish when you post questions
> >> along the lines of "how do I do this query". When people
> >> just post a query and report that it doesn't work, it's
> >> generally not enough information for someone to figure out
> >> how to help them. When you can provide a script of the
> >> table, the data, etc then others have a lot more to go on
> >> with helping you out. Some of this is explained in this
> >> link: http://www.aspfaq.com/etiquette.asp?id=5006
> >>
> >> -Sue
> >>
> >> On Wed, 23 Mar 2005 06:19:03 -0800, CLM
> >> <CLM@discussions.microsoft.com> wrote:
> >>
> >> >How do I go about doing that? I can load a strip down *.mdb but I am
> >> >unfamiliar with DDL.
> >> >
> >> >CLM
> >> >
> >> >"Sue Hoegemeier" wrote:
> >> >
> >> >> Could you post the DDL for ApplicationLoad and some sample
> >> >> data and describe what it is you are trying to do?
> >> >>
> >> >> -Sue
> >> >>
> >> >> On Tue, 22 Mar 2005 07:03:04 -0800, CLM
> >> >> <CLM@discussions.microsoft.com> wrote:
> >> >>
> >> >> >Sue,
> >> >> >
> >> >> >Thx for the direction, but if I could bother you to assist one more time. I
> >> >> >have the following written in SQL Query Analyzer:
> >> >> >
> >> >> >SELECT App.appinv_agency_id,App.appinv_techarea AS TA, CountOfTA = CASE
> >> >> >App.appinv_techarea
> >> >> > WHEN (COUNT(App.appinv_techarea)>1) THEN COUNT(App.appinv_agency_id)
> >> >> > WHEN (COUNT(App.appinv_techarea)<1) THEN COUNT(App.appinv_techarea)
> >> >> >FROM dbo.ApplicationLoad AS App
> >> >> >WHERE App.appinv_agency_id = 770
> >> >> >GROUP BY App.appinv_agency_id
> >> >> >
> >> >> >And I am getting the follow error message:
> >> >> > Server: Msg 170, Level 15, State 1, Line 2
> >> >> > Line 2: Incorrect syntax near '>'.
> >> >> >
> >> >> >As far as I know > is an accepted operator. Any ideas why I am getting the
> >> >> >error message that I am. I did find out it is a syntax error message but I
> >> >> >can't find any error.
> >> >> >
> >> >> >CLM
> >> >> >
> >> >> >
> >> >> >
> >> >> >"Sue Hoegemeier" wrote:
> >> >> >
> >> >> >> A select case in VBA is different from a Case expression in
> >> >> >> SQL. Although somewhat similar, the two aren't the same
> >> >> >> thing.
> >> >> >> But you really can use Case in SQL. It's documented in books
> >> >> >> online.
> >> >> >>
> >> >> >> -Sue
> >> >> >>
> >> >> >> On Mon, 21 Mar 2005 17:11:08 -0800, CLM
> >> >> >> <CLM@discussions.microsoft.com> wrote:
> >> >> >>
> >> >> >> >I thought cases were used within vba code/programming. I wasn't aware that
> >> >> >> >you could use a case statement within a SELECT statement.
> >> >> >> >
> >> >> >> >-CLM
> >> >> >> >
> >> >> >> >"Sue Hoegemeier" wrote:
> >> >> >> >
> >> >> >> >> You can use a Case expression instead. There is no IIF in
> >> >> >> >> T-SQL or ANSI SQL. You can find more information and some
> >> >> >> >> examples of using Case in SQL Server books online.
> >> >> >> >>
> >> >> >> >> -Sue
> >> >> >> >>
> >> >> >> >> On Mon, 21 Mar 2005 14:27:02 -0800, CLM
> >> >> >> >> <CLM@discussions.microsoft.com> wrote:
> >> >> >> >>
> >> >> >> >> >I have a field in the query where I count the tech_area field. I have an Iif
> >> >> >> >> >(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
> >> >> >> >> >
> >> >> >> >> >My question is how do I translate that in SQL Server? I tried to write the
> >> >> >> >> >query using the new view window but I kept erroring when I put the IIf
> >> >> >> >> >statement in.
> >> >> >> >> >
> >> >> >> >> >Any help would be greatly appreciated.
> >> >> >> >> >
> >> >> >> >> >Thx,
> >> >> >> >> >